Page 1 of 1

Query to sort by field variable in URL

Posted: Fri Jul 14, 2006 10:31 am
by cdickson
I'm trying to write a query that will pull all data from a table and allow me to sort the data by one of the fields, but do it within a URL. The field I want to sort by is "city" and I want the results to show two links:

1) To results.php that will display listings in only one city similar to this:

Code: Select all

<a href='accommodations.php?city=Town1'>Lodging In Town1</a>
2) To results.php that will display listings in all cities except the one above.

I'm still working on different variations of this code:

Code: Select all

$query = "SELECT * FROM lodging";
$result = mysql_query($query) or die("Error: " . mysql_error());

while($row = mysql_fetch_row($result)){		  
  echo("<ul><li><a href='accommodations.php?city=Town1'>Lodging In Town1</a></li>
  <li><a href='accommodations.php?city!=Town1'>Lodging In other Towns</a></li>
  </ul>");
}
Any help will be appreciated.

Posted: Fri Jul 14, 2006 5:54 pm
by RobertGonzalez
Simple logic (in my opinion anyway)...

List all cities with a link to each. If you click on a city, repost the page with that cities information on it, then show the city list below the city with all the cities in it again EXCEPT the one that was chosen.

Code: Select all

<?php
$city = '';
$city_validates = false;
$sql_append = '';

if (isset($_GET['city']))
{
    $city = $_GET['city'];
    //Validate this however you see fit
    $city_validates = true;
}

if ($city_validates)
{
    // Keep this city out of the city list
    $sql_append = ' WHERE `db_field_field_for_city` <> ' . $city;

    //Manage display of selected city information
    // This could be running a query, or echoing stored data
}

// Now make us a list
$sql = 'SELECT * FROM `lodging`' . $sql_append . ' ORDER BY `id_field_or_some_other_sort_field` ASC';
if (!$result = mysql_query($sql))
{
    die('There was a problem pulling the city list: ' . mysql_query());
}

while ($row = mysql_fetch_array($result))
{
    // Echo out your city list using $row['field_names'] with a link to this page
    echo '<a href="?city=' . $row['city_field'] . '" title="See information on ' .$row['city_field'] . '">' . $row['city_field'] . '</a>';
}
?>