CODEWITHSUNDEEP
phpmysqlsorting
Phuket Beachvillas Thailand
Phuket Beachvillas Thailand

Reputation: 11

Sorting MySQL with several fields to choose from

I have a portfolio which I would like to have listed in ASC sort order, according to several fields to choose from on top of the list. Without sorting the printout is perfect, but when activating the sort statement (line 35) I get the following error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/domain/public_html/db_name/portfolio.php on line 47

Can somebody please help and tell me what to do with this code? Many thanks for any advice:

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Portfolio</title>
<link rel="stylesheet" type="text/css" href="../scripts/css/formate.css" />
</head>
<body>

<?php 
 // Connects to Database 
 mysql_connect("localhost","user","passw") or die(mysql_error()); 
 mysql_select_db("db_name") or die(mysql_error()); 
 ?>

<table>
    <tr>
        <th><a href="?orderBy=ref_id">Order by Villa name   |</a></th>
        <th><a href="?orderBy=bedrooms">Order by no. of Beds   |</a></th>
        <th><a href="?orderBy=max_occupants">Order by Sleeps   </a></th>
    </tr>
</table>

<?php
// sort table as selected
$orderBy = array('ref_id', 'bedrooms', 'max_occupants');
$order = 'ref_id';
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
    $order = $_GET['orderBy'];
}

// Read table ---> and sort it:

// $data = mysql_query("SELECT * FROM res_properties"); 

 $data = mysql_query("SELECT * FROM db_row ORDER BY '.$order"); 

 Print "<table border cellpadding=3>"; 
 Print "<br><h3>Portfolio</h3 ><p> <br>";
 echo 'Listings as per: -       ';
 print date('r');
 print "\\n";
 Print "<br><p> <br>";

 Print "<table border cellpadding=3 >"; 
 Print "<tr align='center'><th width=130>Villa Name</th><th width=40>Beds</th><th width=40>Baths</th><th width=60>Sleeps</th><th width=200 >Property Website </th><th width=50 >Prop.ID</th></tr>"; 

   while($info = mysql_fetch_array( $data )) 

{ 

 Print "<tr align='center'><font face='arial' size='2' color='000000'>"; 
 Print "<td>".$info['ref_id'] . "</td> "; 
 Print "<td>".$info['bedrooms'] . " </td>"; 
 Print "<td>".$info['bathrooms'] . " </td>"; 
 Print "<td>".$info['max_occupants'] . " </td>"; 
 Print "<td><a href=\"http://www.domain.com/properties/index.php/property/" . $info['slug'] . ".html\">Open website here</a></td>";
 Print "<td>".$info['id'] . "</td></tr> "; 

 } 
 Print "</table>"; 
 ?> 

</body>
</html>

Upvotes: 0

Views: 406

Answers (3)

NullPoiиteя
NullPoiиteя

Reputation: 57312

try

$data = mysql_query("SELECT * FROM db_row ORDER BY '$order'");

or

 $data = mysql_query("SELECT * FROM db_row ORDER BY " . $order."'");

NOTE 2 : Use of mysql_* function are deprecated even it will generate E_DEPRECATED warning in php5.5 so use PDO or MySQLi instead if you want to learn pdo here is good tutorial

Upvotes: 2

Ranty
Ranty

Reputation: 3352

Maybe you are looking for something like this?:

$data = mysql_query('SELECT * FROM db_row ORDER BY ' . $order . ' ASC');

In order to do ascending and descending sorting, you need to add another parameter to your link. Should it be asc or desc by default is your call, whatever makes more sense. In this case I made 0 for ascending and 1 for descending.

<a href="?orderBy=ref_id&sort=0">

The next thing you want to do is to add logic for sort handling:

$sortBy = array('asc', 'desc');
$sort = 0;
if (isset($_GET['sort']) && in_array($_GET['sort'], array_keys($sortBy))) {
    $sort = $_GET['sort'];
}

And the last thing to do is the change of asc to desc and vice versa when you click the link second time. If you put the code I provided (logic for sort handling) and the code for the $order handling above your navigation table (to make the variables accessible within the table), you can add a condition into the link:

<a href="?orderBy=ref_id&sort=<?php echo ($order == 'ref_id' ? !$sort : 0); ?>">

And you MySQL query will look something like this:

$data = mysql_query('SELECT * FROM db_row ORDER BY ' . $order . ' ' . $sortBy[$sort]);

Upvotes: 1

Mark Davidson
Mark Davidson

Reputation: 5513

I think its simply how your building your query.

 $data = mysql_query("SELECT * FROM db_row ORDER BY '.$order");

to

 $data = mysql_query("SELECT * FROM db_row ORDER BY " . $order);

If that doesn’t work add mysql_error after execution of that statement and see what the error is.

Upvotes: 3

Related Questions