Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 042B0632BD9 for ; Sat, 18 Apr 2009 11:47:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 02291-01 for ; Sat, 18 Apr 2009 11:47:07 -0300 (ADT) X-Greylist: delayed 00:06:39.10189 by SQLgrey-1.7.6 Received: from outbound-mail-16.bluehost.com (outbound-mail-16.bluehost.com [69.89.20.231]) by mail.postgresql.org (Postfix) with SMTP id BC505632559 for ; Sat, 18 Apr 2009 11:47:16 -0300 (ADT) Received: (qmail 14431 invoked by uid 0); 18 Apr 2009 13:39:12 -0000 Received: from unknown (HELO host256.hostmonster.com) (74.220.215.56) by outboundproxy1.bluehost.com with SMTP; 18 Apr 2009 13:39:12 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=lumatila.com; h=Received:From:To:References:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:In-Reply-To:X-MimeOLE:Thread-Index:X-Identified-User; b=oDCw5CqwFpyYUv8lLIOd/d6jnRjfIAFby5f63wab25FfS0Z4F+yTiFLZMKQJ3AUCIFgb0zWjla87KUHxWRgnhehNcErWyYpbkkyFvNUfwgpqx6nq6sAB3H4PCKA6MslB; Received: from [66.96.3.212] (helo=lukasswork) by host256.hostmonster.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.69) (envelope-from ) id 1LvBid-0005cp-9P; Sat, 18 Apr 2009 08:40:35 -0600 From: "L. Franklin" To: "'Piotr Mitoraj'" , References: <49E9CFBF.4050808@gmail.com> Subject: Re: sorting query results Date: Sat, 18 Apr 2009 09:40:34 -0500 Message-ID: <55C5FCC7F739418594160863C4FB589E@lukasswork> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <49E9CFBF.4050808@gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325 Thread-Index: AcnAJiugpJJFnlQ8Tfmhca0ueownGwADDeEQ X-Identified-User: {2061:host256.hostmonster.com:lumatila:lumatila.com} {sentby:smtp auth 66.96.3.212 authed with lfranklin+lumatila.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/9 X-Sequence-Number: 4014 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