Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 05-04-2002, 12:57 AM
bigmattyh's Avatar
bigmattyh bigmattyh is offline
 
Join Date: Nov 2001
Posts: 141
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Help with a nested MySQL query!

Okay, so I'm a pretty experienced MySQL programmer, but this problem has stumped me.

I'm getting some funky results when working with a nested MySQL query. It goes something like this:
Code:
$cars = $DB_site->query("SELECT * FROM cars");
while ($car = $DB_site->fetch_array($cars)) {
  $passengers= $DB_site->query("SELECT * FROM passengers WHERE car.id = '$car[id]'");
  while ($passenger= $DB_site->fetch_array($passengers)) {
      // Do stuff with data
      // Display the data
  }
}
I'm getting the list of cars to come out right. But the list of passengers for car #1 is getting returned for every car.

Has anyone encountered a similar problem to this, and does anyone have a solution?
Reply With Quote
  #2  
Old 05-04-2002, 02:44 AM
bigmattyh's Avatar
bigmattyh bigmattyh is offline
 
Join Date: Nov 2001
Posts: 141
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Problem solved. I discoverred the wonder that is "unset()".
Reply With Quote
  #3  
Old 05-04-2002, 11:00 PM
Mark Hensler's Avatar
Mark Hensler Mark Hensler is offline
 
Join Date: Oct 2001
Location: California
Posts: 205
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Ack! Avoid nested queries when possible. Use a JOIN query instead. You'll save processing time and system resources.

something like this:

SELECT cars.id as cid, cars.field2 as cfield2, passengers.id as pid, passengers.field2 as pfield2 FROM cars c LEFT JOIN passengers p ON cars.id=passwngers.car_id ORDER BY cars.field3 ASC, passengers.field3 ASC
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04469 seconds
  • Memory Usage 2,167KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (3)post_thanks_box
  • (3)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (3)post_thanks_postbit_info
  • (3)postbit
  • (3)postbit_onlinestatus
  • (3)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.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
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete