CODEWITHSUNDEEP
phpmysqlsqljoin
user1260310
user1260310

Reputation: 2227

PHP/MYSQL join fieldname in array

I have a query that joins two tables that share a field name. I want to grab the field name of one, not the other after converting the results into an array using fetch_array.

I feel like I have accomplished this with a simple alias numerous times but this time it is not working. Can anyone please help me find the error. Many thanks.

Code is:

$sql = "SELECT i.*,ic.* FROM `items` i 
  LEFT JOIN `itemcat` ic ON i.id= ic.itemid 
  WHERE (shortdescript LIKE '%%' OR longdescript LIKE '%%') 
    AND ic.catid='23' limit 0,20 ";

$res = mysql_query($sql) or die(mysql_error());  //query executes no problem
while($row = mysql_fetch_array($res)) {
$id = $row['i.id'];//I do not understand why this is not getting anything.
$id2 = $row['id'];//returns id but from wrong table.

echo $id; //returns blank.  
echo $id2; //returns id of itemcat table when I want id of items table.
}

Upvotes: 0

Views: 876

Answers (4)

shadyyx
shadyyx

Reputation: 16055

You have to specify whhich id column You want to get, that means:

SELECT i.*,ic.*, i.id FROM `items` i 
LEFT JOIN `itemcat` ic ON i.id= ic.itemid 
WHERE (shortdescript LIKE '%%' OR longdescript LIKE '%%') 
AND ic.catid='23' limit 0,20

I do not understand the condition shortdescript LIKE '%%' OR longdescript LIKE '%%' - this is basically the same as if You omit it...

Then in PHP just do:

$id = $row['id'];

Upvotes: 0

Mohammad Ahmad
Mohammad Ahmad

Reputation: 745

Try 

$sql = "SELECT i.*, i.id as firstid, ic.id as secondid, ic.* FROM `items` i LEFT JOIN `itemcat` ic ON i.id= ic.itemid WHERE (shortdescript LIKE '%%' OR longdescript LIKE '%%') AND ic.catid='23' limit 0,20 ";

$res = mysql_query($sql) or die(mysql_error());  //query executes no problem
while($row = mysql_fetch_array($res)) {
$id = $row['firstid']; // id for 'items' table.
$id2 = $row['secondid']; // id for 'itemcat' table.


echo $id; //returns blank.  
echo $id2; //returns id of itemcat table when I want id of items table.
}

Upvotes: 0

GarethL
GarethL

Reputation: 1493

$row['i.id'] will not work. The table name/alias is not included in the output from the query. If you have two column with the same name in different tables, you need to specify the columns individually, and alias them within the query. ie:

$sql = "SELECT i.id as i_id, i.other_field, i.another_field, ic.id as ic_id, ic.my_other_field, ... ";

You can then reference these fields in the $row array by their aliases.

Upvotes: 0

Michael
Michael

Reputation: 12806

The associative array returned by mysql_fetch_array or mysql_fetch_assoc does not include the table name in the key; just the field name. As the columns from table ic are retrieved after the columns from table i they overwrite them. You should set a unique alias on any columns that share names with others.

Upvotes: 2

Related Questions