Query including MIN()

Databases
1

Hi everyone,

I’m stuck with another query and could use some assistance please. I am looping out all modes of transport and prices for a given city, but I also need the transport mode with the lowest price.

The query below will print for example:

bus
£20
train
£30
taxi
£80

I need the query to print:

bus
£20
train
£30
taxi
£80

£20


$q = "SELECT c.city_name, t.transport_mode, t.transport_price
FROM cities AS c INNER JOIN transport AS t
ON t.city_id=t.city_id
WHERE c.city_name = 'london'";
$r = @mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
	echo ''.$row['transport_mode'].'<br />';
	echo ''.$row['transport_price'].'<br />';
}

If I add MIN(t.transport_price) AS lowest_tp after my other columns only the first transport mode and price will print out.

Thank you in advance.

2

surely you will encounter the lowest price amongst all the prices that you are “looping out”

i don’t do php but this is a very trivial programming task

3

Thanks, there is a PHP min() function which does the trick.

Cheers.