PHP MySQL - Multiple Select Queries returned in while loop

Question

I have the following code which works fine however historically and again now I want to (if possible) add a third select statement to collect further information and return it in my while loop i.e. as $row2. Is there any way I can do this or will I have to rehash my code completely to make it work?

Any help or guidance will be gratefully received. Current code is as below:

mysql_select_db("jbsrint", $con);
$result = mysql_query("SELECT *, DATE_FORMAT(Date_Registered, '%d-%m-%Y') AS Date_Registered, DATE_FORMAT(Date_Last_MOT, '%d-%m-%Y') AS Date_Last_MOT,      DATE_FORMAT(Date_Last_Tax, '%d-%m-%Y') AS Date_Last_Tax FROM Veh_List ORDER BY ID DESC");
$DUE_DATES = mysql_query("SELECT *, DATE_FORMAT(MOT_DUE_DATE, '%d-%m-%Y') AS MOT_DUE_DATE, DATE_FORMAT(Date_Tax_Due, '%d-%m-%Y') AS Date_Tax_Due FROM due_dates ORDER BY ID DESC");
echo "<table border='1'>
<tr>
<th>Vehicle Reg</th>
<th>Vehicle Make</th>
<th>Vehicle Model</th>
<th>Vehicle Colour</th>
<th>Date Registered</th>
<th>Date Of Last MOT</th>
<th>MOT Due</th>
<th>Date Of Last Tax</th>
<th>Tax Due</th>
<th>Vehicle Driver</th>
<th>Vehicle Driver Tel</th>
</tr>";
while($row = mysql_fetch_array($result) and $row1 = mysql_fetch_array($DUE_DATES))
{
  echo "<tr>";
  echo "<td class=\"td1\">" . $row['Vehicle_Reg'] . "</td>";
  echo "<td >" . $row['Vehicle_Make'] . "</td>";
  echo "<td class=\"td1\">" . $row['Vehicle_Model'] . "</td>";
  echo "<td>" . $row['Vehicle_Colour'] . "</td>";
  echo "<td class=\"td1\">" . $row['Date_Registered'] . "</td>";
  echo "<td>" . $row['Date_Last_MOT'] . "</td>";
  echo "<td class=\"td1\">" . $row1['MOT_DUE_DATE'] . "</td>"; 
  echo "<td>" . $row['Date_Last_Tax'] . "</td>";
  echo "<td class=\"td1\">" . $row1['Date_Tax_Due'] . "</td>";
  echo "<td>" . $row['Vehicle_Driver'] . "</td>";
  echo "<td class=\"td1\">" . $row['Vehicle_Driver_Tel'] . "</td>";
}

echo "</tr>";
echo "</table>";
mysql_close($con);
?>

score 1 · Accepted Answer · edited May 23 '17 at 12:20

You should not use a separate query for each table. Instead you should use JOIN to retrieve related data from multiple tables in a single query:

SELECT
    veh_List.col1,
    veh_List.col2,
    due_dates.col3,
    ...etc...
FROM veh_List 
JOIN due_dates ON veh_List.id = due_dates.id
-- add more joins here if you need data from more tables
ORDER BY veh_List.id DESC

You should learn about the JOIN keyword and the various possibilities this provides, including understanding the difference between INNER JOIN and OUTER JOIN.

What is the difference between "INNER JOIN" and "OUTER JOIN"?

score 0 · Answer 2 · answered Feb 06 '12 at 10:55

I believe you can simply add another statement and combine it in the same fashion as you had now but my suggestion is to use the opportunity that you are modifying the code and merge the SQL queries into one.

Also, looking at your database design - is there one-to-one relation between the due dates and registered cars? If so, why not merge the two tables together? (Of course, I can only see the columns you are using in your select queries).

Furthermore, it seems a bit dangerous to rely on the ordering to match up the records - what if one row from due dates is removed?

PHP MySQL - Multiple Select Queries returned in while loop

2 Answers2