Sorting MySQL with several fields to choose from

Question

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>

Always check the result of `mysql_query` and never assume that your queries will always succeed. — Salman A, Dec 12 '12 at 11:23

score 3 · Answer 1 · answered Dec 12 '12 at 11:15

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.

score 2 · Answer 2 · edited May 23 '17 at 12:29

2

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

edited May 23 '17 at 12:29

Community

1
1

answered Dec 12 '12 at 11:16

NullPoiиteя

56,591
22
125
143

Think your missing a ' after $order. – Mark Davidson Dec 12 '12 at 11:21

Ranty · Answer 3 · 2012-12-12T17:27:21.267

1

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]);

edited Dec 12 '12 at 17:27

answered Dec 12 '12 at 11:17

Ranty

3,333
3
22
24

Fantastic ! these both are working fine: $data = mysql_query("SELECT * FROM db_row ORDER BY " . $order); and this one: $data = mysql_query('SELECT * FROM db_row ORDER BY ' . $order . ' ASC'); Thank you so MUCH ! Now, the ideal solution was, if I could click on any of the field on top to sort ASC and click again to sort DESC. Would it be too much to ask for ? ANYWAY - THANKS TO ALL OF YOU for a professional advise ! – Phuket Beachvillas Thailand Dec 12 '12 at 17:07
Updated answer with sort handling. Glad to help! – Ranty Dec 12 '12 at 17:27
@*Ranty** Thank you for all your fine work done, but I have to give up :-( It works fine so far and sorts each fields ASC, if clicked, but I simply don't know where to put your code, to make it sort ASD / DESC. PHP knows me, but I don't know PHP enough. Wish I would...Never mind and again: THANKS A LOT. – Phuket Beachvillas Thailand Dec 13 '12 at 06:48

Sorting MySQL with several fields to choose from

3 Answers3