Re: [PHP-DB] speeing up query and display...
A bit offtopic .. But 3000 X any kind of size = a good amount of rendered
html data to be transmitted.. I'd make sure your using some kind of
compression module on the server like mod_gzip ... that would cut down on
download time dramatically (Especially for low bandwidth users)
3000 rows isn't much and the actual query/build time is probably nothing
compared to the transmission time..
On Sun, 20 Jul 2003, John W. Holmes wrote:
> 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:
> >
> > >
> > $manufQuery = db_query("SELECT manufacturer FROM
> > kcs_threads");
> > while ($manufResults = db_fetch($manufQuery)) {
> >
> > ?>
> > > ?>
> >
> > >
> > $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)) {
> >
> > ?>
> > > if ($threadselectResult[thread_index] == $threadResults[id]) echo
> > "checked"; ?>>
> >
> > >
> > }
> > }
> > }
> >
> > ?>
> >
> > Can ANYONE see a way to speed up the query and displaying of the
> > results? Take a while on High Speed and WY 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 "{$row['manufacturer']}\n";
> $old_manufacturer = $row['manufacturer'];
> }
>
> echo " echo empty($row['thread_index']) ? '' : ' selected';
> echo ">{$row['colourID']}\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
RE: [PHP-DB] speeing up query and display...
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:
>
>
> $manufQuery = db_query("SELECT manufacturer FROM
> kcs_threads");
> while ($manufResults = db_fetch($manufQuery)) {
>
> ?>
> ?>
>
>
> $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)) {
>
> ?>
> if ($threadselectResult[thread_index] == $threadResults[id]) echo
> "checked"; ?>>
>
>
> }
> }
> }
>
> ?>
>
> Can ANYONE see a way to speed up the query and displaying of the
> results? Take a while on High Speed and WY 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 "{$row['manufacturer']}\n";
$old_manufacturer = $row['manufacturer'];
}
echo "{$row['colourID']}\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
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:
$manufQuery = db_query("SELECT manufacturer FROM
kcs_threads");
while ($manufResults = db_fetch($manufQuery)) {
?>
?>
$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)) {
?>
if ($threadselectResult[thread_index] == $threadResults[id]) echo
"checked"; ?>>
}
}
}
?>
Can ANYONE see a way to speed up the query and displaying of the
results? Take a while on High Speed and WY 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 "{$row['manufacturer']}\n";
$old_manufacturer = $row['manufacturer'];
}
echo "{$row['colourID']}\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
Re: [PHP-DB] speeing up query and display...
You've got three queries, two of which are in while loops. If you're calling up
3000 records, you may have as many as 3000 DB queries, possibly more. That's
why things take forever.
Restructure your code, placing the queries outside the loops. Then use
mysql_fetch_array() and loop through the result sets. While you're at it, stop
closing out of PHP to write one character. Simply enclose your echo/print text
with single quotes so that PHP knows not to evaluate it.
To start you off, I've done most of the later work. I've also taken the liberty
of neatly formatting your code so that it's easier to follow. Good luck with
the rest.
'.$manufResults[manufacturer].'';
$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)) {
echo ''.$threadResults[colour].'';
}
}
?>
Edward Dudlik
Becoming Digital
www.becomingdigital.com
Did I help you? Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU
- Original Message -
From: "Aaron Wolski" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, 19 July, 2003 13:29
Subject: [PHP-DB] speeing up query and display...
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:
>
Can ANYONE see a way to speed up the query and displaying of the
results? Take a while on High Speed and WY to long on Dialup.
Thanks a lot
Aaron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
