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