PHP/MYSQL join fieldname in array

Question

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

Michael · Accepted Answer · 2012-11-06 12:54:24Z

2

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.

answered Nov 6, 2012 at 12:54

Michael

12.9k6 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GarethL · Accepted Answer · 2012-11-06 12:54:27Z

0

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

answered Nov 6, 2012 at 12:54

GarethL

1,4831 gold badge15 silver badges16 bronze badges

Comments

Mohammad Ahmad · Accepted Answer · 2012-11-06 12:54:56Z

0

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

answered Nov 6, 2012 at 12:54

Mohammad Ahmad

7452 gold badges9 silver badges23 bronze badges

Comments

shadyyx · Accepted Answer · 2012-11-06 12:55:36Z

0

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'];

answered Nov 6, 2012 at 12:55

shadyyx

16.1k7 gold badges62 silver badges98 bronze badges

Collectives™ on Stack Overflow

PHP/MYSQL join fieldname in array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related