| |||||||||||
To sort in PHP or MySQL? Posted by admin (Graham Ellis), 8 January 2006 A very common requirement on a web page is to provide your user with the results of a search, or a listing of products or options, that you've extracted from a database. Where there are never more than two or three options available, you can just display them one after another and not worry too much about the order in which they appear, but if you've half a dozen or more options you'll want to have them appear in order (sorted).Both PHP and MySQL provide you with sorting capabailities - which should you use? In general, it's better to have your MySQL perform the selection of the records that your require, using where and limit clauses on your select statements. Quite simply, sorting isn't usually very efficient but MySQL is designed to optimise the process as best as possible. It's also more efficient because it means that you're not passing - potentially - a huge amount of data back to the PHP element of your system just for it to throw a lot of it away. There are exceptions, though. Although stored procedures are just coming into the latest releases of MySQL, it's NOT really a full programming language and so it may not be possible for you to make your MySQL perform your most complex of sorts, and in this case you'll find it far better to pass back all the records that need sorting to your PHP element and have that do the sorting. Occasionally, you'll have a requirement to run a well defined but complex standard sort; I came across one a while back which required addresses in the USA to be sorted by state, starting in the East and moving Westward. Initially, I performed the sort in my main code (it was actually Perl rather than PHP, but the principle's the same). Then I added an extra column to the data which provided a "sort order" value onto each of the addresses - just a number based on the state, but capable of being altered so that the Head Office for each region could be prioritised if I wanted. A further improvement (not made IRL) would be to add an extra table with two columns - the state code and its westness; the sort could then be made in the MySQL and based on a join. This page is a thread posted to the opentalk forum
at www.opentalk.org.uk and
archived here for reference. To jump to the archive index please
follow this link.
|
| ||||||||||
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho |