Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.
}
share|improve this question

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
up vote 0 down vote

$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.

share|improve this answer
up vote 0 down vote

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.
}
share|improve this answer
up vote 0 down vote

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'];
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.