SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pulling data from a referenced table?

    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?
    Daniel

    http://www.dfbpunk.com

  2. #2
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didnt really get what you said, but..umm...this might work.

    Code:
     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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Daniel

    http://www.dfbpunk.com

  4. #4
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    and dont forget the FROM clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $region_name $row["kfth_region.name"]; 
    Daniel

    http://www.dfbpunk.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    give each field a different column alias in your query

    select foo.name as fooname
    , bar.name as barname
    from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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>";
       
       
       } 
    Daniel

    http://www.dfbpunk.com

  9. #9
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing that region = country...

    try this:

    PHP Code:
    $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?

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately that code generates the same results.
    Daniel

    http://www.dfbpunk.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you say "the same results" may we see these too?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •