I’m trying to calculate a win chance from buying a freebies ticket in the percentage of each user that has been recorded in the database
here is my freebies table
fr_id | user_id 1 | 60 2 | 50 3 | 4 4 | 60 5 | 60 6 | 60 7 | 4 8 | 60 9 | 50 10 | 50
the total number of tickets is 10
- user id 60 have a 5 tickets
- user id 50 have a 3 tickets
- user 4 have 2 tickets
so I want to calculate on each user how much win chance they get
for example :
- user 60: 70% win chance
- user 50: 25% win chance
- user 4: 5% win chance
how to calculate it in PHP code based on user id?
$win_chance = $database->query("SELECT * FROM freebies WHERE user_id='60'"); echo $win_chance; //example 70%
Advertisement
Answer
You can use next simple query:
select sum(user_id = 60) / count(*) as chance from tickets;
Below is PHP implementation:
<?php $user_id = 60; $query = "select sum(user_id = ?) / count(*) as chance from tickets;"; // get DB version using PDO $stmt = $pdo->prepare($query); $stmt->execute([$user_id]); $row = $stmt->fetch(PDO::FETCH_ASSOC); printf('Win chance for user %d is %s ' . PHP_EOL, $user_id, $row['chance']);