Sort order problem

Ogden2k · 06-07-2005, 05:03 PM

I have a little game database, I am trying to sort the data (per a user click) by title, or by systme or by whatever the user specifies. Here's the code that I have, it's not sorting:

PHP Code:


			
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> 
<head> 
    <title>Games Database</title> 
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> 
    <link rel="stylesheet" type="text/css" href="http://misc.techiehq.net/stuff/css/games.css" /> 
</head> 
<body> 
<div class="body_fore"> 

<div class="title">
Games:
</div> 

<table class="table_head"> 
<tr> 
     <td width="85">System <a href="index.php?order=system">^</a> v</td> 
     <td width="200">Title <a href="index.php?order=title">^</a> v</td> 
     <td width="110">Developer ^ v</td> 
     <td width="140">Publisher ^ v</td> 
     <td width="140">Year ^ v</td> 
     <td width="140">Purchased ^ v</td> 
     <td width="88">Genre ^ v</td> 
     <td width="120">Serial ^ v</td> 
</tr> 
</table>
 
<?php
// Connect to the DB server 
$dbcnx = @mysql_connect('localhost', 'x', 'x'); 
    if (!$dbcnx) { 
    exit('<p>Unable to connect to the ' . 
    'database server at this time.</p>'); 
} 
// Select the binarydr_stuff DB 
    if (!@mysql_select_db('binarydr_stuff')) { 
    exit('<p>Unable to locate the stuff ' . 
    'database at this time.</p>'); 
}
// Request the text of all the info 
$result = @mysql_query('SELECT * FROM games'); 

if (!$result) { 
    exit('<p>Error performing query: ' . mysql_error() . '</p>'); 
}
// Display each game
while ($row = mysql_fetch_array($result)) { 
echo ' 
<table class="cells"> 
<tr> 
     <td width="85">' . $row['system'] . '</td> 
     <td width="200">' . $row['title'] . '</td> 
     <td width="110">' . $row['developer'] . '</td> 
     <td width="140">' . $row['publisher'] . '</td> 
     <td width="140">' . $row['year'] . '</td> 
     <td width="140">' . $row['purchased'] . '</td> 
     <td width="88">' . $row['genre'] .'</td> 
     <td width="120">' . $row['serial'] .'</td> 
</tr> 
</table> 
'; 
}
$order = isset($_GET['order']) ? mysql_escape_string($_GET['order']) : 'system'; // Request the text of all the info
    $result1 = @mysql_query(
    "SELECT system,title,developer,publisher, year,purchased,genre,serial FROM games 
    ORDER BY $order DESC"
);
// When clicked, this link will load this page with the hook sub form displayed 
echo '<div class="button_area"> 
<p>[ <a href="add.php">Add a Game</a> | <a href="../search/index.php">Search</a> | <a href="../">Home</a> | <a href="' . $_SERVER['PHP_SELF'] . '">Refresh</a> ]</p></div> 
</div> 
</div>';
?> 
</body> 
</html>

jaswinder_rana · 06-07-2005, 05:10 PM

TRY changing this line (which is i think line number 42)

PHP Code:


			
$result = @mysql_query('SELECT * FROM games');

with (which are 64-68)

PHP Code:


			
$order = isset($_GET['order']) ? mysql_escape_string($_GET['order']) : 'system'; // Request the text of all the info

    $result1 = @mysql_query(

    "SELECT system,title,developer,publisher, year,purchased,genre,serial FROM games

    ORDER BY $order DESC"

);

Line numbers can be wrong. so, take care

i think all you did is put the query at wrong place. just need to exchange it

Suggestion
don't order by the input from url. what if i changed the url manually and changed to Something_wrong. this will give error and your query won't execute

try, using switch statement as

PHP Code:


			
  $order = '';

  switch($_GET['order'])

  {

    case 'order':

     $order = 'order';

     break;

    case 'system':

     $order = 'system';

     break;

    default:

     $order = 'order';

     break;

  }

this will take the default one if user changes the link manually. people (like me) try to do it just to see how the code handles it??

Ogden2k · 06-07-2005, 05:34 PM

I took your suggestion, that's a better way of going about it. I also fixed the default view / sorting too. Thank you for the help.

jaswinder_rana · 06-07-2005, 05:35 PM

glad to help you

Ogden2k · 06-07-2005, 06:52 PM

Hmf, I have been trying to get this resolved but it doesn't seem to work... I have created a link for them to be sorted ASC which works, but DESC will not work.

Tried setting up another case and result array but it did not work. Any pointers?

jaswinder_rana · 06-07-2005, 07:08 PM

Using 2 different techniques (ASC & DESC) is not impossible, but its going to take a lot of tihinking

for example

page.php?order=name&method=asc
page.php?order=phone&method=asc
page.php?order=postal&method=asc

say, i have above 3 links.so, for sorting you already got it and ASC is the default. now, if you want to do desc too, then when you choose the order you also need to change the method to DESC for next time.

PHP Code:


			
switch($_GET['order'])

{

 case name:

  $order = 'name';

  $method = ($_GET['method'] == 'ASC')?'DESC:'ASC';

}



$query = "select * from table order by $order $method";

So, for next time now you have to change THAT PARTICULAR link to DESC, so when next time you click it, it also orders but this time it orders by DESC

i know its not clear. but, i couldn't find an example to support it