[SOLVED] Is it possible to reorder data without running another query?

[matthewst]

I have a page that looks like an excel spread sheet. I need to be able to sort this page by different columns. I could make the coulmn headers links to different pages where the query is "ordered by" the appropriate column. The problem is the data is quit large and it takes a while to load.

 

$Cust_Tables = mysql_query("Select table_id, set_no, dist_id, contact_fname, contact_lname, rest_name, rest_city, rest_state_pr FROM abc_tables where status_2<'6' order by rest_name", $db_link);
	$tables = mysql_num_rows($Cust_Tables);

 

Has anyone done this?

[metrostars]

I don't think it's possible.

 

What you could do however, is to make the section of the page where the table is, refresh using AJAX, so that only the table would refresh instead of having the whole page refreshed.

 

I'm not too good with Javascript so you'll have to ask someone else on this.

[soycharliente]

Make each heading and link and format it like this:

index.php?sort=date

 

$defualt = "user";

$sort = $_GET["sort"]

if (!empty($sort)) {

SELECT * FROM table ORDER BY $sort

} else {

SELECT * FROM table ORDER BY $default

}

 

That will only sort one direction though. IF you want it to switch between ASC and DESC, you'll have to add another variable in the URL and then test to see how you're sorting ATM and change the links accordingly.

 

Hope this helps.

[metrostars]

From what I gather he doesn't want to query the database when he resorts the data, because of bandwidth reason, I doubt it's possible without javascript, if it is at all possible.

[soycharliente]

Use pagination, if he's got SO MUCH data and it takes the page SO LONG to load.

[Psycho]

You can sort a table of data with JavaScript. But, if it takes a long time to do it in PHP, then it might be an issue in JavaScript as well.

 

Here is a page with a JavaScript tutorial if you really want to go to the trouble: http://kryogenix.org/code/browser/sorttable/

 

I have only used it for small tables of data.

[per1os]

I don't think it's possible.

 

What you could do however, is to make the section of the page where the table is, refresh using AJAX, so that only the table would refresh instead of having the whole page refreshed.

 

I'm not too good with Javascript so you'll have to ask someone else on this.

 

It is very possible with PHP. Inefficient but possible.

 

The basic gist is you have all that data in an array. You create your own function to run on that array and sort it using http://us2.php.net/array_walk

 

Not too hard, and I am not sure if it will run fast or not, should be faster than javascript at anyrate.

[matthewst]

thanks everyone, i'll try everything and let you know what works best

[roopurt18]

Sometimes adding the proper index to a table can speed up your queries too.

[matthewst]

Thanks everyone for all the help! I ended up using sorttable.js to sort my data. The only issue I had with it was that it wouldn't sort if the data was created in the table. I had to run all my queries and do all my functions and save all the results to variables. Then echo the variables in the table. The sorttable.js is surprisingly fast at sorting data.

[Psycho]

The only issue I had with it was that it wouldn't sort if the data was created in the table. I had to run all my queries and do all my functions and save all the results to variables. Then echo the variables in the table.

 

No offense, but there must have been an erro in your code. The browser doesn't care one bit if the HTML was constructed by PHP in-line, using variable, whatever. It only cares about the final output. But, glad you got it working. It's a good solution to use when you don't want to put that extra load on your server. But, using JavaScript does have it's drawbacks - i.e. user could have it turned off.

[cooldude832]

your query is a bit bloated

<?php
$Cust_Tables = mysql_query("Select 
table_id, set_no, dist_id, contact_fname, contact_lname, rest_name, rest_city, rest_state_pr
FROM abc_tables where 
status_2<'6' order by rest_name", $db_link);?>

Are all those fields properly made in the table and is your table properly optimized and indexed?

[cooldude832]

What you could do is actually use the while(fetch) idea to write out a javascript array for them and then sort off those arrays (it be an echo command in the loop) however this is a php forum not js so i guess you have to go somewhere to get help on that

[Psycho]

What you could do is actually use the while(fetch) idea to write out a javascript array for them and then sort off those arrays (it be an echo command in the loop) however this is a php forum not js so i guess you have to go somewhere to get help on that

The sorttable.js function will automatically resort a properly formatted HTML table on the fly. No data arrays needed. Very simple, slick tool.