CODEWITHSUNDEEP
phpmysqlarrays
Will WP
Will WP

Reputation: 1237

Use data provided by array_count_values

I have created an array that takes some data from a DB and enters it into the array.

Using array_count_values, I also find out how many times each value appears in the array, and the resulting array looks like this:

Original code:

$winnersarray = array();
$winnersresults=mysql_query("SELECT r.driverId FROM results r JOIN calendar c ON r.eventID=c.id WHERE c.event='$eventName' AND c.competition='$competition_id' AND r.position='1' AND r.eventSession NOT IN ('T','P','Q','Q1','Q2','QA')");
while($row = mysql_fetch_assoc($winnersresults)) {
    $winnersarray[]=$row['driverId'];
}

print_r(array_count_values($winnersarray));

Array printed:

Array ( [887] => 1 [191] => 1 [94] => 2 [253] => 1 [578] => 1 [50] => 2 [4] => 1 [179] => 1 [59] => 1 [95] => 1 [333] => 2 [936] => 1 )

At this point, I get stuck. What I am aiming to achieve is a table that features the number of instances the value appears (i.e. 2 for user 94, user 50 and user 333), and other data using these IDs (namely, the username associated to it and the IDs of the events where that ID was in position 1).

Any pointer in the right direction is welcome!

EDIT: a similar table, from wikipedia, showing how I would want my final table data to look: https://i.sstatic.net/t0h6F.png The table is sorted by number of wins, which is also something I need to do with the above data.

PS - i know mysql is depreciated - I am going through legacy code and updating to mysqli as I go. The site is a good 4-5 years old so there is quite a bit to tinker!

Upvotes: 0

Views: 105

Answers (1)

chris85
chris85

Reputation: 23892

This can be accomplished using just SQL, no need for the PHP array functions. Use SQL's group by, count and order by. Here's how it should work:

SELECT count(r.driverId) as count, r.driverId 
FROM results r 
JOIN calendar c 
ON r.eventID=c.id 
WHERE c.event='$eventName' AND 
c.competition='$competition_id' AND 
r.position='1' AND 
r.eventSession NOT IN ('T','P','Q','Q1','Q2','QA') 
group by r.driverId 
order by count desc

Upvotes: 1

Related Questions