Mysql - get the closest value to fix a value of zero, for many, depending on the result

Question

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

Im looking for a way to get back all the members who were guessing the correct result and evaluate the difference. IF NO (zero) member guessed correctly, returned members who guessed closer to the correct result

See MYSQL FIDLE EXAMPLE 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 that I ran this script in the following table:

NOTHING is displayed, even if I set the result correctly:

My variable values ​​are correct in sql status so this is not a problem

Any help would be appreciated ...

IMPORTANT, THE USER CHOSED THE CLOSED ARTIFICIAL RESULTS, FOR THE WHOLE GAME, DURING THE CIRCLE - THE WINNER

example: if user A won 4 from Kiev, and user B won 5 selected, then user B won the round

Answer 1

In this answer, I call "Best" to choose any choice that has selected the right winner for a particular match and has the closest result to the actual score of the match.

These scenarios also respect the various “rounds” in the contest, as this is an important complication.

This answer consists of two parts: first, a query similar to the query in the question, which returns all the "best" options for a particular match. To make it easier to run in SQL Fiddle, I used MySQL variables instead of PHP variables.

Scheme with test data:

 create table Multiple_Picks ( pick_id int, member_nr int, event_id int, pick varchar(100), score int ); insert into Multiple_Picks values (11,100,1,'Crusaders',15), (12,100,2,'Waratahs',10), (13,100,3,'Chiefs',4), (21,200,1,'Crusaders',15), (22,200,2,'Waratahs',10), (23,200,3,'Lions',4), (31,300,1,'Crusaders',15), (32,300,2,'Waratahs',12), (33,300,3,'Lions',6), (41,100,4,'Crusaders',20), (42,100,5,'Waratahs',20), (43,100,6,'Lions',20) ; 

Requests to show all selections, and then it’s best to choose for a specific match:

 set @matchId = 2; set @winner = 'Waratahs'; set @winScore = 8; -- Show all picks for a particular match select * from Multiple_Picks where event_id = @matchId; -- Show best picks for a particular match select p.* from Multiple_Picks p where p.event_id = @matchId and p.pick = @winner and abs(p.score - @winScore) = (select min(abs(other.score - @winScore)) from Multiple_Picks other where other.event_id = @matchId and other.pick = @winner ) ; 

SQL Fiddle to show selections for a specific match

 -- Show all picks for a particular match +---------+-----------+----------+----------+-------+ | pick_id | member_nr | event_id | pick | score | +---------+-----------+----------+----------+-------+ | 12 | 100 | 2 | Waratahs | 10 | | 22 | 200 | 2 | Waratahs | 10 | | 32 | 300 | 2 | Waratahs | 12 | +---------+-----------+----------+----------+-------+ -- Show best picks for a particular match +---------+-----------+----------+----------+-------+ | pick_id | member_nr | event_id | pick | score | +---------+-----------+----------+----------+-------+ | 12 | 100 | 2 | Waratahs | 10 | | 22 | 200 | 2 | Waratahs | 10 | +---------+-----------+----------+----------+-------+ 

Now we need to work on finding the winner of each round of the competition.

First we have additional test data containing actual points for matches in rounds 1 and 2.

 create table Matches ( event_id int, winner varchar(100), score int, round int ); insert into Matches values (1,'Crusaders',10,1), (2,'Waratahs',11,1), (3,'Lions',4,1), (4,'Crusaders',20,2), (5,'Waratahs',20,2), (6,'Chiefs',20,2) ; 

Now choose the best bet for all matches. Subselect (aliased as m) calculates the best_diff for each match as the minimum difference between the actual score and each guessed score. Then this subtask is connected to each selection so that only the "best" samples are returned.

 -- Show all best picks for all Matches select p.*, m.round from Multiple_Picks p join ( select m2.event_id, m2.winner, m2.score, m2.round, min(abs(m2.score-p2.score)) as best_diff from Matches m2 join Multiple_Picks p2 on p2.event_id = m2.event_id and p2.pick = m2.winner group by m2.event_id, m2.winner, m2.score, m2.round ) as m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff order by m.round, p.event_id ; 

