CODEWITHSUNDEEP
phppdo
Dao Tam
Dao Tam

Reputation: 503

Fetch Group by values of the second column

The following code teaches me how to group result by the first column. But I don't know how to do it with the second column. Please explain and give me some example that make me clearly understand. Thank you so much. (Sorry about my English)

$insert = $dbh->prepare("INSERT INTO fruit(name, colour) VALUES (?, ?)");
$insert->execute(array('apple', 'green'));
$insert->execute(array('pear', 'yellow'));

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Group values by the first column */
var_dump($sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP));

And the most important is whether I can group base on the name of table field or not. (In this case I don't care the index of the column name, I just know the field name). Thanks

Upvotes: 1

Views: 2308

Answers (2)

jewelnguyen8
jewelnguyen8

Reputation: 249

$sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP, columnIndex);
$sth->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_GROUP, columnName);

I don't know it works or not. Just try it.

Upvotes: 0

Elias Van Ootegem
Elias Van Ootegem

Reputation: 76395

Erm, as the manual clearly states: the results are grouped by the values of the first column, so just change:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");

to

$sth = $dbh->prepare("SELECT colour, name FROM fruit");

cf code example you've copy pasted:

/* Group values by the >> FIRST << column */
var_dump($sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP));

Passing the column index value can indeed be worth a try, too:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$byName = $sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);
$byColour = $sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP, 1);

Upvotes: 3

Related Questions