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!
