i have a table with the following fields:
row_id
first_field
second_field
row_id is of type integer, is set to auto increment and is the primary key. the other two fields are of type text.
the table is populated up to five rows. as such, the values for row_id are 1, 2, 3, 4 and 5.
i also have another table of a similar structure that has a one-to-many correspondence with my first table.
something weird happens though when i do a select query and feed the result to mysql_fetch_array.
when i run this:
$query = "select a.*, b.* from table1 as a
left join table2 as b on a.row_id = b.row_id";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo '<pre>'; print_r($row); echo '</pre>';
}
i get this:
Array
(
[0] => 1
[row_id] => 1
[1] => some text
[first_field] => some text
[2] => some text
[second_field] => some text
}
Array
(
[0] => 2
[row_id] => 2
[1] => some text
[first_field] => some text
[2] => some text
[second_field] => some text
}
Array
(
[0] => 3
[row_id] =>
[1] => some text
[first_field] => some text
[2] => some text
[second_field] => some text
}
Array
(
[0] => 4
[row_id] =>
[1] => some text
[first_field] => some text
[2] => some text
[second_field] => some text
}
Array
(
[0] => 5
[row_id] =>
[1] => some text
[first_field] => some text
[2] => some text
[second_field] => some text
}
on each array result, i'd like to direct your attention to the first field, row_id. in the first two arrays, index 0 and row_id have the same values, whereas in the subsequent three arrays, only index 0 has a value. row_id appears to be null.
this is the first time i've ever encountered something like this. what is causing this? how can this be fixed?
thanks!
a.*, b.*
. Since both tables have row_id
, the fetch call is overwriting array keys - Michael Berkowski 2012-04-05 15:35
This is related to the fact that you have LEFT JOIN
ed the other table in the query.
With a LEFT JOIN
, the rows in the first table that do not have matching row in the second table will have the JOIN
ed fields populated with NULL
values.
Because you have a column called row_id
in both tables and you have selected *
from both tables, the later value for row_id
is overwriting the earlier in the results. It would seem that there are no matching rows in table2
for rows 3, 4 and 5 in table1
. This is resulting in a NULL row_id
.
The solution is to choose which row_id
you want, and there are a number of ways to do this. But with a relatively simple set of results like this, I suggest you explicitly state all the columns you want:
SELECT a.*, b.col_1, b.col_2
FROM table1 a
LEFT JOIN table2 b ON a.row_id = b.row_id
Alternatively, you could drop the LEFT
from the join, and the rows 3, 4 and 5 will be omitted from the results.