How to calculate the percentage from MYSQL database using PHP?

Question

I have a table called feedbacks that looks like this:

id    user    type
1    JOhnT  Positive
2    JOhnT  Negative
3    Sarah  Positive
4    JOhnT  Positive
5    JOhnT  Neutral
....................

I need to get the percentage of POSITIVE feedback for each user using PHP.

I tried something like this which i know it is wrong:

$sql = "SELECT type, count(*), 
concat(round(( count(*)/(SELECT count(*) FROM feedbacks WHERE user='JOhnT' AND type='POSITIVE') * 100 ),2),'%') AS percentage 
FROM feedback 
WHERE user='$email' AND type='positive' GROUP BY type";
$query = mysqli_query($db_conx, $sqlJ);

echo $sql;

Could someone please advice on how to achieve this?

EDIT:

Based on the comments, this is what i have so far:

$sql = "SELECT * FROM feedbacks WHERE user='JOhnT' AND type='POSITIVE'";

$query = mysqli_query($db_conx, $sql) or die(mysqli_error($db_conx));

$productCount = mysqli_num_rows($query);

if ($productCount > 0) {
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){

 ///do I need to calculate the percentage here?//
//if so, how?////


}
}

I ended up creating an array and push each $row in my whole loop into the array and then calculate the percentage like this:

   $c = count($values);
    $array = $values;       
    function array_avg($array, $round=1){
    $num = count($array);
    return array_map(
        function($val) use ($num,$round){
            return array('count'=>$val,'avg'=>round($val/$num*100, $round));
        },
        array_count_values($array));
}

$rating = 0;                    

if($c > 0){
$avgs = array_avg($array);
$rating = $avgs["positive"]["avg"];
}

This works fine for now.

If you're wanting to echo those out, you're not doing it the right way. You're trying to echo a result set. You need to use a loop. — Funk Forty Niner, Jun 07 '20 at 13:50
Please explain what is wrong. You don't get a result, the data is incorrect? Also is PHP related to the issue or is this a query question? If you want to calculate in PHP (which I wouldn't) don't use aggregate functions, return all the data then aggregate and calculate in PHP. — user3783243, Jun 07 '20 at 13:51
@user3783243 are you saying to get the data as i usually get from mysql and then calculate the percentage in the php in a while loop? — user2056633, Jun 07 '20 at 13:54
@Dharman was the downvote because of not using PDO or something related to the actual question being asked? — user2056633, Jun 07 '20 at 14:02
If you want to do it `using php` then yes. If the issue isn't with PHP and is really with the aggregate functions you should explain that more and remove the PHP association. — user3783243, Jun 07 '20 at 14:02
@user3783243 I have edited my question. and yes the issue is related to the PHP as I dont have a single clue how to do it using PHP. — user2056633, Jun 07 '20 at 14:03
If you want to know the reason why someone has downvoted a post you can hover over the downvote button with your mouse. It will tell you the reason. — Dharman, Jun 07 '20 at 14:03
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jun 07 '20 at 14:04
Does this help? https://stackoverflow.com/a/15746938/1839439 — Dharman, Jun 07 '20 at 14:04
@user2056633 What happened to aggregate functions? Percentage is total positive feedback for John vs all users, or of Johns feedback what is positive? — user3783243, Jun 07 '20 at 14:05
@Dharman, the user2056633 just advised against doing it that way if I understood correctly. — user2056633, Jun 07 '20 at 14:06
No, you misunderstood them. They said not to do it in PHP. Do it in MySQL. — Dharman, Jun 07 '20 at 14:06
@user3783243, the percentage of POSITIVE feedback for John only... so for example, if John has 24 feedback and they are POSITIVE then he has 100% rating OR If he has 24 feedback and 7 of them negative and 4 of them are neutral and the rest of them are POSITIVE then calculate the percentage of POSITIVE here... does that make sense? — user2056633, Jun 07 '20 at 14:10
So you can't have your the `positive` constraint on the where clause in that case. You can do this all in MySQL much easier, close to what you had initially. Why do you want to do this in PHP? Something like `SELECT sum(case when type='positive' then 1 else 0 end) / count(*) as positive_percent FROM feedbacks WHERE user='JOhnT';` I think would be easiest. Then use `positive_percent` index in PHP. — user3783243, Jun 07 '20 at 14:13
@user3783243, sorry for being daft but how do i have to use the `positive_percent` index in PHP? — user2056633, Jun 07 '20 at 15:01

score 1 · Answer 1 · answered Jun 07 '20 at 14:55

1

You can do this a conditional average. In MySQL, you could phrase this as:

select user, avg(type = 'Positive') positive_ratio
from feedback
group by user

This gives you, for each user, a value between 0 and 1 that represents the ratio of positive types. You can multiply that by 100 if you want a percentage.

If you want this information for a single user, you can filter with a where clause (and there is no need to group by):

select user, avg(type = 'Positive') positive_ratio
from feedback
where user = 'JOhnT'

Side note: user is a MySQL keyword, hence not a good choice for a column name.

answered Jun 07 '20 at 14:55

GMB

216,147
25
84
135

How should I go about using this query in PHP? by that I mean how do I echo the result? – user2056633 Jun 07 '20 at 15:03
@user2056633: are you asking how to execute a query and fetch the results in PHP? That's basic MySQL/PHP usage, you can find a lot of tutorials in the wild on how to proceed. – GMB Jun 07 '20 at 15:07
No, no.. I'm asking how to echo the result of 'positive_ratio' which is in your answer. that is an index. isn't it? – user2056633 Jun 07 '20 at 15:08
@user2056633 It is an alias. It is the index of an array in PHP if fetched as associative. So in your previous code `$row['positive_ratio']` and remove the `while` because I believe this will only return 1 row. – user3783243 Jun 07 '20 at 15:51

How to calculate the percentage from MYSQL database using PHP?

1 Answers1