Use data provided by array_count_values

Question

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!

@RomanPerekhrest thank you - I added a link to an image of how it should result. — Will WP
– Will WP, Commented Jun 12, 2016 at 15:45
@chris85 would this be as part of the original array or in its stead? — Will WP
– Will WP, Commented Jun 12, 2016 at 15:52
Instead, that should replace your current query and give you a count for each ID. — chris85
– chris85, Commented Jun 12, 2016 at 15:53
@chris85 thank you - this gives me the association between the user and the number of instances, but it sorts it by user id. Is there any way to sort it by the number of instances (descending)? Or is it not possible as that is something we find out only as we proceed? — Will WP
– Will WP, Commented Jun 12, 2016 at 16:00

chris85 · Accepted Answer · 2016-06-12 16:09:20Z

1

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

answered Jun 12, 2016 at 16:09

chris85

23.9k7 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will WP Over a year ago

Thank you for your help!

Collectives™ on Stack Overflow

Use data provided by array_count_values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related