将两个查询中的值匹配到同一个表中

Question

I'm trying to add a description to a coded value on PHP. The code was extracted from a table, and the description from another. The idea is that they are presented on a table with the proper description for each code, but when I tried to add the raw ordered query, the descriptions where misplaced, so I tried this:

 <table>
 for($i=0; $i < $items; $i++)
 {
     $row = $results -> fetch_array();
     echo '<tr>';
     echo '<td>'.$row['code'].'</td>';
     for($j=0; $j<$items2; $j++) 
     {
         $row2 = $result2 -> fetch_array();
         if ($row['code'] == $row2['code']) 
         {
             echo '<td>'.$row2['description'].'</td>';
         }
     }
     echo '</tr>';
 }
 echo '</table>';

It's working for the first value, but only that one, the rest of the column is empty.

Answer 1

I think your problem is, that you have to reset the row pointer for $result2.

 <table>
 for($i=0; $i < $items; $i++)
 {
     $row = $results -> fetch_array();
     echo '<tr>';
     echo '<td>'.$row['code'].'</td>';
     for($j=0; $j<items2; $j++) 
     {
         $result2 -> data_seek( 0 );
         $row2 = $result2 -> fetch_array();
         if ($row['code'] == $row2['code']) 
         {
             echo '<td>'.$row2['code'].'</td>';
         }
     }
     echo '</tr>';
 }
 echo '</table>';

Every time you call fetch_array, an internal row pointer is incremented by one. So in the outer for loop´s second cycle, $result2 -> fetch_array() will retun NULL since there is no more result left.

Alternatively, you can iterate through $result2 before the first loop und store the results in a two dimensional array. However: I would prefer to use a JOIN to put together the two statements, so that you only will get one resultset. But there´s too few information provided, to see if that´s possible in this case.

MySQL JOIN Syntax