query or php with a join

[Andrew]

PHP Guys  Dolls I have a sight display issue that I just need to resolve :)

After a select I end up with a record = 9 but I want to display the record as
the name not the value.

The value was inserted as a value so I need to make a join to the original table
in the query, but alas I have tried a few things without any luck.  So it's the
experts whom I need to help out:)

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);

while ($row  =  mysql_fetch_row($result)) {

$City=$row['5'];
}

the display is:

? echo $City;

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?

Andrew


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query or php with a join

[Tore Bostrup]

Since you haven't told us your table designs, I have to guess, but something
like:

SELECT I.ItemSKU,
I.ItemName,
I.ItemDescription,
I.PostCode,
I.Category,
I.CityID,
I.CTelephone,
I.ItemID,
I.Cfax,
I.Cemail,
I.Caddress,
I.CTown,
I.Cwww,
C.CityName
FROM items as I
INNER JOIN city as C
ON C.CityID = I.CityID
WHERE C.CityID='$CityID'

Your Order By CityID is not required, since your query only selects a
single city anyway.

I prefer formatting the query as above to make it easire to read.  For the
same reason, I also prefer to use table aliases.  With short table names
like items and city, this is not a big deal, but when the table names get
longer, the query can get obscured by the prefixes.

HTH,
Tore


- Original Message -
From: Andrew [EMAIL PROTECTED]
To: MySQL-Lista [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 10:41 AM
Subject: query or php with a join


 PHP Guys  Dolls I have a sight display issue that I just need to resolve
:)

 After a select I end up with a record = 9 but I want to display the record
as
 the name not the value.

 The value was inserted as a value so I need to make a join to the original
table
 in the query, but alas I have tried a few things without any luck.  So
it's the
 experts whom I need to help out:)

 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);

 while ($row  =  mysql_fetch_row($result)) {

 $City=$row['5'];
 }

 the display is:

 ? echo $City;

 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?

 Andrew


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php