Ytukyg

Background: I'm a hobby programmer, trying to create a personal leaderboard for a racing game. This is done with a Raspberry Pi running a MariaDB. Leaderboard is scraped from another site and stored in the db. Then my personal laptimes are received with UDP from the game, and stored in the same MariaDB on the Pi. The results are shown on a php page. User can select different car and track combo on the php page. I want to display every players best (lowest) laptime for the chosen car-track combo. (Player names are offical gamertags, where no duplicates are allowed. They are all unique.)

With a SELECT statement like this, I get 84 rows, but I get all my own laptimes, and I only want to show my best laptime for that car-track combo.

$sqlstring1="SELECT * FROM laptimes WHERE track = '{$trackselect}' {$choice} AND carname LIKE '{$carselect}' ORDER BY laptime ASC";

I tried to use a SELECT like this, but then I only get 54 rows? I only have two laptimes recorded, so a bunch of laptimes from other people are not shown.

$sqlstring1 = "SELECT t1.* FROM laptimes t1

JOIN (

SELECT player, MIN(laptime) AS min_laptime

FROM laptimes 

GROUP BY player

) AS t2 ON t1.player = t2.player AND t1.laptime = t2.min_laptime

WHERE track = '{$trackselect}' {$choice} AND carname LIKE '{$carselect}' 

ORDER BY laptime ASC";

I've tried different SELECTS too, with LEFT JOIN for example.
The $trackselect and $carselect are values from dropdowns on php page, They seem to be working in both cases. $choice value is from two different buttons on php page. Button 1 sets $choice ="". Button 2 sets $choice = " AND player LIKE 'Maskmagog' ". This is used to show only my laptimes. It seems to be working.

Question: Why are some rows not selected in the 2nd SELECT?

Here are the top 5 output for the first SELECT:

1.  sel                 Ginetta G40 Junior  00:56.017   

2.  {DFRUK}michaeldimel Ginetta G40 Junior  00:56.149   

3.  Yorkshire Farmer    Ginetta G40 Junior  00:56.709   

4.  piquet              Ginetta G40 Junior  00:56.756   

5.  Oomph               Ginetta G40 Junior  00:56.981   

and the 2nd SELECT:

1.  Yorkshire Farmer    Ginetta G40 Junior  00:56.709   

2.  saya777             Ginetta G40 Junior  00:57.002   

3.  Lamb4chop™          Ginetta G40 Junior  00:57.067   

4.  Jonge               Ginetta G40 Junior  00:57.250   

5.  NsGTR35             Ginetta G40 Junior  00:57.296   

As you can see, only "Yorkshire Farmer" survives and is shown in both selects, and I don't understand why.

Here is the full php page, on Pastebin: https://pastebin.com/ALTzfm2S

EDIT: Thanks to comments below, I finally understand why the 2nd SELECT doesn't work. It first selects the players lowest laptime, regardless of track/car. Then it applies the WHERE, and the players who have their lowest laptimes set in other cars/tracks are of course removed from the result. D'oh! :)

EDIT 2: Well it seems I can't mark more than one answer. Both solutions seems to work, many thanks to all involved! I can't tell if one answer is better/more useful/more correct.

Based on your requirements, you don't need nested SELECT's. You can add the conditions to your inner query and use that:

SELECT player, MIN(laptime) AS min_laptime

FROM laptimes 

WHERE track = ?

    AND carname LIKE ?

    AND player LIKE ?

GROUP BY player

ORDER BY laptime ASC

If you don't need all conditions, you can build the sql statement string dynamically, adding only the ones you need. Afterwards you bind the required number of variables and execute the statement.

Your problem is that your subquery is returning the minimum lap time for a player regardless of track or car. You need it to return the minimum lap time for the player for each car on each track they have raced on, so you need to change the GROUP BY to

GROUP BY player, track, carname

and then you also need to add track and carname to the subquery SELECT and to the JOIN. So your query should look like:

$sqlstring1 = "SELECT t1.* FROM laptimes t1

JOIN (

SELECT player, track, carname, MIN(laptime) AS min_laptime

FROM laptimes 

GROUP BY player, track, carname

) AS t2 ON t1.player = t2.player AND t1.laptime = t2.min_laptime AND t1.track = t2.track AND t1.carname = t2.carname

WHERE track = '{$trackselect}' {$choice} AND carname LIKE '{$carselect}' 

ORDER BY laptime ASC";