Sorting and diplaying query results

teleute

Hi all;

I'm using PHP to create a search form that queries a mySQL database (they can search on any combination of first name, last name, zip, phone, etc...). Everything's working perfectly on that end, so now I need to add the ability to sort the results by the different columns (name, phone, etc...). My understanding is that, especially with a large database, it would be much quicker to read all this info into a PHP array and sort that, rather than rerun the query with a different ORDER BY value. I've gone ahead and put the data in a 2-dimensional array - each row of the outer array is a record with a person's info. I'm sorting it by a simple usort function. But my question (other than am I on the right track?) is: what's the best way to reload the page and display the new data set when the person clicks a column to sort? Thanks.

kburger

Let the database do the sorting. It'll be faster than letting PHP do the sorting. Use the ORDER BY clause. Just create an $orderby variable and make each column header be an href to ?orderby=name or ?orderby=phone or whatever.

teleute

Really? Everything I'd seen suggested that the database would be slower, especially with hundreds of thousands of records.

If I did do it through the database, wouldn't I have to pass not just the orderby variable, but all of the user's search terms as well? (i.e. lastname=jones, etc...) There are quite a few search term possibilities, and I'd be afraid of running over the character limit.

Thanks! :-)

kburger

When the page is reloaded your arrays will have to be reloaded from the database, so you really haven't saved anything by loading them into an array since you can't re-order the array without reloading the page (unless you do it via javascript, which I wouldn't recommend).

And yes, you'd need to pass the original search terms also. If I'm not mistaken, the GET limit is 4,096 characters. I wouldn't think you'd need that much, would you?

Anybody else want to chime in?

teleute

Why would the array need to be reloaded from the database? Isn't there some way I could pass the PHP array as I refresh the page?

As to the GET limit, I'd read 256. Plus there's the possibility of spaces and various other special characters in the search terms that seemed like they might make GET a bit more complicated.

kburger

If your array is as big as it sounds like it is, I don't think you'd want to pass it around. Obviously, it would have to be via POST if you did that. But I think that's probably a bad idea to do. That's a lot of data to submit. I still say let the database do the work for you.

PHP has lots of useful funcitons to make the URL safe. Check out urlencode() and urldecode()

teleute

Well, the database is hundreds of thousands of records, but the search results will very rarely return more than 50 records (if it does, they should put in another search parameter anyway, not just sort).

bradgrafelman

What you could certainly do is tell your users that "Hey, we have a large DB. If your search returns more than 50 results, you only get the first 50!".

Then, tag on " LIMIT 50" to your SQL query. Now, if you persist on storing those 50 results somewhere, you might even try sessions/cookies/etc. Sessions wouldn't be too hard... just cram all the results into an array or two. Not sure if this is very efficient.. someone else will have to chime in on that.

With as many records as you say you have, I would most definitely use "WHERE firstname='$search_firstname'" etc, and then at the end a "LIMIT 50". Then, if they wanted to look at the next 50... make up a system that 'chunks' the results, so-to-speak, i.e. "LIMIT $currpage, $currpage+50".