[RESOLVED] Display data from Linked Tables

adwfun

I have 2 tables named City and District in MySQL.
Table City fields : CityID, CityName
Table District fields : DistrictID, DistrictName, CityID

I linked District to City. When retrieving data from District Table, how could I display CityName instead of cityID ?

Thanks in advance.

Drakla

If you wanted all districts then:

SELECT DistrictName, CityName FROM City, District WHERE City.CityID = District.CityID

If you wanted just a particular one add

WHERE City.CityID = 5

adwfun

My codes as follow:
What's problem ?

<?php
include("connectdb.php");
$district=mysql_query("select * from District");
while ($result=mysql_fetch_array($district))
{
echo $result["DistrictID"]." ";
echo $result["Districtname"]." ";
echo $result["select Districtname, Cityname from District, City where City.CityID=District.CityID"]." ";
}
?>

mjax

<?php
include("connectdb.php");
$district=mysql_query("select * from District, City where City.CityID=District.CityID");
while ($result=mysql_fetch_array($district))
{
echo $result["DistrictID"]."<br>";
echo $result["Districtname"]."<br>";
echo $result["Cityname"]."<br>";
}
?>

adwfun

Thanks a bunch...mjax

one more question :
When I add a table "shop" into your code and it returns a error message
"mysql_fetch_array(): supplied argument is not a valid MySQL result resource". Why?

Please see the codes below:

<?php
include("connectdb.php");
$shop=mysql_query("select * from Shop, District, City where Shop.ShopDistrict=District.DistrictID, ShopCity=CityID");
while ($result=mysql_fetch_array($shop))
{
echo $result["ShopName"]." ";
echo $result["Cityname"].: ";
echo $result["Districtname"]." ";
echo $result["ShopAddress"]." ";
echo $result["ShopTel"]." ";
}
?>

mjax

<?php
include("connectdb.php");
$shop=mysql_query("select * from Shop, District, City where Shop.ShopDistrict=District.DistrictID AND ShopCity=CityID");
if(!$shop) die('An SQL error occurred: '.mysql_error());
while ($result=mysql_fetch_array($shop))
{
echo $result["ShopName"]."<br>";
echo $result["Cityname"].:<br>";
echo $result["Districtname"]."<br>";
echo $result["ShopAddress"]."<br>";
echo $result["ShopTel"]."<br>";
}
?>