Mysql JOIN - Unexpected result in mysql_fetch_array

Question

I am using a complex join statement to get data from my 'items' table where 'column1' is equal to the value of table2.ID

This is in a mysql_query() statement, and it should return 3 rows.

Now my understanding is that by using $array=mysql_fetch_array($queryresult);, I can then loop through each 'row' using a foreach($array as $output){echo $output['ID'];}

This is however not returning what i want. Using print_r on $output is outputting non-sensical information.

So, yes it is ver much back to basics, but obviously i have missed the point.

Answer 1

You need to use while loop:

while($row = mysql_fetch_array($queryresult)){
  // handle each row
}

Answer 2

This is how I do it. This is by far not the end all solution... Just an example of how I do it.

$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
     $i = 0;
     while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo $row["questionId"];
        echo $row["questionText"];
        echo $row["questionReview"];
     $i++;
     }  

http://php.net/manual/en/function.mysql-fetch-array.php

Answer 3

$array has a single row in it when you get to the loop, so when you say $output['ID'] you are one level deeper than you are expecting, walking through the columns instead of each row. When the ids don't exist or are translating to integers, thats where the nonsense comes in.

Use while($row = mysql_fetch_array($queryresult)) to walk through each row in the result set, then access the column values from $row['id'], $row['name'], etc. It will return false when there are no more rows.

The result will always be a single flat array with a single row per index id, regardless of the join dimensions.