₦airaland Forum

I'm trying to create an online booking system for a small inter-city transport company. I have three tables namely bus, route, bus_route

bus
-----------
id
bus_number
total_seat
Status


route
-----------
route_id
route_number
route_from
route_to
time
price
status


bus_route
-----------
id
bus_id
route_id

Please, I need an SQL JOIN query to retrieve the bus number, time, price, and route when status=2 (both bus and route) when you query using route_id

My brain is cracked and needs to be joined with superglue, cant join any query as i dey like this.

select
b.number number, r.time time, r.price price, r.route_to rto
from
bus b LEFT JOIN (route r,bus_route br) ON (??i don forget??)
WHERE r.status=2;

Na wia my brain fit reach be this o jare - make another person try.

I finally found a way to do it. This is it below so others can learn. I'm surprised nobody could help. Wonder why sharing knowledge is such a big problem to some of us here.

		$query = mysql_query("SELECT bus_number, route_from, route_to, time, price, route_number
				FROM bus
				JOIN bus_route ON bus_route.bus_id = bus.id 
				JOIN route ON bus_route.route_id = route.route_id
				AND route.status =  '".mysql_real_escape_string($status)."'
				AND bus_route.route_id = '".mysql_real_escape_string($route_id)."'
				WHERE bus.status =  '".mysql_real_escape_string($status)."'
				AND bus_route.status = '".mysql_real_escape_string($status)."'
		" or die(mysql_error());
		$count = mysql_num_rows($query);
		$row = mysql_fetch_assoc($query);
		

Having small issues with outputting the array that is returned from the query. This is how the array was created

				do{
					$content["bus_number"][] = $row['bus_number'];
					$content["route_from"][] = $row['route_from'];
					$content["route_to"][] = $row['route_to'];
					$content["price"][] = $row['price'];
					$content["time"][] = $row['time'];
				}
				while($row = mysql_fetch_assoc($query)) ;

I want to output it formatted in a table. Please, can anyone be kind enough to show me how to do this. I've tried using a foreach loop but i get Array as output

g