Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
630 views
in Technique[技术] by (71.8m points)

html - Using Select Query inside a while loop in php

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

enter image description here

buses table

enter image description here

Result

Not showing Data Of From city Field

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 *.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...