RE: speeing up query and display...

["Aaron Wolski" <aaronjw@martekbiz.com>]

John,

I got it and it seems to work wonderfully.

When you mentioned doing the whole process in one query I was sure a
LEFT JOIN was going to be involved but to be honest.. I do not
understand LEFT JOINS in the least.. where to apply them and why.

Thanks so much for taking your valuable time to help. I am sure I'll be
able to learn from what you've provided to take my developments that
much further.

Thanks once again.

Aaron

-----Original Message-----
From: John W. Holmes [mailto:holmes072000@charter.net] 
Sent: July 20, 2003 4:48 PM
To: Aaron Wolski
Cc: php-db@lists.php.net
Subject: Re:  speeing up query and display...

Aaron Wolski wrote:
> Hi Guys,
>  
> I have the following query which unfortunately has to grab all records
> in a table (well over 3000) to display in a multiple select box.
>  
> Code:
>  
> <?php
>  
>                 $manufQuery = db_query("SELECT manufacturer FROM
> kcs_threads");
>                 while ($manufResults = db_fetch($manufQuery)) {
>  
> ?>
>                 <option value=""><?php echo
$manufResults[manufacturer];
> ?></option>
>  
> <?php
>  
> $threadQuery = db_query("SELECT id,colour,colourID FROM kcs_threads
> LIMIT 10");
> while ($threadResults = db_fetch($threadQuery)) {
>  
>                 $threadselectQuery = db_query("SELECT * FROM
> kcs_patternthreads WHERE pattern_index='$id'");
>                 while ($threadselectResult =
> db_fetch($threadselectQuery)) {
>  
> ?>
>                 <option value="<?php echo $threadResults[id]; ?>"
<?php
> if ($threadselectResult[thread_index] == $threadResults[id]) echo
> "checked"; ?>><?php echo $threadResults[colour]; ?></option>
>  
> <?php
>  
>                                                 }
>                                 }
>                 }
>  
> ?>
>  
> Can ANYONE see a way to speed up the query and displaying of the
> results? Take a while on High Speed and WAAAAY to long on Dialup.

Like someone else said, your nested queries approach is horrible. Here's

the way to do it with one query.

$query = "select t.manufacturer, t.id, t.colour, t.colourID,
p.thread_index
from kcs_threads t LEFT JOIN kcs_patternthreads p ON t.id =
p.thread_index
where p.pattern_index = $id OR p.pattern_index IS NULL";

$old_manufacturer = '';

$result = db_query($query);
while($row = db_fetch($result))
{
     if($old_manufacturer != $row['manufacturer'])
     {
         echo "<option value=\"\">{$row['manufacturer']}</option>\n";
         $old_manufacturer = $row['manufacturer'];
     }

     echo "<option value=\"{$row['id']}\"";
     echo empty($row['thread_index']) ? '' : ' selected';
     echo ">{$row['colourID']}</option>\n";
}

-- 
---John Holmes...

Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

PHP|Architect: A magazine for PHP Professionals - www.phparch.com





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php