vb.org Archive - query question

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

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

- - query question (https://vborg.vbsupport.ru/showthread.php?t=63723)

burnist

04-10-2004 06:13 PM

query question

I want to select a row from a table thats contains a list of ids which is simple enough but i want to then take each id from that row (1 per coloum) and use it to select roms in another table. I cant think of anyway to do this as there would be new colums added to the first table quite frequently.

Dean C

04-10-2004 06:35 PM

You can do queries within queries:

e.g. SELECT * FROM table WHERE(SELECT ids FROM table2);

I believe ^^

burnist

04-10-2004 07:12 PM

PHP Code:


		
			
$useritemsquery = $DB_site->query("SELECT id,name,icon FROM items WHERE id =('SELECT * FROM items_users WHERE userid=$bbuserinfo[userid]') ORDER BY id ASC");

while($useritems = $DB_site->fetch_array($useritemsquery)){

eval("\$showusersitem .= \"".fetch_template("itemshop_showuseritems")."\";");

}

Thats what i've go so far and i think what you ment works but i think it joins all the id's together into one big id and then the first query cant find the row and wont return it so the eval never happens

Christine

04-10-2004 07:18 PM

Quote:

Originally Posted by Dean C

You can do queries within queries:

e.g. SELECT * FROM table WHERE(SELECT ids FROM table2);

I believe ^^

Dean,

I think that isn't available until MySQL 5 (or is it 4.1)?

burnist

04-10-2004 07:34 PM

^ crap i only run 4.0.18 :(

Dean C

04-11-2004 01:26 PM

I'm pretty sure I read it in the mysql 4 manual :)

Christine

04-11-2004 01:51 PM

Gotcha.

I had to dig it back up, because I didn't recall the specifics but it stuck out to me that 'I can't do this yet'.

Apparently, it isn't a matter of can't, it is a matter of how (and that is what changes in 4.1)

Quote:

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and other methods.

http://dev.mysql.com/doc/mysql/en/Subqueries.html

http://dev.mysql.com/doc/mysql/en/Re...ubqueries.html

Glad I saw this thread!!

burnist

04-11-2004 03:42 PM

So basicly im looking for something like this ??

[sql]
$DB_site->query("SELECT id,name,icon FROM items
LEFT JOIN items_users ON items.id=items_users.*
WHERE items_users.userid IS $bbuserinfo[userid]; ORDER BY items.id ASC");
[/sql]

All times are GMT. The time now is 04:13 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01082 seconds Memory Usage 1,726KB 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 (1)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (8)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: