Group results by column in php mysql

mlucicom
Posts: 32

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
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
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
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
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
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'];

Similar Topics