Pulling data from a referenced table?

[deepfrybonanza]

I've searched the internet for an answer to this question, but I don't think I'm using the proper search terms so I haven't found anything.

I have two tables, country and city. The city table contains a field called "country_id" which associated each city with a country in the "country" table.

Now, my question is, how do I pull the name of the country so that I can sort the cities alphabetically by country and display the country name next to the city? The "country" table has a field called "country_name," but all I have is the id key cross-referenced.

Can anyone point me toward a good tutorial or help me figure out what it's called that I'm searching for?

[astericks]

I didnt really get what you said, but..umm...this might work.

 SELECT tbl_country.*, tbl_city.* WHERE tbl_city.countryID = tbl_country.countryID ORDER by country_name ASC, city_name ASC

Cities with no countryID's wont get displayed. and it's gonna sort by country name and then by city name. I tihnk it might work..

asT.

[deepfrybonanza]

I get a syntax error on that:

Error performing query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tbl_city.country_id = tbl_country.id ORDER by

[astericks]

dont copy it word for word.

replace tbl_city by your tablename that contains the cities, tbl_country by the table containing the countryies and make sure the column names are correct too.

[r937]

and dont forget the FROM clause

[deepfrybonanza]

Okay, I've got it mostly working except for one thing. There is an overlapping field name between the two tables, so how do I call each field in the while loop? THis was my idea, but it's not working:

$region_name = $row["kfth_region.name"]; 

[r937]

give each field a different column alias in your query

select foo.name as fooname
, bar.name as barname
from ...

[deepfrybonanza]

Hmmm... I'm getting all the records but they're not displaying correctly. I currently have four regions and five cities spread across those different regions. With the following code, the page displays each city four times, once for each region:

$result = @mysql_query("SELECT kfth_city.*, kfth_region.name as region_name from kfth_city, kfth_region WHERE $country_id = kfth_city.country_id ORDER by region_name ASC, kfth_city.name ASC");
   if (!$result) {
   echo("<p>Error performing query: ". mysql_error() ."</p>");
   exit ();
   }
   
   
   while ($row = mysql_fetch_array($result)) {
      $region_name = $row["region_name"];
      $id = $row["id"];
      $name = $row["name"];
   
   
   echo "$name";
   
   if ($region_name) {
   
   echo " (Region: $region_name) ";
   
   }
   
 echo "(<a href=\"delete_city.php?id=$id\" onclick=\"return confirm_entry()\">delete</a>) (<a href=\"change_region.php?id=$id&country_id=$country_id\">change region association</a>)<br>";
   
   
   } 

[astericks]

I'm guessing that region = country...

try this:

$sql = "SELECT kfth_city.*, kfth_region.name as region_name from kfth_city, kfth_region WHERE kfth_city.country_id = '" . $country_id . "' ORDER by region_name ASC, kfth_city.name ASC";

$result = @mysql_query($sql) or die(<p>Error performing query: ". mysql_error() ."</p>");
   
   
   while ($row = mysql_fetch_array($result)) {
      $region_name = $row["region_name"];
      $id = $row["id"];
      $name = $row["name"];
   
   
   echo $name;
   
   if (trim($region_name) != '') { // $region_name is not empty
   
        echo 'Region: ' . $region_name;
   
   }
   
 echo '<a href="delete_city.php?id=' . $id . '" onclick="return confirm_entry()">delete</a>
<a href="change_region.php?id=' . $id . '&country_id=' . $country_id . '">change region association</a><br>';
      
  } 

Just changed a few things around to mkae the codes more readable...it should not have significant change on the results. However, what do you mean by "not being displayed properly" ??? the order of the results or the layout of the page?

[deepfrybonanza]

Unfortunately that code generates the same results.

[r937]

when you say "the same results" may we see these too?