Closed Thread Icon

Topic awaiting preservation: Sortable Table pulled from Mysql with PHP (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=8890" title="Pages that link to Topic awaiting preservation: Sortable Table pulled from Mysql with PHP (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: Sortable Table pulled from Mysql with PHP <span class="small">(Page 1 of 1)</span>\

 
Carnage
Nervous Wreck (II) Inmate

From:
Insane since: May 2001

posted posted 10-06-2003 17:47

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

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-06-2003 19:08

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...

Carnage
Nervous Wreck (II) Inmate

From:
Insane since: May 2001

posted posted 10-06-2003 19:14

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.

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 10-06-2003 20:16

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

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-06-2003 21:13
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.

Carnage
Nervous Wreck (II) Inmate

From:
Insane since: May 2001

posted posted 10-06-2003 21:27

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.

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 10-06-2003 22:11

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

Nevel
Bipolar (III) Inmate

From: Amsterdam
Insane since: Jun 2002

posted posted 10-07-2003 01:46

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 10-07-2003 02:26

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

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 10-07-2003 09:37

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).]

« BackwardsOnwards »

Show Forum Drop Down Menu