Then it’s easy to get the best choices for each player for each round by simply grouping the previous query with member_nr and round:

 -- Show a count of best picks for each player for each round select p.member_nr, m.round, count(*) as best_count from Multiple_Picks p join ( select m2.event_id, m2.winner, m2.score, m2.round, min(abs(m2.score-p2.score)) as best_diff from Matches m2 join Multiple_Picks p2 on p2.event_id = m2.event_id and p2.pick = m2.winner group by m2.event_id, m2.winner, m2.score, m2.round ) as m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff group by p.member_nr, m.round order by m.round, count(*) desc ; 

SQL Fiddle for all the best choices and counts for all matches

 -- Show all best picks for all Matches +---------+-----------+----------+-----------+-------+-------+ | pick_id | member_nr | event_id | pick | score | round | +---------+-----------+----------+-----------+-------+-------+ | 31 | 300 | 1 | Crusaders | 15 | 1 | | 21 | 200 | 1 | Crusaders | 15 | 1 | | 11 | 100 | 1 | Crusaders | 15 | 1 | | 12 | 100 | 2 | Waratahs | 10 | 1 | | 32 | 300 | 2 | Waratahs | 12 | 1 | | 22 | 200 | 2 | Waratahs | 10 | 1 | | 23 | 200 | 3 | Lions | 4 | 1 | | 41 | 100 | 4 | Crusaders | 20 | 2 | | 42 | 100 | 5 | Waratahs | 20 | 2 | +---------+-----------+----------+-----------+-------+-------+ -- Show a count of best picks for each player for each round +-----------+-------+------------+ | member_nr | round | best_count | +-----------+-------+------------+ | 200 | 1 | 3 | | 300 | 1 | 2 | | 100 | 1 | 2 | | 100 | 2 | 2 | +-----------+-------+------------+ 

The final step is to select only those players for each round who have the highest number of Best Choices. I tried modifying the above queries, but nesting becomes two confusions, so my solution was to create some logical representations so that the final query could be easily understood. The views basically embody the logic of the queries, which I explained above:

 create view MatchesWithBestDiff as select m.event_id, m.winner, m.score, m.round, min(abs(m.score-p.score)) as best_diff from Matches m join Multiple_Picks p on p.event_id = m.event_id and p.pick = m.winner group by m.event_id, m.winner, m.score, m.round ; create view BestPicks as select p.*, m.round from Multiple_Picks p join MatchesWithBestDiff m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff ; create view BestPickCount as select member_nr, round, count(*) as best_count from BestPicks group by member_nr, round ; 

So, the query that shows the winners of each round is simple:

 -- Show the players with the highest number of Best Picks for each round select * from BestPickCount p where best_count = ( select max(other.best_count) from BestPickCount other where other.round = p.round ) order by round ; 

SQL Fiddle for Players with the Best Choice for Each Round

 -- Show the players with the highest number of Best Picks for each round +-----------+-------+------------+ | member_nr | round | best_count | +-----------+-------+------------+ | 200 | 1 | 3 | | 100 | 2 | 2 | +-----------+-------+------------+ 

All this research reminded me how difficult it is to get SQL to do a lot of manipulations when records need to be selected depending on the maxima and amounts. Some of these types of queries can be much simpler with window functions (OVER and PARTITION BY clauses), but they are not available in MySQL.

When developing the above queries, I found some interesting limitations: MySQL does not allow joining subqueries in view definitions. ANSI SQL does not allow an aggregate in a subquery to refer to either a column from an internal query or a column from an external query. MySQL seems to allow this sometimes, but I couldn’t find a clear indication of when it was allowed, so I decided to program the above queries to avoid this “function”.

Answer 2

Why don't you 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 LIMIT if you need several of them.

In addition, never put your parameters directly in the SQL query, even in trusted ones (not in your case), and even if you are sure that they will always be integer or non-string. Use prepared statements.

Answer 3

Scenario 1: NO USERS CHOOSE THE RIGHT TEAM

I believe that the result in this situation should be an empty result, because everyone made a mistake.

MEMBERS WHO OPEN CLOSE TO INCREASE ACCOUNT AND RESULT

It seems to work already in your code example, except for one error in select.

  abs(p.score-'$winScore') = (SELECT min(abs(p2.score-1)) 

Instead of the constant 1 (one), it should be the variable '$winScore'

and to control the number of users you get, you can limit your results to get the following:

 $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-'$winner')) FROM Multiple_Picks p2 Where p2.pick=p.pick AND p2.event_id = p.event_id) order by p.id limit '$numberOfMembers'"; 

Scenario 2: Scenario 2: Multiple Users Selecting the Right Team

The same as in the previous question.

