Same column names in different tables

[HookedOnWinter]

Hello,

I have a MySQL query which returns columns of the same name from two different tables. I'm using mysql_fetch_array, so I can just use numbers to get the data (i.e. $row['0']), but I'd like to use the names. Is there a way to do that? For example, $row['table1.column'] or $row['tables1']['column']. Thanks,

PJ

[NogDog]

Use aliases in your query:

$sql = "SELECT t1.col AS col_1, t2.col AS col_2 FROM table1 t1, table2 t2";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)
{
echo "<tr><td>".$row['col_1']."</td><td>".row['col_2']."</td></td>\n";
}

[yintercept]

You might try giving the columns name an alias in the SQL string. Let's assume the column in question is called mycol, and your tables are called First_Table and Second_Table. You could write the following PHP/SQL:

$sql="SELECT a.mycol AS 'a_mycol', b.mycol AS 'b_mycol'
FROM First_Table a, Second_Table b
WHERE a.pk = b.pk LIMIT 1";

if ($r = mysql_query($sql)) {
$arr = mysql_fetch_assoc($r);
print_r($arr);
}

When you print the array you should see the array keys are:

$arr['a_mycol'] adnd $arr['b_mycol'];

The word "AS" in giving aliases in SQL is optional. The select statements in most SQL language "SELECT column AS 'Name'" is the same as "SELECT column 'Name'". The small quotes are only required when your alias has a space in it.


Using aliases to make the name of the variable clearer is a good thing to do.

[HookedOnWinter]

perfect, thanks!