how to get count(*) the right way in php

Question

I tried to count() the data from database with php but it don't show me the total data but it show the datas.

this is how I count

$query = "SELECT 
        mitra.*,         
        user.username, 
        user.privilege, 
        user.name 
    FROM 
        mitra
    INNER JOIN 
        user ON mitra.id_user = user.id "

$result = $connection->query($query);

if ($result->num_rows > 0) {
    foreach ($result as $row) :
        $id = "" . $row["id"] . "";
        $total = "" . $row["total_puas"] . "";
        $privilege = "" . $row["privilege"] . "";

        if ($privilege == 2) :
            $calculate = $total / count($id);
            var_dump(count($id));
        endif;
    endforeach;
}

===================
=   id =  total   =
=  1   =   45.84  =
=  2   =   75.45  =
=  3   =   34.54  =
===================

when I var_dumb it it shows int(1)int(1)int(1) not int(3) that what I wanted.

actually I want to count $calculate with the data in $total that should be there is float and the amount from $total that I want to divided with count id

is there any solution that how to count the amount from $total and can be devided with count $id that should be 3?. please help

what I really trying to do from that table example is like 45.84 + 75.45 + 34.54 / 3

Answer 1

You can try this code:

<?php
$i = 0;
$total =0.00;
if ($result->num_rows > 0) {
    while ($row = $result->fetch_array()):
       if ($row["privilege"] == 2) :
          $total = $total + $row["total"];
          $i++;
       endif;
    endwhile;
    echo $total."<br>";
    echo $i."<br>";
    echo  $calculate = $total / $i;
}

?>

output
=====================================
 $total = 155.83;
 $i = 3;
 $calculate = $total/$i;
 $ans = 51.943333333333;
=====================================

Answer 2

Sounds like you want a COUNT() with GROUP BY in your query. Doing count($id) in PHP will always yield one, as its not an array of values.

$query = "SELECT COUNT(id) as cnt, id, total_puas
          FROM table
          GROUP BY id";

$result = $connection->query($query);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $calculate = $row["total_puas"] / $row['cnt'];
        echo $row['id']." has a count of ".$row['cnt'].", a total of ".$row['total_puas']." and calculated to ".$calculate."<br />\n";
    }
}

From your updated question, the output becomes a bit more clear. Based on the output data and the result you desire, you want to calculate the sum of all total_paus, divided by the number of rows. You can do this directly in one query, like this

$query = "SELECT SUM(total_puas) / COUNT(u.id) as total
          FROM mitra AS m
          INNER JOIN user AS u
              ON mitra.id_user = user.id";

$result = $connection->query($query);
$row = $result->fetch_assoc();
$total = $row['total'];
echo $total;

Answer 3

You can try this code:

$query = "SELECT * FROM table"
$result = $connection->query($query);

if ($result->num_rows > 0) {
    $total = 0.0;
    foreach ($result as $row) :
        $id = "" . $row["id"] . "";
        $total = $total + $row['total'];
    endforeach;
    $calculate = $total / $result->num_rows;
    echo $total.<br>;
    echo $calclulate;
}