Thread: 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. Regards, Piotr
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@theMode.com) Check out my Stock Option website http://www.callpix.com and my Musicians Free Classified http://www.TheMode.com
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.
Piotr, -----Original Message----- From: Piotr Mitoraj [mailto:piotr.mitoraj@gmail.com] Sent: Saturday, April 18, 2009 8:04 AM To: pgsql-php@postgresql.org 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
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
------------------------------------------------------------------------
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
On 2009-04-18, Piotr Mitoraj <piotr.mitoraj@gmail.com> 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.