I'm building a simple website to let people at my work easily match employees names to their baby pictures, using Jquery draggable script.
I have two tables (USERS and ENTRIES). There is a 3rd table called PEOPLE but it's not important for this question.
In entries, the userid of "0" has the CORRECT ordering (i.e. personid 1 should be 4th. personid 2 should be 3rd, etc. And again, personid is from another table called PEOPLE that shouldn't matter for this question).
+----------+---------+--------------+
| userid |firstname| lastname
+----------+---------+--------------+
| 1 | Bob | Wilson |
| 2 | Charlie | Jackson |
| 3 | Jim | Smith |
| 4 | Doug | Jones |
+----------+---------+--------------+
+----------+---------+--------------+
| userid | personid| ordering
+----------+---------+--------------+
| 0 | 1 | 4 |
| 0 | 2 | 3 |
| 0 | 3 | 1 |
| 0 | 4 | 2 |
| 1 | 1 | 2 |
| 1 | 2 | 4 |
| 1 | 3 | 1 |
| 1 | 4 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 3 |
| 2 | 3 | 4 |
| 2 | 4 | 2 |
+----------+---------+--------------+
I will actually have probably 100 users with entries in the entries table. And each user will have 100 personids with an ordering. What I want to do is, in the most efficient, logical way, loop through all of the entries and compare each one to the CORRECT answer (i.e. userid 0).
So my thinking is probably to get all of the entries in arrays and then compare array for userid 1 to the array for userid 0. Then compare the array for userid2 to the array for userid 0. And so on.
I just want to compare how many right answers each subsequent user has. So in my example tables, userid 1 has ONE correct answer (Personid 3 matching with ordering 1) and userid 2 has TWO correct answers (personid 2 matching with ordering 3 and personid 4 matching with ordering 2).
I first did this...
$sql = "SELECT * FROM entries";
$getpeople = mysqli_query($connection, $sql);
if (!$getpeople) {
die("Database query failed: " . mysqli_error($connection));
} else {
while ($row = mysqli_fetch_array($getpeople)) {
$entriesarray[$row['userid']][$row['personid']]=$row['ordering'];
}
}
That would give me a bunch of arrays for all users with their entries.
Then I did this as a test...
$result_array = array_intersect_assoc($entriesarray[1], $entriesarray[0]);
print_r($result_array);
echo "COUNTRIGHT=".count($result_array);
And that essentially does what I want by giving me COUNTRIGHT of "1". It sees how many from the array for userid 1 match value AND key from the array for userid 0 (again, the correct answer array).
But now I'm stumped as to how to do this efficiently in a nice loop, rather than having to do it one by one. Again, I'd probably have 100 users to loop through. And I'm questioning whether my initial mySQL query above is correct or should be done differently.
And ultimately, I'd want to list out all users firstname, lastname and the number they got right. And order them DESC by the number they got right. In essence, it'd be a leaderboard that would look like...
Jim Smith 2
Charlie Wilson 1
and so on (but on a much greater scale where the person in first place will probably have around 80 or 90 correct).
Because I want to show names too on the "leaderboard", I know I need a JOIN somewhere in here to get that info from the USERS table, so it gets even more convoluted for my tiny brain :)
I hope this makes sense to someone. If anyone can point me in the right direction, that would be fantastic. I'm losing my mind and it's probably fairly simple at the end of the day.
Thanks!
The query below will give you a count of correct entries per user by left joining to user 0 and counting the ordering matches for each person
select t1.userid, count(t2.*)
from entries t1
left join entries t2 on t2.userid = 0
and t2.personid = t1.personid
and t2.ordering = t1.ordering
group by t1.userid
If you need names from the user table you can join it
select u.*, count(t2.*)
from entries t1
join users u on u.userid = t1.userid
left join entries t2 on t2.userid = 0
and t2.personid = t1.personid
and t2.ordering = t1.ordering
group by u.userid
You're on the right track. Comparing all the users in your $entriesarray in a loop is not going to be much more complicated than what you've already done.
First, shift user 0 from the $entriesarray to get the correct set to match against. Then you can just iterate over the rest of the entries array calculating the correct matches like so...
$correctSet = $entriesarray[0];
unset($entriesarray[0]); // we don't want to check this against itself
$leaderBoard = []; // initialize an empty leaderboard array
foreach($entriesarray as $userId => $entries) {
$correctMatches = count(array_intersect_assoc($entries, $correctSet));
$leaderBoard[$userId] = $correctMatches;
}
As far as doing this in SQL, it's also possible by just doing a JOIN against user_id 0 as already answered above (so I won't bother repeating). The user information can also be obtained separately and looked up by user_id since you already have that information in your $leaderboard array in this approach.