CODEWITHSUNDEEP
phpmysqlforeachgroup-by
charless
charless

Reputation: 72

MySql 5.6 Group Then Loop Thru Each Group

So am working with a special code where I need to GROUP BY this, then echo each item in each resulting group.

SELECT * FROM `geo` GROUP BY city ORDER BY visaid DESC"

From here, I get lost. Tried using:

SELECT name, COUNT(city) FROM `geo` GROUP BY city ORDER BY visaid DESC

It does not return each, but rather the count total for each group. However, need to loop through the clustered results and display each row for each group. Example:

NEW YORK
    Brad, visaid, expired
    Gina, visaid, 1 month
    Sam, visaid, 1 week

LONDON
    Edward
    Patricia

How is this accomplished without running multiple queries?

Upvotes: 1

Views: 489

Answers (2)

charless
charless

Reputation: 72

Resolved

For anyone interested, I managed to use a single re-query to achieve the desired results.

SELECT * FROM `geo` GROUP BY city

Then a 2nd query

SELECT * FROM `geo` WHERE city='' ORDER BY visaid DESC

Upvotes: 1

sa289
sa289

Reputation: 700

Since you tagged your question as PHP, you can use PHP to help with this.

Instead of using GROUP BY, just do ORDER BY city, visaid DESC and then as you go through the results have a variable that keeps track of the last row's city and when it differs from the current row's city, echo out the city name.

Upvotes: 2

Related Questions