[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