html - Using Select Query inside a while loop in php

Question

I am trying to Print some data from 2 tables when i try to execute 2nd select query inside while loop its not showing any result
My query is

$query=mysql_query("SELECT * FROM buses");
echo "<table border='1'>";
echo "<tr>";
echo "<td>Sno</td>";
echo "<td>Route #</td>";
echo "<td>Bus _Type</td>";
echo "<td>Timing</td>";
echo "<td>From</td>";
echo "</tr>";
while($row=mysql_fetch_array($query)){
echo "<tr>";
echo "<td>"."<center>".$row['sno']."</center>"."</td>";
echo "<td>"."<center>".$row['Route']."</center>"."</td>";
echo "<td>"."<center>".$row['Bus_type']."</center>"."</td>";
echo "<td>"."<center>".substr($row['Timing'],0,5)."</center>"."</td>";
$route=$row['Route'];
$query2=mysql_query("SELECT fromcity FROM routes WHERE Route= '$route' ");
$row2=mysql_fetch_array($query2);
echo "<td>"."<center>".$row2['fromcity']."</center>"."</td>";

routes Table

buses table

Result

Answer 1

You can continue with your solution but for efficiency sake, you shouldn't be doing a SELECT inside a loop, you should be using an SQL JOIN.

Your query should be joining the two tables on the Route field:

SELECT * FROM buses b
INNER JOIN routes r ON b.Route = r.Route;

No extra queries will be needed inside the loop as you'll have access to the fromcity from this query. You may want to specifically declare the fields in the SELECT rather than using *.