对循环中的SELECT语句的结果进行分组

Question

I'm trying to design a system that can diagnose a user of a disease depending on what the user keys in. I used jquery to create dynamic textboxes in which the user keys in the symptoms.This is stored in array and passed to the php file to run a query for results.

<?php
$symp=$_POST['fields'];
foreach ($symp as $Values) {
$qry=   "SELECT D.name, group_concat( symp SEPARATOR ',' ) as conca, count(*) as SymptomsMatching from linkds DS JOIN symptoms S on DS.symptomId = S.id JOIN disease D on DS.diseaseId = D.id WHERE  S.symp  IN ('$Values') GROUP BY D.name ORDER BY count(*) DESC, D.name ";
$rep=mysqli_query($con,$qry);
  foreach ($rep as $row) {
  echo"<tr>
  <td class='text-left'>".$row['name']."</td>
  <td id='title'class='text-left'>".$row['conca']."</td>
  <td class='text-left'>".$row['SymptomsMatching']."</td>
  </tr>";
    }
 }

That is the code in the php script..At the moment i'm get results but its not grouping by the disease name.

Disease     |       Symptoms        | No of Symptoms
 Malaria    |     fever             |    1
Diarrhea    |     fever             |    1
Malaria     |     nausea            |    1
Diarrhea    |     nausea            |    1
Malaria     |     headache          |    1

This the result am getting at the moment.. But the table below is what i expect:

Disease     |            Symptoms                   | No of Symptoms
 Malaria    |     fever,nausea,headache             |    3
Diarrhea    |     fever,nausea                      |    2

When i run the same query in phpmyadmin i get the right table... i dont know if the loop in the php script is causing it.. Can someone please help me out.

Answer 1

The array_walk function will help you format the $values correctly, so each value is enclosed in "single quotes" and MYSQL WHERE IN () will process correctly.

<?php
$symp=$_POST['fields'];

array_walk($symp, function(&$v,$k){
      $v = "'$v'";
 });

$values=implode(",",$symp);

$qry = "SELECT D.name, group_concat( symp SEPARATOR ',' ) as conca, count(*) as SymptomsMatching from linkds DS 
   JOIN symptoms S on DS.symptomId = S.id 
   JOIN disease D on DS.diseaseId = D.id 
   WHERE  S.symp  IN ($values) 
   GROUP BY D.name 
   ORDER BY SymptomsMatching DESC, D.name ASC";

$rep=mysqli_query($con,$qry);
if($rep){
    foreach ($rep as $row) {
        echo"<tr>
          <td class='text-left'>".$row['name']."</td>
          <td id='title'class='text-left'>".$row['conca']."</td>
          <td class='text-left'>".$row['SymptomsMatching']."</td>
          </tr>";
    }
}
?>