-3

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

sagi
  • 40,026
  • 6
  • 59
  • 84
irfan spi
  • 86
  • 2
  • 13
  • Oh god, the markup... When doing blocks of code just leave 4 spaces in front of it, don't use back ticks... I've done the first three lines for you. Edit your question to tidy it up please. – Styphon May 05 '16 at 14:16
  • I retrive a the value in $route variable like this $route=$row['Route']; – irfan spi May 05 '16 at 14:24
  • 1
    [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard May 05 '16 at 14:25
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 05 '16 at 14:26

1 Answers1

2

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

Devon Bessemer
  • 34,461
  • 9
  • 69
  • 95
  • how can i print the result of this query – irfan spi May 05 '16 at 14:33
  • Well, how do you print the results of any query? `print_r(mysql_fetch_array($query))` would print out the first row so you can see the contents. I highly urge you to listen to the other advice and stop using mysql_ functions though. Learn PDO. – Devon Bessemer May 05 '16 at 14:35
  • i tried to print the result but its not showing value of fromcity – irfan spi May 05 '16 at 14:43