vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Scenario: Optimizing code with LEFT JOIN? (https://vborg.vbsupport.ru/showthread.php?t=62678)

Velocd 03-19-2004 10:30 PM
Scenario: Optimizing code with LEFT JOIN?
 
Say I have the following setup:

Code:
| table: athlete |
+--------------------------------+
| id  |  name  |  sports
+--------------------------------+
| 1  |  'John'  |  '1, 2'


| table: sport |
+----------------+
| id  |  title
+----------------+
| 1  |  'soccer'
+----------------+
| 2  |  'track'
PHP Code:
$sql = "SELECT name, sports 
    FROM `athlete`";

$result = mysql_query($sql) or die('Invalid query: ' . mysql_error());

while ($athlete = mysql_fetch_array($result))
{
    $sql = "SELECT title 
        FROM `sport` 
        WHERE id IN ($athlete[sports])";
        
    $result = mysql_query($sql) or die('Invalid query: ' . mysql_error());
    
    $size = mysql_num_rows($result);
    $i = 0;
    
    while ($sport = mysql_fetch_array($result))
    {
        $sports .= $sport['title'] . ($i < $size ? ' & ' : '');
        
        $i++;
    }
    
    echo "$athlete[name] enjoys $sports";
} 
Unless I made a mistake, which is possible since I haven't tested the above code, it should print:

Code:
John enjoys soccer & track
My problem is I don't want to call a second query inside the first while loop.

If I had only 1 sport, say soccer, inside the `sports` field for row 1 of table `athlete`, I could do the following:

PHP Code:
$sql = "SELECT athlete.name, athlete.sports, sport.title AS sport_list
    FROM `athlete` 
    LEFT JOIN `sport` 
    ON (athlete.sports=sport.id)";

$result = mysql_query($sql) or die('Invalid query: ' . mysql_error());

while ($athlete = mysql_fetch_array($result))
{
    echo "$athlete[name] enjoys $athlete[sport_list]";
} 
Returning:

Code:
John enjoys soccer
This only works with one sport though. Is there someway to use a LEFT JOIN and grab the title for all sports whose IDs are located in the athlete's `sports` field?

Maybe return a subarray containing the values?

I understand I could easily insert the names of the sports into the `athlete` table, but in my real code things are much more complicated, and I might have the sport title change, therefore I would have to search every row in the `athletes` table that contains the prior sport name(s) and update it to the new name.

Sorry if my wording is confusing, the actual concept isn't, and hopefully there is a way to simply this process to my needs. ;)

[originally posted at Sitepoint]

Dean C 03-20-2004 09:43 AM
Why not have both queries and loop through them and create an array. Then work with it from there..?

Velocd 03-20-2004 04:54 PM
Well, that's what I'm doing in the first example. I'm just wondering if there is a way to do it in 1 query. :[]

Dean C 03-20-2004 05:04 PM
You're doing a query loop there. For every row you're running two queries. What I meant was to run both queries. Loop through them both and place their contents in two arrays. That way you have two queries?

Boofo 03-20-2004 06:19 PM
Why don't you use just one table instead of having 2 tables? That way you could pull all the info with 1 query. ;)

Velocd 03-21-2004 11:47 PM
In my real code, the tables are much more complex and so is how the data is handled. One table wouldn't work at all.

As for creating two arrays from two loops, that would lead me to having arrays with arrays with arrays in them, and all sorts of messy goodness.

A suggestion by a member of Sitepoint (http://www.sitepoint.com/forums/show....php?p=1149531) was to create a 3rd table for containing the relations, and then using 1 query to access all the required data through an inner join.


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

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01037 seconds
  • Memory Usage 1,738KB
  • 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
  • (3)bbcode_code_printable
  • (2)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (6)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