Sorting MySQL datasets by a specific field

cannboys

Hi,

Am currently having some difficulty in sorting some information retrieved from a MySQL query.

Basically it's for a page (http://www.teatonic.com.au/stockists/search_all.php) which is supposed to retrieve a list of stockists/retailers and then sort them alphabetically by the field containing their Town/Suburb/City (see below).

However, the code below is returning an error that mysql_num_rows is not a valid result - I'm totally lost here, so if anyone can help me it would be appreciated.

<?
$DBName = "stockists";
$table = "stockists";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName"); $sqlquery = "SELECT * FROM ${table} WHERE State = 'VIC' SORT BY City_town, Bus_name ASC";

$result = mysql_query($sqlquery);

if (mysql_num_rows($result))
{
foreach ($result as $row)
{
?>

<dd>Email: <a href="mailto:<?= $row['email'] ?>" title="email
<?= $row['Bus_name'] ?>"><?= $row['email'] ?></a></dd>

<dd>Website: [url]http://[/url]<a href="http://<?= $row['website'] ?>"
target="<?= $row['Bus_name'] ?>"
title="<?= $row['Bus_name'] ?>"><?= $row['website'] ?></a></dd>

<?php
} // end foreach loop
}
else
{
?>

<p>There Were No Results for Your Search</p>

<?php
}
@mysql_free_result($result);
?>

tsinka

Hi,

replace SORT BY with ORDER BY

Thomas

cannboys

Thanks. It seemed to fix the problem with the MySQL query, but now the PHP is broke 😐

tsinka

Ok,

you need to use mysql_fetch_array or mysql_fetch_assoc in this case to get the row data itself. $result is only a result set handle.

Something like

 while ($row = mysql_fetch_assoc($result))
{

instead of the foreach line.

Thomas

cannboys

Top Shelf! Thanks Thomas!