Select value from table where varname = '3 different things''

[Nullifi3d]

I am trying to select 3 items from the setting table:
the info from the value column where the varname column equals illegalusernames, minusernamelength & maxusernamelength.

Something like:
"SELECT value AS illegalusernames FROM ".TABLE_PREFIX."setting WHERE varname = 'illegalusernames'"
"SELECT value AS minusernamelength FROM ".TABLE_PREFIX."setting WHERE varname = 'minusernamelength'"
"SELECT value AS maxusernamelength FROM ".TABLE_PREFIX."setting WHERE varname = 'maxusernamelength'"

I want to do this without having to pull 3 different queries.

[Lynne]

Your where would be something like:
SELECT value, varname FROM `setting` WHERE varname IN ('illegalusernames','minusernamelength ','maxusernamelength')

[Nullifi3d]

For some odd reason the sql only fetches the illegalusernames field when I read the var_dump output.
$results = $db->query_first("SELECT value, varname FROM ".TABLE_PREFIX."setting WHERE varname IN ('illegalusernames','minuserlength ','maxuserlength')");Also tried query_read, but it only outputs a mysql resource.

[Lynne]

(The same was true when I tested it on my setting table but then I went and looked and neither of those other settings are in there.) However, if the rows are in your table, you aren't going to get them by using query_first. query_first only fetches a single row. You need to do a regular query and then a fetch_array (probably in a while loop to grab the values).

[Nullifi3d]

I was doing some research and found matching information to what you're saying and tried adjusting my code:
$query = $db->query_read("SELECT value, varname FROM ".TABLE_PREFIX."setting WHERE varname IN ('illegalusernames','minuserlength ','maxuserlength')");
$results = $db->fetch_array($query);
print_r($results);

The Above Outputs:
Array ( [value] => admin moderator op9 option9 option.9 forum vbulletin [varname] => illegalusernames )
I can't understand doing a loop/while/foreach if there isn't proper array data to loop through.
Strange enough, when executing this sql straight into phpmyadmin it returns all three rows. So I assume it must be something with how I am calling the db queries within vbulletin/php.

I have tried several variations of queries/fetches with no success.

[kh99]

You want to do something like:

$query = $db->query_read("SELECT value, varname FROM ".TABLE_PREFIX."setting WHERE varname IN ('illegalusernames','minuserlength ','maxuserlength')");

while ($results = $db->fetch_array($query))
{
print_r($results);
}



Edit: Oops, just noticed that Lynne beat me to that first reply. Anyway, you also have an extra space in the 'minuserlength' string.

[Nullifi3d]

You want to do something like:

$query = $db->query_read("SELECT value, varname FROM ".TABLE_PREFIX."setting WHERE varname IN ('illegalusernames','minuserlength ','maxuserlength')");

while ($results = $db->fetch_array($query))
{
print_r($results);
}


You should think about it as if the "query" call initializes things, and the fetch_array() actually gets the data. If you had a large database and a query that returned thousands of rows, it might not all fit in memory at the same time so the function couldn't return an array. Instead it's set up this way so that you don't have to concern yourself with those kinds of issues.

Edit: Oops, just noticed that Lynne beat me to that first reply. Anyway, you also have an extra space in the 'minuserlength' string.
freaking genius. ty... i feel like a moran for not trying while.