[thelist] query or php with a join
Mike Migurski
mike at saturn5.com
Fri Feb 28 09:50:18 CST 2003
>After a select I end up with a record = 9 but I want to display the
>record as the name not the value.
<snip>
>The query is:
>
>$result=mysql_query("SELECT items.ItemSKU, items.ItemName,
>items.ItemDescription, items.PostCode, items.Category, items.CityID,
>items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress,
>items.CTown, items.Cwww FROM items WHERE CityID='$CityID' ORDER BY
>CityID");
<snip>
>So I need to create a query that then joing the CityID from items to the
>CityName in table city. Or is it the php that needs to be altered?
Assuming you have a table city, with columns CityID and CityName, the
following query should get you sorted:
SELECT
c.cityName, i.*
FROM
items AS i, city AS c
WHERE
i.cityID='$CityID'
AND i.cityID=c.cityID
ORDER BY
i.cityID
LIMIT
1
$row[0] should then be the city name. The rest is the rest of items, as
you had it though not necessarily in the same order. The limit clause is a
minor sanity check, since you only want the first row. Also, be sure to
escape the $CityID variable prior to placing it in the query.
---------------------------------------------------------------------
michal migurski- contact info and pgp key:
http://www.saturn5.com/mike/contact.html
"Freedom! Horrible, horrible freedom!"
More information about the thelist
mailing list