RE: [PHP-DB] speeing up query and display...
| From: | Aaron Wolski | Date: | Mon, 21 Jul 2003 11:39:40 +0000 |
| Subject: | RE: [PHP-DB] speeing up query and display... | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-29354@lists.php.net to get a copy of this message | ||
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: [PHP-DB] 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