SCENARIO 3: MULTIPLE USERS SELECTED RIGHT TEAMS AND SPEED. RETURN OF ALL USERS WHO DEPARTED THE RIGHT TEAM AND SPEED.

You can achieve this using the same query, just replace LIMIT with the "rank" function, and also if you get a few immediate results, but you should limit their number in accordance with the order of their voting by id, for which I suggest sorting. Thus, the final request will look like this:

 $sql="select * from (SELECT p.*, abs(p.score-'$winScore') scr_diff, @rownum := @rownum + 1 rank FROM Multiple_Picks p, (SELECT @rownum := 0) rank_gen WHERE p.event_id='$matchId' AND p.pick='$winner' AND abs(p.score-'$winScore') = (SELECT min(abs(p2.score-'$winner')) FROM Multiple_Picks p2 Where p2.pick=p.pick AND p2.event_id = p.event_id) order by p.id ) sq where sq.scr_diff = 0 or sq.rank < '$numberOfMembers'"; 

Fiddle

Answer 4

The best guesser for one match

First find the member (s) who selected the winner and received the closest rating:

 SELECT p.* FROM ( SELECT MIN(ABS(score-'$winScore')) AS closest FROM Multiple_Picks WHERE event_id = '$matchId' AND pick='$winner' ) AS c JOIN Multiple_Picks p WHERE p.event_id = '$matchId' AND p.pick = '$winner' AND ABS(score-'$winScore') = c.closest 

If this does not produce any results, then what should happen? (This would be because no one chose a winner for a particular event.)

But, I think your question is much more complicated. However, the above gives a mapping from (event_id, pick) → the list of participants who “won”. Beginning with...

Missing Information

There is a riddle - where did the results come from? I assume this table is already populated:

 CREATE TABLE Win ( event_id ..., -- which game winnner ..., -- who won score ... -- by what score ) 

The best option for divination

So create a table BestGuessers (event_id, member). The details “all games” and “round” are a bit vague. Therefore, I will continue this at least one step further.

 CREATE TEMPORARY TABLE BestGuessers( event_id ..., member_nr ... -- who guessed the best for that event ) SELECT p.event_id, p.member_nr FROM ( SELECT w.event_id, w.winner, MIN(ABS(mp.score-w.score)) AS closest FROM Multiple_Picks AS mp JOIN Win AS w ON mp.event_id = w.event_id AND mp.pick = w.winner GROUP BY w.event_id, w.winner ) AS c JOIN Multiple_Picks p ON p.event_id = c.event_id AND p.pick = c.pick AND p.score = c.closest 

Now, from this you can choose the best guesses (s).

 SELECT y.member_nr FROM ( SELECT COUNT(*) AS ct FROM BestGuessers GROUP BY member_nr ORDER BY COUNT(*) DESC LIMIT 1 ) AS x -- the max number of correct guesses STRAIGHT_JOIN ( SELECT member_nr, COUNT(*) AS ct FROM BestGuessers GROUP BY member_nr ) AS y -- the users who guessed correctly that many times USING (ct); 

All of this is quite complicated; I may have typos, even logical errors. But maybe I came up.

Answer 5

It seems an extra table to store actual results will help here. For example, suppose this is in a table called results with sample values ​​as follows:

 event_id winner result 1 Crusaders 16 2 Waratahs 15 3 Chiefs 4 4 Crusaders 17 5 Reds 12 0 Rebels 14 7 Cheetahs 15 8 Crusaders 14 

It can be JOIN ed for each row, and the results are compared as follows:

 SELECT p.* , CASE WHEN ABS(p.score - r.result) - CASE WHEN p.pick = r.winner THEN 999999 ELSE 0 END = (SELECT MIN(ABS(p2.score - r2.result) - CASE WHEN p2.pick = r2.winner THEN 999999 ELSE 0 END) FROM picks p2 JOIN results r2 ON p2.event_id = r2.event_id WHERE p2.event_id = p.event_id) THEN 1 ELSE 0 END AS win FROM picks p JOIN results r ON p.event_id = r.event_id; 

Explanation

The rightmost win column is 1 if the member is designed to win or draw an event, otherwise it is 0. The method used is similar to the method used in your message, the main difference being that the team and the score are combined. The main thing to explain here is 999999, which is deducted when choosing the right team - so this can overshadow the difference in the score. (Of course, even a larger value can be selected if necessary).

Demo

SQL Fiddle Demo