list categories

[jim39]

Hi

 

If anyone could help with below code as I am new to php

 

<?php
$result = mysql_query("SELECT * FROM hotels ORDER BY city");

while($row = mysql_fetch_array($result))
  {
  echo $row['city'] . " " ;
  echo "<br />";
  }
?>

 

above code list all cities as below

newyork

newyork

newyork

newyork

newyork

newyork

Washington

Washington

Washington

Washington

Washington

Washington

Seattle

Seattle

Seattle

Seattle

Seattle

Kansas

Kansas

Kansas

Kansas

Kansas

Kansas

Kansas

.... continue

 

but i want to list like below and when a city link is clicked, all the hotels in that city will display on a city Page using the appropriate template

 

Newyork

washington

seattle

kansas ....

 

any help much appreciate

 

Thanks

 

 

[JAY6390]

You would need to create a new page, for example hotels.php and then use $_GET to find the url. Also, you should only have one city for each of the city names in the above so change your code to

SELECT * FROM hotels ORDER BY city GROUP BY city

If you want more help, it would be better if you gave some idea as to the database table schema so we know what columns you have

[dgoosens]

as a start...

if these cities appear so often in your table, you might want to create a new table with them and link them to the hotels with an ID.

 

otherwise, just change your query from

$result = mysql_query("SELECT * FROM hotels ORDER BY city");

to

$result = mysql_query("SELECT DISTINCT city FROM hotels ORDER BY city");

[jim39]

Thanks guys,

 

below is worked fine...

 

$result = mysql_query("SELECT DISTINCT city FROM hotels ORDER BY city");

 

one more question

 

when a city link is clicked as listed below ( say city.php?id=newyork) , I want to list all the hotels in that city will display on a city Page using the appropriate template (city.php) so how can i give a link here

 

<?php
$result = mysql_query("SELECT DISTINCT city FROM hotels ORDER BY city");

while($row = mysql_fetch_array($result))
  {
[b]  echo $row['city'] . " " ;[/b]
  echo "<br />";
  }
?>

 

Newyork

washington

seattle

kansas

 

Thanks again

 

 

[dgoosens]

something like this...

<?php
$result = mysql_query("SELECT DISTINCT city FROM hotels ORDER BY city");

while($row = mysql_fetch_array($result))
  {
      echo '<a href="city.php?id=' . $row['city'] . ' ">' . $row['city'] . '</a><br>';
  }
?>

 

but it would be much better to pass an ID than the city name

you might run into trouble when there are spaces (New York) or accentuated chars (Málaga)

 

to do so you would create a table in your DB

CITIES

  - id

  - city

 

HOTELS

  - id

  - hotel

  - city_id

 

This would greatly improve your app.