Page 1 of 1
Group results by column in php mysql
Posted: 04 Apr 2017, 19:18
by mlucicom
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.
Group results by column in php mysql
Posted: 04 Apr 2017, 19:33
by 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.
Group results by column in php mysql
Posted: 04 Apr 2017, 19:53
by 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?
Group results by column in php mysql
Posted: 05 Apr 2017, 05:40
by 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;
}
}
Re: Group results by column in php mysql
Posted: 05 Apr 2017, 20:32
by 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();
?>
Group results by column in php mysql
Posted: 06 Apr 2017, 06:48
by 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".