CODEWITHSUNDEEP
phpmysqlsql
xitas
xitas

Reputation: 1164

MYSQL GROUP BY RECORD

I am beginner and I am stuck over a problem, I have a table in which I have a large data about restaurants, I have some restaurants that have multiple branches, so I want to display them like below as you can see I have three entries in my table for restaurant C, and i want to display them under a single name.

Restaurant A:
  123 B town
Restaurant B:
  123 C town
Restaurant C:
  1 A Town
  2 B Town 
  3 C Town

Problem: I have made a query, but it only displays a single entry for each restaurant even if there are multiple records against the restaurant.

SELECT * FROM Restaurants
GROUP BY NAME

Upvotes: 0

Views: 99

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Sample Data

resturant       |   branch
-----------------------------
Restaurant A    |   123 B town
Restaurant B    |   123 C town
Restaurant C    |   1 A Town
Restaurant C    |   2 B Town 
Restaurant C    |   3 C Town

Query

SELECT 
    resturant,
    GROUP_CONCAT(branch)
FROM Restaurants
GROUP BY NAME

Result

resturant       |   branch
-----------------------------
Restaurant A    |   123 B town
Restaurant B    |   123 C town
Restaurant C    |   1 A Town,2 B Town,3 C Town

Use php explode() on branch

Upvotes: 2

Phliplip
Phliplip

Reputation: 3632

I would:

SELECT * FROM Restaurants
ORDER BY NAME

And in your php script - please remind that I'm not aware of your table naming, so this is more or less pseudocode.

$prevGroup = false;
// How ever you normally iterate through your rows
foreach($rows as $row) {
    $group = $row['name'];
    if($prevGroup != $group) {
        echo $row['name'];
        $prevGroup = $group;
    }
    echo $row['city'];
}

Upvotes: 1

DhruvPathak
DhruvPathak

Reputation: 43265

You would not want to group them by name, as that grouping will show only one row per group. fetch all the rows, and then do the grouping in PHP using an associative array.

Or use GROUP_CONCAT , that would give you a comma seperated list of towns in a column.

eg.

SELECT ame,some_id,GROUP_CONCAT(branch_town) from my_table GROUP BY NAME

Upvotes: 1

Related Questions