?
Solved

PHP MySql , changing order from ascending to descending.

Posted on 2005-05-10
3
Medium Priority
?
1,530 Views
Last Modified: 2006-11-18
I currently have a Database that holds a list of movies I own. Im using php and MySql.

The following is what I have so far:

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

if (!isset($_GET['genre'])){
$query="SELECT * FROM table1 ORDER BY name ASC";
}
else if (isset($_GET['genre'])){
$selected_genre = $_GET['genre'];
echo $selected_genre;
$query="SELECT * FROM table1  WHERE genre='$selected_genre' ORDER BY name ASC";
}
else
{
$query="SELECT * FROM table1 ORDER BY name ASC";
}



$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
function select_order (){
      echo "im gay";
      }
?>



<table cellpadding="3" border="1" cellspacing="3">
      <tr>
            <td><b><a href="" onClick="select_order();">Name:</span></b></td>
            <td><b>Genre:</b></td>
            <td><b>Year:</b></td>
            <td><b>Date Entered:</b></td>
      </tr>

<?php
$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name");
$genre=mysql_result($result,$i,"genre");
$year=mysql_result($result,$i,"year");
$link=mysql_result($result,$i,"link");
$date_added=mysql_result($result,$i,"date_added");
?>

 <tr>
       <td><?php echo "<a href=\"$link\">$name</a>" ?></td>
      <td><?php echo "$genre" ?></td>
      <td><?php echo "$year" ?></td>
      <td><?php echo "$date_added" ?></td>
</tr>

<?php
$i++;
}
echo "</table>";
?>


What I would like to be able to do is, click on the header for the table NAME, and have the list re-populate in ascending and descending order. I am a little confused on how to do this.

Thanks.
0
Comment
Question by:ohhhnick
3 Comments
 

Author Comment

by:ohhhnick
ID: 13966007
Also to add, i would perfer to not use the $_GET method, as i am using it for a jump menu to organize by genre, unless there is somehow to have them not conflict with each other. here is the code to the jump menu.
<form name="genre">
  <select name="Genre">
    <option value="">Select A Genre</option>
      <option value="index.php">All</option>
      <option value="?genre=horror">Horror</option>
    <option value="?genre=comedy">Comedy</option>
    </select>
  <input type="button" name="Button1" value="Go" onClick="MM_jumpMenuGo('Genre','parent',1)">
</form>
0
 
LVL 12

Expert Comment

by:mmc98dl1
ID: 13966550
set up a variable and param it to be DESC as you default.

Put this into your queries instead of having DESC.

Put a link back to the page on the date header in the table, and if the var = DESC print ASC else print DESC.

You will also need to tack on an isset(genre) if so then add &genre=$_GET['genre']

This appies vice versa with the form, if sort is specified then put in a form hidden var to specify this.
0
 
LVL 32

Accepted Solution

by:
ldbkutty earned 1000 total points
ID: 13967540
GET variables are the formal way of sorting.

<?php

$link = mysql_connect("localhost", $username, $password) or die("Could not connect: " . mysql_error());
mysql_select_db($database) or die( "Unable to select database: " . mysql_error());

// Sorting by selection.  
$key_column = isset($_GET['sort_by']) ? intval($_GET['sort_by']) : 1;

$db_columns_list = array(1 => "name", 2 => "genre", 3 => "year", 4 => "date_added");
$sort_by = $db_columns_list[$key_column];

// Selecting order.
$order_by_str = (isset($_GET['order_by']) && intval($_GET['order_by']) == 1) ? "ASC" : "DESC";

// Convert the order_by to integer so as to navigate.
$order_by = (strtolower(trim($order_by_str)) == "asc") ? -1 : 1;

// Default ordering.
if(!isset($_GET['sort_by']) && !isset($_GET['order_by']))
{
      $sort_by      = "name";
      $order_by_str = "ASC";      
}

if(isset($_GET['genre']))
{
      $selected_genre = $_GET['genre'];
      echo $selected_genre;
      $query = "SELECT * FROM table1 WHERE genre = '" . mysql_real_escape_string($selected_genre) . "' ORDER BY $sort_by $order_by_str";
}
else
{
      $query = "SELECT * FROM table1 ORDER BY $sort_by $order_by_str";
}

$result = mysql_query($query) or die("Sql error: " . mysql_error());

$num = mysql_num_rows($result);

mysql_close();

?>

<table cellpadding="3" border="1" cellspacing="3">
     <tr>
          <td><b><a href="?sort_by=1&order_by=<?php echo $order_by; ?>">Name:</a></b></td>
          <td><b><a href="?sort_by=2&order_by=<?php echo $order_by; ?>">Genre:</a></b></td>
          <td><b><a href="?sort_by=3&order_by=<?php echo $order_by; ?>">Year:</a></b></td>
          <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Date Entered:</a></b></td>
     </tr>

<?php

$i=0;
while ($i < $num)
{
      $name = mysql_result($result, $i, "name");
      $genre = mysql_result($result, $i, "genre");
      $year = mysql_result($result, $i, "year");
      $link = mysql_result($result, $i, "link");
      $date_added = mysql_result($result, $i, "date_added");
?>

 <tr>
     <td><?php echo "<a href=\"$link\">$name</a>" ?></td>
     <td><?php echo $genre ?></td>
     <td><?php echo $year ?></td>
     <td><?php echo $date_added ?></td>
</tr>

<?php

      $i++;
}
echo "</table>";

?>

And for stopping the conflict with your other genre form, change that form method to POST.

<form name="genre" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<select name="Genre">
  <option value="">Select A Genre</option>
   <option value="index.php">All</option>
   <option value="?genre=horror">Horror</option>
  <option value="?genre=comedy">Comedy</option>
</select>
<input type="button" name="Button1" value="Go" onClick="MM_jumpMenuGo('Genre','parent',1)" />
</form>

If you intended to have GET method for the form, you can use $_SERVER['QUERY_STRING'] to get the already existing URL queries and append it to the sorting anchor tag / genre form action.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question