mysql_fetch_array returns an array with a value for the numeric index, but blank for the corresponding associative index

Go To StackoverFlow.com

2

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!

2012-04-05 15:14
by scorched
Could you add the query to your question pleas - MakuraYami 2012-04-05 15:17
You say the table is row_id yet your array references thread_id. Confuse - Blake 2012-04-05 15:20
ok, will edit the original question and include the query. thank - scorched 2012-04-05 15:21
@blake: sorry, a typo. i meant row_id. will edit. thank - scorched 2012-04-05 15:22
Do you get the same problem with mysql_fetch_assoc() - DaveRandom 2012-04-05 15:22
@DaveRandom: yes, it's the same with mysqlfetchassoc() - scorched 2012-04-05 15:32
Be explicit about cols you're selecting instead of a.*, b.*. Since both tables have row_id, the fetch call is overwriting array keys - Michael Berkowski 2012-04-05 15:35


1

This is related to the fact that you have LEFT JOINed 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 JOINed 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.

2012-04-05 15:38
by DaveRandom
ah, yes. thank you for pointing that out. cheers - scorched 2012-04-05 15:42
Ads