Storing query result for later use

aaron_karp

I'm running a really big, intensive query, but I want the output to be sortable by the user. But to run the same query again every time (with the only change being the ORDER BY part) seems extremely wasteful.

Do you guys have any suggestions of how to best store the results of the query (session var?) and then sort them each time they're requested according to the user's preference, since I can't use mysql's ORDER BY function w/o running the query again?

My current thought is to store all the info in my own array the first time the query is run, keeping track of all the key/value pairs. Then I'll store that in a session variable. If a sort is requested, rather than run the query I'll just loop sort the array and loop through it.

But I feel like you guys probably have a better solution. Or see a flaw with my solutions. Either way, any help is appreciated!

Mordecai

Yep, session is probably the best way. Keep in mind that if it's a large array, though, sorting will take a while.

aaron_karp

Yeah it's huge....In fact I'm getting this error:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 44 bytes) in Unknown on line 0

Is this from my query, or from trying to store all of the data like this: $data[$key][count($data[$key])] = $value;

Do you recommend letting mysql query it and sort it, even if it takes 15 seconds, or do you think keeping the array in a session variable is a better idea? (if it even works)

tommynanda

I would use one of the following two options

Store Query results in txt file and sort them into an array whenever user requests... this is much similar to ORDER BY MySQL.. no big change...

Second one might be... dumping Query results on client side...
store your results into javascript array.. so when ever user wants to sort they can sort Javascript array than server side array.. no load on server... or you can use a XML dump too.. but I would strongly recommend passing your values to Javascript array

Hope this helps

TommYNandA

aaron_karp

Thanks. Both of those ideas are really good.

I've got the session/array version working now, and tomorrow I'll have a go at the others.

Thanks again.