Group results by column in php mysql

Discuss coding issues, and scripts related to PHP and MySQL.
mlucicom
Posts: 37

Group results by column in php mysql

Have you any ideea why this sql select return me only one value?

Code: Select all

SELECT  `location`.`id` , `location`.`location` , `location`.`customer` , `area`.`id` ,  GROUP_CONCAT(area.id ORDER BY area.id SEPARATOR ', ')
 FROM `location` , `area` 
where `location`.`id`= `area`.`id_principal`
in database i have multiple rows with this condition.

- if i delete group concat i get all results, but i need this in one results.
Admin Posts: 805
Hello,
What value is it returned, and what value do you want to get?

- If the result you want to get is not possible directly from sql query, you can arrange the results in php.
mlucicom Posts: 37
for example i have this code:

Code: Select all

$sql = " SELECT `members`.`username` , `members`.`ID` , `acces1`.`username` , `acces1`.`location_id` , `location`.`id`, `location`.`location` , `location`.`customer` 
FROM `members` , `acces1` , `location`
where `members`.`ID`=`acces1`.`username` AND `location`.`id`=`acces1`.`location_id`";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
   $location = $row['location'];
   $location_id = $row['id'];
    $q=$conn->query("SELECT  area,id,id_principal FROM area  WHERE id_principal ='" . $location_id . "' " );

           foreach($q as $cat){
           	$area_id = $cat['id'];
           	$area = $cat['area'];

 $linkq=$conn->query("SELECT description FROM area_description WHERE id_area ='" . $cat['id'] . "' "); 
  foreach($linkq as $link){ 
  	$descriere = $link['description'];
  }
 echo 'location : '.$location.' <li>
  		<ul>area: '.$area.'</ul>
  		<ul>description '.$descriere.'</ul>
  		</li><br/>';
    }
    
}
and i get this results

Code: Select all

location : Bulevardul Ecaterina
area: Centru
description hgtvhgghgghvbhghgffhcvhh

location : VeryCreative
area: Bulevardul Ecaterina Teodoroiu
description hgtvhgghgghvbhghgffhcvhh

location : VeryCreative
area: ce
description hgtvhgghgghvbhghgffhcvhh

location : VeryCreative
area: Centru Targu Jiu
description Este o locatie frumoasa si foarte atragatoare

location : VeryCreative
area: Bacau
description asjdads

location : VeryCreative
area: Cluj
description asjdads

location : VeryCreative
area: Cluj Napoca
description Aici este situat datacentrul MXHOST

location : VeryCreative
area: Mall
description Proprietate pentru mall
how can i group all results with location and display this into a list?
Admin Posts: 805
Maybe it works with group_concat if you add also GROUP BY.
Try this code:

Code: Select all

SELECT  `location`.`id` , `location`.`location` , `location`.`customer` , `area`.`id` ,  GROUP_CONCAT(area.id ORDER BY area.id SEPARATOR ', ')
 FROM `location` , `area`
where `location`.`id`= `area`.`id_principal`
GROUP BY `location`.`location`
If it not works, make an array in php where you store data by column value. Something like this:

Code: Select all

//define the array before sql query
$arr =[];
//perform the sql..
//..then, in where() instruction add data in $arr, by column
where(...){
  if(!isset($arr[$location])) $arr[$location]=[];
  $arr[$location]['area']=$area;
  $arr[$location]['description']=$description;
}
//test the $arr
var_export($arr);

//then, traverse the $arr
foreach($arr as $k=>$v){
  echo '<hr>'. $k;
  foreach($v as $k2>$v2){
    echo '<br>'. $k2 .': '. $v2;
  }
}
mlucicom Posts: 37
Have you any ideea why don't work this code?

Code: Select all

<?php

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT  `location`.`id` , `location`.`location` , `location`.`customer` , `area`.`id` ,  GROUP_CONCAT(area.id ORDER BY area.id SEPARATOR ', ')
 FROM `location` , `area`
where `location`.`id`= `area`.`id_principal`
GROUP BY `location`.`location` ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
      $var = $row['GROUP_CONCAT(area.id ORDER BY area.id SEPARATOR ', ')'];
      echo $var;
      
    }
} else {
    echo "0 results";
}
$conn->close();
?>
Admin Posts: 805
I cannot test the code because i not have those mysql tables, but i can advice you to add an alias to GROUP_CONCAT:

Code: Select all

$sql ="... GROUP_CONCAT(area.id ORDER BY area.id SEPARATOR ', ') AS areaid ...";
Then, in php use the added alias "areaid".

Code: Select all

$var = $row['areaid'];