echo a variable after a JOIN

[jeff5656]

if I join two tables and one of the fieldnames is the same in both tables, how do I echo out the correct one?

as an example,

$query = "SELECT * FROM food, more_food ";
$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {

 

Now the following "made sense", but it doesn't work.  How do i speciy the variable from the correct table if the ieldnames are the same in both tables?

echo "the type of apple is $row['food.apple'];

[MatthewJ]

Use aliases on the table columns...

 

SELECT table1.field as field1, table2.field as field2 FROM ........

 

then you can use $row['field1']

[smarble53]

Shouldn't the array have key values? i.e. $row[5]

 

i dont know off of the top of my head if it does that or not from the mysql statement.

[jeff5656]

"SELECT table1.field as field1, table2.field as field2 FROM ........"

 

Is there any way to do that and still use the wildcard *?  I have many fieldnames  to selct from both tables and don't want to have to write them all out...

[MatthewJ]

No, unless it is more than 100 fields, writing out the query once shouldn't be that big of a deal

 

Also, have you examined your results of that query? I believe that will create a result row for every row in the right table appended to every row of the left table...

 

so, if table 1 has 3 rows, and table 2 has 5 rows, your result set is going to be 15 records. That does not sound like a desirable result, but I could be wrong