-1

Hi so I've already got a php script working working for this but it use a massive amount of data for a really easy task so I really would like to improve it.

My problem is the following: I have a SQL database build like this

and I would to do request like this one:

http://myphppage.php?username=Whataname&stage=1

and I would like it to echo the result so that if I read my php page content I can read the following:

21

so basicly all I want is to do a request with the username and the stage as parameters and I would like it to return the lowest value of the column "time" WHERE name=the name parameter entered AND stage = the stage parameter entered

I'm not really good in sql but I'm pretty I can make this kind of sql request in a single line or two instead of this massive script I have right now.

here's the current script I have:

<?php
$q2 = "SELECT username FROM DB WHERE stage='$stage' GROUP BY username ORDER BY time ASC";
$result2 = mysql_query($q2);

$times = array();
$userusernames = array();

$usernames = mysql_fetch_assoc($result2);
while($rows=mysql_fetch_assoc($result2))
{
    $temp = $rows['username'];
    $q3 = "SELECT time FROM DB WHERE stage='$stage' AND username='$temp' GROUP BY time ORDER BY time ASC";
    $result3 = mysql_query($q3);
    while($aaa = mysql_fetch_assoc($result3))
    {
        array_push($times, $aaa['time']);
        array_push($userusernames, $rows['username']);

        if($rows['username']==$username)
        {
            echo $aaa['time'];
        }
        break;
    }

}

?>

may someone please help me figuring out how to do this

EDIT: I have been looking around on internet but I can't find what I'm looking for, I'm pretty sure there's the answer somewhere so maybe you can just help me reformulate my question and I could find the answer by myself. I'm pretty stuck due to my lack of english vocabulary...

thx in advance

Vylaxez
  • 21
  • 3
  • 1
    You need to `LIMIT 1` your query :) and remove your `GROUP BY`? You can remove the rest of your PHP loops, you really don't need it. – user228395 Jun 18 '18 at 19:03
  • why you are using group by? .. if you need distinct values use DISTINCT and not an improper group by – ScaisEdge Jun 18 '18 at 19:04
  • thx gonna try this out, I didn't know about this LIMIT parameter in sql request – Vylaxez Jun 18 '18 at 19:04
  • so I tryed this out: SELECT time FROM DB WHERE stage='$stage' AND name='$name' ORDER BY time ASC LIMIT 1 and it works perfectly, thank you very much !!! I have to admit I feel very stupid to not have though to this before ... Thanks again – Vylaxez Jun 18 '18 at 19:11
  • Without any index on the `username` column, the query using LIMIT can have a performance impact. Look at https://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit for more infos. – Jarzon Jan 31 '19 at 01:26

1 Answers1

0

You can archive the wanted result with only one query:

SELECT username, MIN(time) AS lowertime
FROM test_table
WHERE stage='$stage' AND username='$temp'
GROUP BY username
ORDER BY time
Jarzon
  • 615
  • 1
  • 6
  • 16