vb.org Archive - Select value from table where varname = '3 different things''

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Select value from table where varname = '3 different things'' (https://vborg.vbsupport.ru/showthread.php?t=287614)

Nullifi3d

09-08-2012 01:50 PM

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

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:

Code:

"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

09-08-2012 03:05 PM

Your where would be something like:

Code:

SELECT value, varname FROM `setting` WHERE varname IN ('illegalusernames','minusernamelength ','maxusernamelength')

Nullifi3d

09-08-2012 05:36 PM

For some odd reason the sql only fetches the illegalusernames field when I read the var_dump output.

Code:

$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

09-08-2012 05:43 PM

(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

09-08-2012 08:24 PM

I was doing some research and found matching information to what you're saying and tried adjusting my code:

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	09-08-2012 09:58 PM

You want to do something like:

Code:

$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

09-08-2012 11:07 PM

Quote:

Originally Posted by kh99 (Post 2363816)

You want to do something like:

Code:

$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.

All times are GMT. The time now is 12:00 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01314 seconds Memory Usage 1,729KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (6)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: