• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1615
  • Last Modified:

PHP MySql , changing order from ascending to descending.

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
ohhhnick
Asked:
ohhhnick
1 Solution
 
ohhhnickAuthor Commented:
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
 
mmc98dl1Commented:
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
 
ldbkuttyCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now