MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:March 19 2003 5:58pm
Subject:Re: another table to query!
View as plain text  
Andrew wrote:

>I have done this and have now started to populate the items_city table with the
>appropriate records what I now need to do is show the records using the city
>items_table.  At the moment the records are displayed when a city is selected bu
>the cityID is from the items table. Like this:
>
>$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 Category='$Category' and
>CityID='$CityID' ORDER BY CityID");
>
>Presumably I need to do a JOIN ?
>  
>
Yes.  It should look something like:

SELECT i.ItemSKU, i.ItemName, i.ItemDescription, i.PostCode, i.Category,   ic.CityID,
i.CTelephone, i.ItemID, i.Cfax, i.Cemail, i.Caddress, i.CTown, i.Cwww 
FROM items i Inner Join item_city ic ON ic.ItemID = i.ItemID
WHERE i.Category='$Category' and ic.CityID='$CityID' 
ORDER BY ic.CityID

Note that if you're just looking for a single CityID, there's no reason 
to ORDER BY it, and little reason to SELECT it.

Also, now that you have the item_city table, you can and should remove 
city_id from items.  If you have a significant amount of data already 
entered, you might want to copy the cityid/itemid information from items 
into item_city first.

Bruce Feist


Thread
another table to query!Andrew19 Mar
RE: another table to query!Andrew19 Mar
  • Re: another table to query!Bruce Feist19 Mar
    • RE: another table to query!Andrew19 Mar
      • Re: another table to query!Bruce Feist19 Mar