CODEWITHSUNDEEP
phpmysql
Dwza
Dwza

Reputation: 6565

Select from database in groups

Im just getting back into programming, haven't for a long time... but no there is something wired and i don't know if i'm just really rusty and have something wrong or just don't get it ^^

I have a table in my DB 

id | column1 | column2 | column3
1  | aaaaaaa | 1111111 | zzzzzzz
2  | bbbbbbb | 2222222 | zzzzzzz
3  | ccccccc | 3333333 | zzzzzzz
4  | ddddddd | 4444444 | yyyyyyy
5  | eeeeeee | 5555555 | yyyyyyy
6  | fffffff | 6666666 | xxxxxxx
7  | ggggggg | 77777777| zzzzzzz

Now I want to select the whole content grouped by column3 (Actually I don't know its content).

The result I want to achieve is:

first group

1  | aaaaaaa | 1111111 | zzzzzzz
2  | bbbbbbb | 2222222 | zzzzzzz
3  | ccccccc | 3333333 | zzzzzzz

second group

4  | ddddddd | 4444444 | yyyyyyy
5  | eeeeeee | 5555555 | yyyyyyy

third group

6  | fffffff | 6666666 | xxxxxxx

is this possible with a select ? or do I have to select all and fill it into an array setting the key like

foreach($result as $res)
    $data[$res['column3']][] = $res;

I don't know why but my thoughts were that:

SELECT * FROM myTable GROUP BY column3

would do the trick but I'm obviously wrong :)

EDIT: Solution

Actually column3 is a timestamp that gets update on edit. It only will be updated once so this
timestamp only changes once. I have to select it by Day so I use it like this...

$result = $this->pdo->query("SELECT dbx_id, ordernumber, debited, timestamp FROM " . TABLE_HISTORY . " WHERE dbx_id=:dbx_id AND debited=1", array(":dbx_id" => $dbx_id), true);

foreach($result as $part){
    $data[date('d-m-Y', strtotime($part['timestamp']))][] = $part;
}

Thanks to all who tried to help

Upvotes: 0

Views: 76

Answers (3)

Dale
Dale

Reputation: 319

How about using a union? You could identify the groups within each join for example:

SELECT "Group1" AS whichGroup, * FROM myTable WHERE column3 LIKE "zzzzzzz"
UNION ALL
SELECT "Group2" AS whichGroup, * FROM myTable WHERE column3 LIKE "yyyyyyy"
UNION ALL
SELECT "Group3" AS whichGroup, * FROM myTable WHERE column3 LIKE "xxxxxxx"

Not 100% on that syntax as it's been a while since I used a union, but this would group them for your output, and allows you to do it all within MySQL, although I think it's just as easy to do the heavy lifting in PHP instead...

If I'm right, this should output something like:

whichGroup | id | column1 | column2 | column3
Group1     | 1  | aaaaaaa | 1111111 | zzzzzzz
Group1     | 2  | bbbbbbb | 2222222 | zzzzzzz
Group1     | 3  | ccccccc | 3333333 | zzzzzzz
Group2     | 4  | ddddddd | 4444444 | yyyyyyy
Group2     | 5  | eeeeeee | 5555555 | yyyyyyy
Group3     | 6  | fffffff | 6666666 | xxxxxxx

Upvotes: 0

Dan
Dan

Reputation: 11084

I like your foreach idea, and I don't see a way around it since you can't get multiple results from one query. Your best bet is to parse out the groups in code. You don't even need the GROUP BY clause. I would specify an ORDER BY but even that is not necessary.

Psuedocode:

SELECT * FROM mytable ORDER BY column3;

$aryGroups = array();
foreach($result as $row){
  if( ! isset($aryGroups[$row['column3']]) ){
    $aryGroups[$row['column3']] = array();
  }
  else{
    $aryGroups[$row['column3']][] = $row;
  }
}

I've used something like this a few times, but you may need to tweak it to get the exact results you want.

Upvotes: 2

MIvanIsten
MIvanIsten

Reputation: 490

Use your foreach idea. GROUP BY is for use with aggregate functions like COUNT, SUM, etc See manual.

Upvotes: 2

Related Questions