Topic awaiting preservation: Sortable Table pulled from Mysql with PHP (Page 1 of 1)

Ok here is the deal.

I want to have a table that is sortable in ascending or descending order depending on how many times I click on it. So hence lets say I have a table with

Name

Why not just go back to the DB?

I can't see any performance issues arising from it, and it'd be a whole hell of a lot cleaner...

Well first off if I go back to the database I have to requery the database, if I used a dhtml method of it I could just resort client side without the need to hit the database. Obviously I would not want to transfer a huge list over the internet multiple times per user, especially if I have hundreds to thousands of users on the website.

Secondly if I use a client side interface I can get the resorting done without the need of refreshing the page. Obviously I could sink my table into an iframe or the like to only refresh the table frame, but again I'd have to hit the server.

I'm trying to get where I can pull thousands of rows from the mysql server once, and resort ASC or DESC on any of the 5+ columns without hitting the sql server again unless I need to narrow, widen, or refine the search in any way.

Carnage: Hmmmmmmmm its an interesting one - if I wanted to look at the first page and had to wait for 100 pages to download then I doubt I would be very happy. You can do this client-side but you could start getting into trouble. The 2 solutions I'd consider are:

1. Grabbing all the details and loading them into an array (and then pos. storing this along the lines of Application Objects) - this would depend on the structure of your data and the way it will be pulled out. It could make things slightly more efficient but it would depend (it also depends on if you have restricted MySQL queries as you do at Dreamhost).

2. I'd simply use LIMIT and change the ORDER BY field DESC/ASC and just send only the specific data for the page required.

If done client side whatever happens you would have to send the maximum amount of data over the Internet (i.e. all your pages) which is slow and inefficient. Also by using DHTML (I've seen it done) you might run into browser-related problems making it inaccessible to some people.

Also think about how virtually everyone else does this? They would do it server-side because (except for some freak occurence) it is usually the best solution to the problem. I'm not going to stop you doing it this way and I can point you to resources that might help but I'd highly recommend you stay server-side for this.

___________________
Emps

The Emperor dot org

quote:

---

Well first off if I go back to the database I have to requery the database

---

But what's wrong with that?

I didn't realize some hosts restrict your database queries. Is that the case in your situation? If not, I'd say you're still better off going back to the database than trying to manipulate this much info client side.

Yeah I currently have it implemented as #2. I just move my start variable for use in LIMIT forwards or backwards when they change pages. And if they want to sort by a diff column ASC or DESC I do it via the SORT in the mysql pull. Was just wondering if there was a way to do this without hitting the database every time. Maybe a cachable version of the SQL query that could be implemented client side but on a cached txt file or the like that would expire in X amount of time, or by doing it client side.

The only other way I could see without refreshing the entire webpage would be to take the table I want to refresh and insert it into it's own frame or iframe of a sort and let it refresh at will thus not refreshing the entire page only the portion that needs to be refreshed.

The nice thing about client side is I could pull the entire lets say 100 pages from the server, and if I happened to go offline I could do some sorting or whatnot without being online.

Carnage: There may be design restrictions that you haven't outlined but if you use LIMIT (rather than some terrible waste of resources like grabbing them all and then using PHP to pump out the required output) then that is a reasonably flexible and efficient use of MySQL.

Have a look at 'Application Objects' in PHP:

:FAQ: Is there a PHP equivalent to ASP's "Application object"?

As, depending on your data structure and its usage, this may be a more efficient way of doing things (esp. if MySL queries are restricted).

___________________
Emps

The Emperor dot org

Yup,

I agree with DL and Emps on this one, mysql might not be the fastest db around, but it's fast enough to keep firing queries. If not, change your query or your db-layout.

And trying to store all info and then somehow save this for future use... it'll certainly be a pain in the ass. Back in the days, when databases were reallllll slow, people used to build crappy applications in order to limit db-traffic.
Fortunately, these days are long gone . So just stick with LIMIT and ORDER BY, and you'll be fine .
And that's actually an advice I still tend to forget myself so now and then, to let the DB handle everything it is capable of, instead of developing software solutions.

I don't really get this offline-point, what's the deal here? Are you afraid your visitors will have to pay too much money while viewing your site, or...?

Night night now

Yep and its worth pointing out that MySQL operates faster than PHP so it is always worth getting it to do the processing for you if you can.

___________________
Emps

The Emperor dot org

hm... quick fix: two divs, one sorted asc, one desc, hide the other one when the user clicks.
Now, this is only economical for two combinations, but if my specs said 'sort by column x either asc or desc while hitting the database only once', that's what I'd do. (read rows into array then use array_reverse to output it in reverse order. be warned, this only works up to 8 mb of memory consumption. after that, php kills your script)


ps: mysql has a pretty good query chache... so I really wouldn't worry about performance or traffic... since you have to access the database for every 20 row block again anyhow.

[This message has been edited by Tyberius Prime (edited 10-07-2003).]