Problem when use "LEFT JOIN"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
detrox
Forum Newbie
Posts: 21
Joined: Wed Jun 04, 2003 1:27 am
Location: P.R.China

Problem when use "LEFT JOIN"

Post by detrox »

In my project, I have to run

Code: Select all

$res = mysql_query("SELECT * FROM poll_opt LEFT JOIN poll_sub ON poll_opt.subid = poll_sub.id");

while ($data = mysql_fetch_array($res)) {
...
   poll_id = $dataї"id"];
...
}
but I have a field named "id" in both poll_opt and poll_sub, how can I distinguish them when i use $data["id"].
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

specify your fields in the SELECT and use an alias for the id
'table1.id as first_id, table2.id as second_id'

retrieve with poll_id = $data["first_id"];
User avatar
detrox
Forum Newbie
Posts: 21
Joined: Wed Jun 04, 2003 1:27 am
Location: P.R.China

Post by detrox »

Thanks very much. It makes great help.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Just adding;
As you use mysql_fetch_array($res) you also get the numerical instance of the keys in the array fetched...

Code: Select all

$dataї'id'] = 'foo'
$dataї0] = 'foo'
$dataї'id'] = 'bar' // heres your problem, what id to use...
$dataї1] = 'bar' // workaround usage
so, even if both are 'named' id, you can distinguish them using;

Code: Select all

poll_id = $dataї0]
other_poll = $dataї9] // or whatever...
Post Reply