mysql - get closest value to correct value return zero to many depending on result

Question

My site allows users to guess the result of a sports match. At the end of the match the guesses should be compared to the actual result. The winner(s) are the members with the closest correct guess

Im looking for a way to return all members who guessed the correct result and score difference IF NO (zero) member guessed correctly return members who guessed closest to the correct result

See MYSQL FIDLE EXAMPLE

I modified the script to change fixed values taking variables as you can see below

if(isset($_POST['resultBtn'])){
    foreach($_POST['winner'] as $id =>$winner){
        $winScore = $_POST['score'][$id];
        :
        :


$sql="SELECT p.*
        FROM Multiple_Picks p
        WHERE p.event_id='$matchId' AND 
        p.pick='$winner' AND
        abs(p.score-'$winScore') = (SELECT min(abs(p2.score-1))
                        FROM Multiple_Picks p2
                        Where p2.pick=p.pick AND
                        p2.event_id = p.event_id)";

My problem is if I run this script on the following table:

NOTHING gets displayed even if I put result exactly correct:

My variable values are correct in the sql statment so that is not the problem

Any help will be welcomed...

IMPORTANT THE USER WHO SELECTED CLOSEST CORRECT RESULTS, FOR ALL GAME, DURING THE ROUND IS THE WINNER

example: if user A won 4 of the picks and user B won 5 of the picks then user B is the winner of the round

Answer 1

Why don't you want just

SELECT p.*, abs(p.score-'$winScore') as diff
        FROM Multiple_Picks p
        WHERE p.event_id='$matchId' AND p.pick='$winner'
        ORDER BY diff ASC 
        LIMIT 1

This will return the closest member for the event. Remove the LIMIT if you need a few of them.

Also, never put your parameters directly into the SQL query, even trusted ones (not your case) and even if you're sure they will always be integer or non-string type. Use prepared statements.