sorting query results

[Piotr Mitoraj]

Hi,
failing to find the straight-forward answer on the web I hope to find
some advice in here.
I want to display the table fetched from the postgres database. The
headers of the columns are links used to sort the data. Like the
functionality you have on ebay when you sort according to price or time
left. I recognized two ways of doing that:

1. Issue a new query to the database each time the specific sorting was
requested. In other words, let the database do the sorting and fetch the
results.

2. Save the table into php array and and use php built-in functions to
sort and refresh the table.

Which of those methods would be most desired in terms of performance and
according to "the best practices"?
What would be possible drawbacks/advantages of those methods.

Regards,
Piotr


--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[brew]

Piotr......

> I recognized two
> ways of doing that:   (sorting results)
>
> 1. Issue a new query to the database each time the specific sorting was
> requested. In other words, let the database do the sorting and fetch the
> results.
>
> 2. Save the table into php array and and use php built-in functions to sort
> and refresh the table.
>
> Which of those methods would be most desired in terms of performance and
> according to "the best practices"?
> What would be possible drawbacks/advantages of those methods.

I'd do it in within the database query, it's just a little bit of
programming, probably just add 'ORDER BY' to your statement.  Possibly
more, but if you have a real complicated query you'll be spending a
bunch of time getting it right anyway.

If it's slow, then you start to optimize the query.

At any rate, that's the way I do it.....

brew
--

Strange Brew (brew@...)

Check out my Stock Option website http://www.callpix.com
and my Musicians Free Classified http://www.TheMode.com

--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[Piotr Mitoraj]

Piotr......

>  
>> I recognized two
>> ways of doing that:   (sorting results)
>>
>> 1. Issue a new query to the database each time the specific sorting was
>> requested. In other words, let the database do the sorting and fetch the
>> results.
>>
>> 2. Save the table into php array and and use php built-in functions to sort
>> and refresh the table.
>>
>> Which of those methods would be most desired in terms of performance and
>> according to "the best practices"?
>> What would be possible drawbacks/advantages of those methods.
>>    
>
> I'd do it in within the database query, it's just a little bit of
> programming, probably just add 'ORDER BY' to your statement.  Possibly
> more, but if you have a real complicated query you'll be spending a
> bunch of time getting it right anyway.
>
> If it's slow, then you start to optimize the query.
>
> At any rate, that's the way I do it.....
>
> brew
>  

Thanks Brew! The queries I am using are not that complicated (yet). So
for now I ll just stick to this method.

--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[L. Franklin]

Piotr,

-----Original Message-----
From: Piotr Mitoraj [mailto:piotr.mitoraj@...]
Sent: Saturday, April 18, 2009 8:04 AM
To: pgsql-php@...
Subject: [PHP] sorting query results

Hi,
failing to find the straight-forward answer on the web I hope to find
some advice in here.
I want to display the table fetched from the postgres database. The
headers of the columns are links used to sort the data. Like the
functionality you have on ebay when you sort according to price or time
left. I recognized two ways of doing that:

1. Issue a new query to the database each time the specific sorting was
requested. In other words, let the database do the sorting and fetch the
results.

2. Save the table into php array and and use php built-in functions to
sort and refresh the table.

Which of those methods would be most desired in terms of performance and
according to "the best practices"?
What would be possible drawbacks/advantages of those methods.
-----------------------------------------

Assuming that you are displaying the entire result set in the browser at
once, I would strongly recommend doing the sorting in javascript in the
browser.  My personal favorite way of doing this is to use the jquery
javascript library coupled with the tablersorter plugin.  With these 2
libraries you can add sorting to your HTML table with a single line of
javascript code.

If you need to provide pagination through the resultset in your web page
then you need to use one of the 2 approaches you listed.  Using arrays and
keeping the data in php will be faster but you will be limited by php's
available memory as to the size of resultset that you can support this way,
so you will need to add some size checking to your resultset and disable the
php array method if it's too big.  The database query method will always
work regardless of result set size but will generally provide the slowest
results.

Lukass



--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

On Sat, 2009-04-18 at 16:03 +0300, Piotr Mitoraj wrote:

> Hi,
> failing to find the straight-forward answer on the web I hope to find
> some advice in here.
> I want to display the table fetched from the postgres database. The
> headers of the columns are links used to sort the data. Like the
> functionality you have on ebay when you sort according to price or time
> left. I recognized two ways of doing that:
>
> 1. Issue a new query to the database each time the specific sorting was
> requested. In other words, let the database do the sorting and fetch the
> results.
>
> 2. Save the table into php array and and use php built-in functions to
> sort and refresh the table.
>
> Which of those methods would be most desired in terms of performance and
> according to "the best practices"?
> What would be possible drawbacks/advantages of those methods.

Personally I've found it easiest to implement the sorting in database.
The reason this is easier is that the programming involved is just a
small change to the 'ORDER BY' clause in the SQL, whereas the sorting in
PHP would often change the whole way the query results are processed.

Another approach I have seen used, which you don't mention, is to do the
sorting in javascript, client side.  This has the advantage that the
database is only queried the first time and all re-sorting is done
client-side.  In this case sorting on things like time/date data can be
more problematic, and of course you're depending client-side behaviour
also.

Cheers,
                                        Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
          Powering the .NZ namespace with Open Source Software
------------------------------------------------------------------------



--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[Piotr Mitoraj]

Andrew McMillan wrote:

> On Sat, 2009-04-18 at 16:03 +0300, Piotr Mitoraj wrote:
>  
>> Hi,
>> failing to find the straight-forward answer on the web I hope to find
>> some advice in here.
>> I want to display the table fetched from the postgres database. The
>> headers of the columns are links used to sort the data. Like the
>> functionality you have on ebay when you sort according to price or time
>> left. I recognized two ways of doing that:
>>
>> 1. Issue a new query to the database each time the specific sorting was
>> requested. In other words, let the database do the sorting and fetch the
>> results.
>>
>> 2. Save the table into php array and and use php built-in functions to
>> sort and refresh the table.
>>
>> Which of those methods would be most desired in terms of performance and
>> according to "the best practices"?
>> What would be possible drawbacks/advantages of those methods.
>>    
>
> Personally I've found it easiest to implement the sorting in database.
> The reason this is easier is that the programming involved is just a
> small change to the 'ORDER BY' clause in the SQL, whereas the sorting in
> PHP would often change the whole way the query results are processed.
>
> Another approach I have seen used, which you don't mention, is to do the
> sorting in javascript, client side.  This has the advantage that the
> database is only queried the first time and all re-sorting is done
> client-side.  In this case sorting on things like time/date data can be
> more problematic, and of course you're depending client-side behaviour
> also.
>
> Cheers,
> Andrew.
>
> ------------------------------------------------------------------------
> andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
>           Powering the .NZ namespace with Open Source Software
> ------------------------------------------------------------------------
>
>  

Thanks for advice Lukass and Andrew. Tablesorter really made my day!
Previously I was not even considering javascript, but for my purpose
seems to be the best.
Quick links for reference just in case anyone else got interested:
http://tablesorter.com/docs/
http://docs.jquery.com/Main_Page

Piotr

--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[Jasen Betts-5]

On 2009-04-18, Piotr Mitoraj <piotr.mitoraj@...> wrote:

> Hi,
> failing to find the straight-forward answer on the web I hope to find
> some advice in here.
> I want to display the table fetched from the postgres database. The
> headers of the columns are links used to sort the data. Like the
> functionality you have on ebay when you sort according to price or time
> left. I recognized two ways of doing that:
>
> 1. Issue a new query to the database each time the specific sorting was
> requested. In other words, let the database do the sorting and fetch the
> results.

this is the easiest way, and a good start.

> 2. Save the table into php array and and use php built-in functions to
> sort and refresh the table.

unless you're proposing to store the store the array in $SESSION (or
similar) that's not going to work.

also you may get issues if the user opens two different queries in
different windows...

> Which of those methods would be most desired in terms of performance and
> according to "the best practices"?
> What would be possible drawbacks/advantages of those methods.

Here's a third way: use javascript to sort the array.
this gives the best user experience but you should arrange the page
so that those with script turned off can still get the results using
one of the other methods.


--
Sent via pgsql-php mailing list (pgsql-php@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php