Ordering Results

princessdeia

I want to give users of my site the option of re-organinng the order the results are returned.

so far I have...

$default_sort = 'featureSite';
$allowed_order = array ('entryDate', 'companyName','featureSite');

if (!isset ($GET['order']) ||
!in_array ($GET['order'], $allowed_order)) {
$order = $default_sort;
}
else {
$order = $_GET['order'];
}

$db = mysql_connect("localhost","xxxx","xxxxxx");
mysql_select_db ("xxxxxxxxx");

$query = "SELECT directory.featureSite, directory.mainBanner, contact.companyName, directory.incAddress, contact.Address, contact.Town, county.countyName, contact.postcode,
directory.incPhone, contact.phone, directory.incEmail, contact.email, contact.website, directory.smallBanner, directory.description, contact.entryDate
FROM categories, contact, county, directory
WHERE categories.categoryID = directory.categoryID
AND directory.companyID = contact.companyID
AND directory.countyID = county.countyID
AND categories.categoryID LIKE '{$POST['category']}'
AND directory.countyID LIKE'{$POST['county']}'
ORDER BY $order";
$result = mysql_query ($query);

$row = mysql_fetch_assoc ($result);
foreach ($row as $heading=>$column) {
echo "<p align='center'>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
}
}
echo "</p>";

Then there's the printing of the results.

However, when I try this clicking the links produces zero results.

Thank you
Dee

liquorvicar

have you tried echo-ing the SQL statement to see what is being generated? have you tried running the query directly against the DB?

princessdeia

Originally posted by liquorvicar
have you tried echo-ing the SQL statement to see what is being generated? have you tried running the query directly against the DB?

I should have mentioned I am a novice at this....
What does this involve doing???

Dee

liquorvicar

try this then:

$query = "SELECT directory.featureSite, directory.mainBanner, contact.companyName, directory.incAddress, contact.Address, contact.Town, county.countyName, contact.postcode,
directory.incPhone, contact.phone, directory.incEmail, contact.email, contact.website, directory.smallBanner, directory.description, contact.entryDate
FROM categories, contact, county, directory
WHERE categories.categoryID = directory.categoryID
AND directory.companyID = contact.companyID
AND directory.countyID = county.countyID
AND categories.categoryID LIKE '{$_POST['category']}'
AND directory.countyID LIKE'{$_POST['county']}'
ORDER BY $order";
die($query);
$result = mysql_query ($query);

this will stop the script at the die() statement and output the SQL statement. i often find it helps to look at what i'm creating.
if that looks OK try copying the SQL command into something like phpMyAdmin (if you have access to it) and seeing what results you get.

princessdeia

I have now got the query working however, when i try and change the order, I get no results - even though they are there in the default.

I think the problem is bacause I am pulling data from four tables.

liquorvicar

are you getting an error?
try replacing

$result = mysql_query ($query);

with

$result = mysql_query ($query) or die(mysql_error());

princessdeia

I am not getting an error - just the text I have set to display when no results are available.

This is a sample query I have set up... http://www.princessdeia.co.uk/weddingdatabase/test.php

The search works fine - but if you try and change the order by clicking the links at the top you get the text for no data to display.

liquorvicar

what happens when you echo the value of $order when $_GET['order'] is not the default?

princessdeia

That works fine - it displays the right Order by result