current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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!

share|improve this question
    
Could you add the query to your question please –  MakuraYami Apr 5 '12 at 15:17
    
You say the table is row_id yet your array references thread_id. Confuse. –  Blake Apr 5 '12 at 15:20
    
ok, will edit the original question and include the query. thanks –  scorched Apr 5 '12 at 15:21
    
@blake: sorry, a typo. i meant row_id. will edit. thanks –  scorched Apr 5 '12 at 15:22
1  
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 Apr 5 '12 at 15:35

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
ah, yes. thank you for pointing that out. cheers! –  scorched Apr 5 '12 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.