[SOLVED] PHP/MYSQL - Dealing Efficiently w/ Results!

[musclehead]

Hello everyone,

 

I have a number of pages which retrieve and display the records in a table - upwards of 1000 records/table and growing.

 

I want to find an efficient way to sort those results. Right now, the "sort" links are links next to each field name which send GET variables to the original page and re-query the database, sorting by the field selected.

 

This works fine and dandy, but it takes forever...well, ok, not forever...but 15-20 seconds...to re-query the table. I'm wondering if there is a more efficient way to do this...storing the records in an array, perhaps...but I haven't had any success there either. I'm just trying to reduce the time it takes to re-sort the results without re-querying the database.

 

Thanks everyone!

[Psycho]

If ALL the results are displayed ont he page (i.e. you are not paginating the results) there are JavaScript solutions that can resort a table dynamically. I don't know what the performance would be for many thousands of records, but I would think it would be much faster than 15-20 seconds.

 

But, it sounds like you have an inefficient query. You might want to investigate how to make it more efficient.

[PHP_PhREEEk]

We need code examples of what you're trying to do... unless you want us to just start guessing!

 

PhREEEk

[musclehead]

The code is simple - that's the problem.

 

I have a basic query.php file:

 

<?
$query = mysql_query("SELECT * FROM table");
$count = mysql_num_rows($query);
$i = 0;
while ($i < $count){
  echo mysql_result($query,$count,"fieldname");
}
?>

 

When a user clicks the button to SORT the results, I re-execute the query as so:

 

URL Example: query.php?sort=DESC&column=col2

 

<?
$sort = $_GET['sort'];
$col = $_GET['column'];
$query = mysql_query("SELECT * FROM table ORDER BY $col $sort");
$count = mysql_num_rows($query);
$i = 0;
while ($i < $count){
  echo mysql_result($query,$count,"fieldname");
}
?>

 

This, I'm sure, is not the most efficient way I can do this...and it takes some considerable time to re-query a large table with many records. Any ideas?

[Psycho]

I doubt that is the code you are using. There is no table or formatting of any kind. There could be inefficiencies in how you are creating the data on the page.

 

But, based upon what you did provide, yes it is inefficient. For one you have a while loop that must do a comparison on every itteration and then you still have to extract the data. Plus, the manual states that mysql_result is a poor choice for larger result sets.

 

Also, I don't see why you have two sets of code. Just use the same set of code and set the defautl sort if none is chosen:

 

<?php

$sort = ($_GET['sort']=='DESC')?'DESC':'ASC';
$col  = ($_GET['column'])?$_GET['column']:'DEFULAT_SORT_FIELD';

$query = "SELECT * FROM table ORDER BY $col $sort";
$result = mysql_query($query);

while ($record < mysql_fetch_array($result)){
  echo "<tr>
        <td>$record['field_1']</td>
        <td>$record['field_2']</td>
        <td>$record['field_3']</td>
        </tr>\n";
}

?>