PHP MYSQL中的SUM和循环

Question

Well, I'm currently working on SUM and Looping array in php.

Here's my php code:

<?php

require_once 'koneksi.php';

if(isset($_POST['id_gejala'])) {
    
    $val = $_POST['id_gejala'];
    
    for($i="0"; $i < sizeof($val); $i++) {
        
        $seldata = mysqli_query($con, "SELECT a.`kode_evidence`, a.`kode_hipotesa`, a.`nilai_evidence`, b.`probabilitas`, SUM(a.`nilai_evidence`) AS `total_evidence` FROM `evidence` AS a INNER JOIN hipotesa AS b ON a.`kode_hipotesa` = b.`kode_hipotesa` WHERE `kode_evidence` ='".$val[$i]."' GROUP BY a.`kode_hipotesa` HAVING COUNT(*) >= 1 ") or die (mysqli_error($con));
        
        while($data1=mysqli_fetch_assoc($seldata)){
            $data[]=$data1;
        }
    }
}

$json = json_encode($data);
echo $json;

?>

I've successfully generated the json response when I echo $json, here's the response I get:

[
{
"kode_evidence":"1",
"kode_hipotesa":"1",
"nilai_evidence":"0.9",
"probabilitas":"0.09",
"total_evidence":"0.8999999761581421"
},
{
"kode_evidence":"2",
"kode_hipotesa":"1",
"nilai_evidence":"0.4",
"probabilitas":"0.09",
"total_evidence":"0.4000000059604645"
}
]

In my php code, the GROUP BY and HAVING COUNT command in sql variable are not working, I still get 2 responses and the result won't do the sum of nilai_evidence.

I need to get only 1 result from post and the do the SUM calculation of the query.

Any help will be much appreciated, thank you.

</div>

Answer 1

You are using aggregation function only on a part of the column that you set in group by ..

this could retrive unrelated value for this column respctec the value for ggregated function

Could be that for your query the valeue of the column in these column is the same
when you could use (fake ) aggregation function for avoid the return of more that one row

  "SELECT 
          min(a.`kode_evidence`)
        , a.`kode_hipotesa`
        , min(a.`nilai_evidence`)
        , min(b.`probabilitas`)
        , SUM(a.`nilai_evidence`) AS `total_evidence` 
  FROM `evidence` AS a 
  INNER JOIN hipotesa AS b ON a.`kode_hipotesa` = b.`kode_hipotesa` 
  WHERE `kode_evidence` ='".$val[$i]."' 
  GROUP BY a.`kode_hipotesa` 
  HAVING COUNT(*) >= 1 ") or die (mysqli_error($con));