MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andrew Date:March 19 2003 8:25pm
Subject:RE: another table to query!
View as plain text  
ok this is making sense and I should have thought about this before* I created
my city table  as now I have a city table that in effect is useless and
comlicated the existing queries :(

A lesson to be learnt indeed.

>-----Original Message-----
>From: Bruce Feist [mailto:bfeist@stripped]
>Sent: 19 March 2003 17:59
>Cc: MySQL-Lista
>Subject: Re: another table to query!
>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'
>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
>Before posting, please check:
>   (the manual)
>           (the list archive)
>To request this thread, e-mail <mysql-thread135061@stripped>
>To unsubscribe, e-mail
>Trouble unsubscribing? Try:
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (
>Version: 6.0.463 / Virus Database: 262 - Release Date: 17/03/2003

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