Sorting Database Results

[Merc]

Hello everyone, I've managed to sort my tutorials using $_GET, but the problem I'm having is that I can't think of a way to allow the user to specify how they would like to sort them without typing what they want in the address bar.

Here is my page

And you type this to sort by date descending in the "Test" category: www.puresportgfx.com/tutorials/tutorials.php?category=Test&sort=date&order=desc

Here is my code:

 <?php

$mysql_host = "mysqlhost";

$mysql_user = "user";

$mysql_pass = "password";

$mysql_data = "date";

$mysql_table = "tutorials";


mysql_connect($mysql_host,$mysql_user,$mysql_pass) or
die("Error connecting to Database! Please Try again.
" . mysql_error());
mysql_select_db($mysql_data) or die("Cannot select database! Please Try again.
" . mysql_error());


//Setting up a variable to request the category which is in the URL
$request_cat = $_REQUEST['category'];

//Displaying an actual tutorial
if (isset($id)) {

// Updating the views, and grabbing a tutorial based on what id it has.
$update = mysql_query("UPDATE $mysql_table SET views = views + 1 
WHERE id='$id'");
$result = mysql_query("SELECT * FROM $mysql_table
WHERE id='$id'");
while($row = mysql_fetch_array($result)){

  // Template for the tutorial
  echo "
  <table width='400' border='0' cellspacing='0' cellpadding='0'>
<tr>
  <td width='400'>
<table width='400' border='0' cellspacing='0' cellpadding='0'>
<tr>
<td><table width='400' border='0' cellspacing='0' cellpadding='0'>
    <tr>
      <td width='275'><div align='left'><strong>
<a href='tutorials.php?category=".$row['category']."&id=".$row['id']."
'>".$row['title']."</a>
</strong></div></td>
      <td width='125'><div align='right'>
[ Views: ".$row['views']." ]</div></td>
    </tr>
      </table></td>
    </tr>
<tr>
<td><div align='left'>Description: ".$row['description']."</div></td>
    </tr>
<tr>
<td><div align='left'>".$row['content']."</div></td>
    </tr>
<tr>
<td><div align='left'>
    <table width='400' border='0' cellspacing='0' cellpadding='0'>
      <tr>
      <td width='150'><div align='left'>Added: ".$row['date']."</div>
</td>
  <td><div align='right'>Author:
<a href='mailto:".$row['email']."'>".$row['author']."</a> </div></td>
      </tr>
    </table>
      </div></td>
    </tr>
<tr>
  <td><div align='left'>Category:<a href='tutorials.php?category=".$row['category']."'>".$row['category']."</a></div></td>
    </tr>
  </table></td>
    </tr>
  </table><br>
    <form name='rating' method='post' action='ratetut.php?id=".$row['id']."'><br>
    <b>Rate this download:</b><br>
    <select name='rating'>
    <option value='5.0' selected='selected'>5</option>
    <option value='4.0'>4</option>
    <option value='3.0'>3</option>
    <option value='2.0'>2</option>
    <option value='1.0'>1</option>
    <option value='0.0'>0</option>
	</select>
	<input type='submit' value='Submit'>
    <input type='hidden' name='cmd' value='do_rating'>
    <input type='hidden' name=\"$_GET[id]\" value=\"$_GET[id]\">
</form>
  "; // End of template of tutorial
} // End of grabbing data

$getcount = mysql_query("SELECT * FROM tutorials 
where id='$_GET[id];'");
while ($result=mysql_fetch_array($getcount)){ 
$rating = $result[rating];
	echo "Current Rating:";
	if((($rating >= 0)or($rating == 0)) && ($rating <= 0.50)){
        echo "<img src='/images/stars/0.png' width='90' height='20'>";
    }
    if((($rating >= 0.50)or($rating == 0.50)) && ($rating <= .99)){
        echo "<img src='/images/stars/0point5.png' width='90' height='20'>";
    }
    if((($rating >= 1.00)or($rating == 1.50)) && ($rating <= 1.49)){
        echo "<img src='/images/stars/1.png' width='90' height='20'>'";
    }
    if((($rating >= 1.50)or($rating == 1.50)) && ($rating <= 1.99)){
        echo "<img src='/images/stars/1point5.png' width='90' height='20'>";
    }
    if((($rating >= 2.00)or($rating == 2.00)) && ($rating <= 2.49)){
        echo "<img src='/images/stars/2.png' width='90' height='20'>";
    }

if((($rating >= 2.50)or($rating == 2.50)) && ($rating <= 2.99)){
    echo "<img src='/images/stars/2point5.png' width='90' height='20'>";
}

if((($rating >= 3.00)or($rating == 3.00)) && ($rating <= 3.49)){
    echo "<img src='/images/stars/3.png' width='90' height='20'>";
}

if((($rating >= 3.50)or($rating == 3.50)) && ($rating <= 3.99)){
    echo "<img src='/images/stars/3point5.png' width='90' height='20'>";
}
if((($rating >= 4.00)or($rating == 4.00)) && ($rating <= 4.49)){
    echo "<img src='/images/stars/4.png' width='90' height='20'>";
}
if((($rating >= 4.50)or($rating == 4.50)) && ($rating <= 4.99)){
    echo "<img src='/images/stars/4point5.png' width='90' height='20'>";
}
if($rating  == 5.0){
    echo "<img src='/images/stars/5.png' width='90' height='20'>";
}
  exit(); // Stopping anything below from interfering with this section
} // End of Displaying an actual tutorial
}
//Displaying tutorials in a category.
if ($category == $request_cat) {

$sort = "$_GET[sort]" ;
switch( $sort )
{
  case 'author';
    $sort_column = 'author';
    break;
  default;
    $sort_column = 'title';
    break;
  case 'id';
    $sort_column = 'id';
	break;
  case 'rating';
  	$sort_column = 'rating';
	break;
  case 'date';
  	$sort_column = 'date';
	break;
}

$order = "$_GET[order]" ;
switch( $order )
{
  case 'asc';
  	$sort_abc = 'ASC';
	break;
  default;
  	$sort_abc = 'DESC';
	break;
}

//Grabbing information from the database.
$result = mysql_query("SELECT * from $mysql_table where category = '$category' ORDER BY $sort_column $sort_abc");

while($row = mysql_fetch_array($result)) {
  //Template for the tutorials in a category.
  echo "
  <table width='400'  border='0' cellspacing='0' cellpadding='0'>
<tr>
  <td width='100' align='center' valign='middle'><div align='center'>
<a href='tutorials.php?category=".$row['category']."&id=".$row['id']."'>
<img src='".$row['avatar']."' alt='".$row['title']."'  title='".$row['title']."'
width='90' height='60' border='0'></a>
</div></td>
  <td width='300'>
<table width='100%'  border='0' cellspacing='0' cellpadding='0'>
    <tr>
<td>
<a href='tutorials.php?category=".$row['category']."&id=".$row['id']."'>
<div align='left'><strong>".$row['title']."</strong></div></a></td>
    </tr>
    <tr>
<td>
<div align='left'>Description: ".$row['description']."</div></td>
    </tr>
    <tr>
<td>
<table width='100%'  border='0' cellspacing='0' cellpadding='0'>
  <tr>
    <td><div align='left'>Added: ".$row['date']."</div></td>
    <td><div align='left'>Category:
<a href='tutorials.php?category=".$row['category']."
".$row['category']."</a></div></td>
    </tr>
    </table></td>
    </tr>
    <tr>
<td><table width='100%'  border='0' cellspacing='0' cellpadding='0'>
  <tr>
    <td width='175'><div align='left'>Author:
<a href='mailto:".$row['email']."'>".$row['author']."</a></div></td>
    <td width='100'><div align='left'>Views: ".$row['views']."</div></td>
      </tr>
    </table></td>
    </tr>
  </table></td>
    </tr>
  </table>
  <br>
  "; // End of Template for the tutorials in a category.
} // End of grabbing information from database.
} // End of Displaying tutorials in a cateogry.
// Main Tutorial page.  This is where all your categories will be listed. 
if (!$category) {

//This makes a list of all the cateogries that are in the database!  No need to make the links yourself!
$result = mysql_query("SELECT DISTINCT category from $mysql_table ");
while($row = mysql_fetch_array($result)) {

  // Showing all the categories
  echo "
  <a href='tutorials.php?category=".$row['category']."'
".$row['category']."</a><br>
  "; // End of showing all the categories
} // End of grabbing unique categories in database
}// End of main tutorial page
?>

If someone could help with dropdown boxes to select how the tutorials are sorted and are only displayed on the category page, I would become your boytoy for a couple of minutes. :rolleyes:

Thanks!

[bradgrafelman]

Couple of things:

$order = "$_GET[order]" ; 

The above code snippet is incorrect in syntax. See the man page for what a [man]string[/man] is, and, more specifically, how to reference array indeces.

Secondly, why not just do this:

$sort_abc = strtoupper(@$_GET['order']);
if($sort_abc != 'ASC' && $sort_abc == 'DESC')
     $sort_abc = NULL; // you can use NULL & let MySQL sort, or put a default value here

$result = mysql_query("SELECT * from `$mysql_table` where `category` = '$category' ORDER BY `$sort_column` $sort_abc");

EDIT: Also, what exactly is your problem? Is it not sorting? Are you getting error messages? Did you check for MySQL errors?

[Merc]

bradgrafelman wrote:

Couple of things:

$order = "$_GET[order]" ; 

The above code snippet is incorrect in syntax. See the man page for what a [man]string[/man] is, and, more specifically, how to reference array indeces.

Secondly, why not just do this:

$sort_abc = strtoupper(@$_GET['order']);
if($sort_abc != 'ASC' && $sort_abc == 'DESC')
     $sort_abc = NULL; // you can use NULL & let MySQL sort, or put a default value here

$result = mysql_query("SELECT * from `$mysql_table` where `category` = '$category' ORDER BY `$sort_column` $sort_abc");

EDIT: Also, what exactly is your problem? Is it not sorting? Are you getting error messages? Did you check for MySQL errors?

Basically, I can't think of a way to allow the user to sort them based on their own personal preference.

[jatinder]

Just add a link somewhere:

<a href="tutorials.php?category=Test&sort=date&order=desc">Sort By Date</a>

The users can click this link to sort the listings.