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:[EMAIL PROTECTED] Sent: July 20, 2003 4:48 PM To: Aaron Wolski Cc: [EMAIL PROTECTED] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
