CODEWITHSUNDEEP
phphtmldrop-down-menusql-order-by
Phil
Phil

Reputation: 1453

PHP - sorting a list using a drop down menu

I am stuck with this, I have been putting myself through a crash course in PHP all week making a fake amazon like site! I want to order my table using a drop down menu but cant work out how! I know the SQL thats the easy bit but the way I have tried to do it is follows:

Set up variables for example

$query = "SELECT bookname, bookauthor, bookpub, bookisbn
                FROM booktable
                ORDER BY bookname ASC";

$query2 = "SELECT bookname, bookauthor, bookpub, bookisbn
            FROM booktable
            ORDER BY bookauthor ASC";

Then I have my form=

<form name="sort" action="products.php" method="post">
<select name="order">
   <option value="choose">Make A Selection</option>
   <option value="title">Title</option>
   <option value="author">Author</option>
   <option value="publisher">Publisher</option>
   <option value="isbn">Book ISBN-10</option>
</select>
<input type="submit" value=" - Sort - " />
</form>

Now for the bit I cant do,

I set up the variable: 

$sort = $_POST['order'];

so that whatever the value of the dropdown menu is, will become assigned to the variable $sort..... (is that right?)

then I want to say IF the value of $sort is the same as lets say 'author' than $query = $query2 but I dont know how to say it without huge error courtesy of XAMPP!

I hope this all makes sense and that my method to try and achieve it is close to correct

Thank you

Upvotes: 2

Views: 20468

Answers (3)

Avanche
Avanche

Reputation: 1820

To sort I would do the following:

Change the option values to match the column names from your table Set your sort option including a default if they haven't selected anything:

$sort=$_POST["order"]!="choose"?$_POST["order"]:"bookname";

then simply set your query to use:

ORDER BY ".mysql_real_escape_string($sort)." ASC

This way you only need one query, rather than a query for each case.

Upvotes: 5

Jazz
Jazz

Reputation: 1485

There are a number of ways to do this, but let's stick with your basic approach -- using the ORDER BY clause of your SQL to do the sorting for you. What you need to do is modify your query (or use a different query) depending on which selection the user has made with the dropdown. Here is what I would do:

$query = 'SELECT bookname, bookauthor, bookpub, bookisbn FROM booktable';
switch( $_POST['order'] ){
    case 'title':
        $query .= ' ORDER BY bookname ASC';
        break;
    case 'publisher':
        $query .= ' ORDER BY bookpub ASC';
        break;
    case 'author':
        $query .= ' ORDER BY bookauthor ASC';
        break;
    case 'isbn':
        $query .= ' ORDER BY bookisbn ASC';
        break;
}
$results = mysql_query( $query ); // or however you are processing the query

Now, this is correct, but personally I find switch statements of this type to be overkill. If you're just using a switch block to change one particular value based on another value, using a PHP array to map one value to another can be much simpler to read, so here's another version which I find more streamlined:

$fieldNameMapping = array( 'title'     => 'bookname',
                           'publisher' => 'bookpub',
                           'author'    => 'bookauthor',
                           'isbn'      => 'bookisbn',    );

$query = 'SELECT bookname, bookauthor, bookpub, bookisbn
             FROM booktable ORDER BY ' . $fieldNameMapping[ $_POST['order'] ] . ' ASC';

$results = mysql_query( $query ); // or however you are processing the query

With either of these methods, make sure to do some basic sanity-checking on $_POST['order'] before you use it. I've left any input validation or sanitization out of my examples, but you always want to be extra-careful when you're taking user data and using it to build an SQL query.

Upvotes: 1

Adeel Mughal
Adeel Mughal

Reputation: 736

for that type of sorting you dont need 2 or 3 queries you can just do it with this

<?php
 $sort = @$_POST['order']; 
if (!empty($sort)) { // If you Sort it with value of your  select options
echo $query = "SELECT bookname, bookauthor, bookpub, bookisbn
                FROM booktable
                ORDER BY '".$sort."' ASC";

} else { // else if you do not pass any value from select option will return this
echo    $query = "SELECT bookname, bookauthor, bookpub, bookisbn
                FROM booktable
                ORDER BY bookname ASC";
}
?>


<form name="sort" action="" method="post">
<select name="order">
   <option value="choose">Make A Selection</option>
   <option value="title">Title</option>
   <option value="author">Author</option>
   <option value="publisher">Publisher</option>
   <option value="isbn">Book ISBN-10</option>
</select>
<input type="submit" value=" - Sort - " />
</form>

Upvotes: 2

Related Questions