Join return result

[atg2d]

Hi i am not entirely sure if this question is for here or for the php forum but i'll try here first. Here is my problem:
select table1.*, table2.* from table1 left join table2 on table1.field1=table2.field1
In php i receve a result:
Array ( [field1] => 1 [field2] => something else )
I want to receive result:
Array ( [table1.field1] => 1 [table1.field2] => something [table2.field1] => 1 [table2.field2] => something else )
What i need to do to get the result set i want? Are there any settings that switch the behaviour to what i want
Thank you

[r937]

definitely a php question

[atg2d]

The reason i think it might be for here is that when i run that sql query in the mysql command line (or any other utility i've tried) it doesn't display "tableX." it displays:

+--------+-------------+--------+-------------------+
| field1 |    field2   | field1 |        field2     |
+--------+-------------+--------+-------------------+
| 1      |  something  |   1    |   something else  |
+--------+-------------+--------+-------------------+

while it shoud display:

+-------------+-------------+-------------+-----------------+
|table1.field1|table1.field2|table2.field1|  table2.field2  |
+-------------+-------------+-------------+-----------------+
|       1     | something   |      1      |  something else |
+-------------+-------------+-------------+-----------------+

or am i wrong? Is this normal?

[r937]

no, this is normal

the result set of any query has column names which are derived from the tables, or else are column aliases assigned in the query, but there's no way they carry the name of the table they came from

what you can do is assign column aliases --

select table1.field1 as t1f1
     , table1.field2 as t1f2
     , table2.field1 as t2f1
     , table2.field2 as t2f2
  from table1
left outer
  join table2         
    on table1.field1
     = table2.field1

then use t1f1, t1f2, t2f1, t2f2 in your php script

[atg2d]

Hi again i tried to post in the PHP part of the forum but they pointed me back here so i'll continue here.
I knew even before you told me that I can use "table1.field1 AS t1f1" the problem is that i have more than 100 scripts that i have to fix if i go that way...
These scripts were written for sqlite and they are working but now i want to migrate them to MySQL so i am looking for a way not to fix all my sql queries (and the scripts with them) so i need some way to enable? php or mysql to return me result as "table1.field1" and not just as field1...
I would appreciate any help at least if you can point me where to look for info.
Thanks

[r937]

tell whoever it is in the php forum that it's a php issue, not mysql

if you don't want to use aliases, then the column names will come back as field1, field2, field1, field2, ...

there is no way the result set will have a tablename appended to the front of the column names

if there is some way in php to access your columns positionally rather than by name, you could do that

of course, that would be a change to your script as disruptive as using aliases