Query to sort by field variable in URL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Query to sort by field variable in URL

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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>';
}
?>
Post Reply