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
