Елин Й. Елин Й. - 1 month ago 13
MySQL Question

Fetch all rows grouped by a specific column using PDO and selecting all columns

I want to use PDO's

fetchAll()
method with
\PDO::FETCH_GROUP
option.

However
\PDO::FETCH_GROUP
uses the first column as key to group the results. The query I have selects all fields like:

SELECT * FROM `my_table`;


The table
my_table
has quite a few fields and it is very possible that new fields to be added in the future. I don't like the idea to specify all fields in the query one by one.

The table looks like that:

| my_table |
+----------+
| id |
| type |
| a_field |
| b_field |
| ... |


Is there any possibility to use
SELECT * FROM my_table
and group by the
type
field?

I've tried the following query but it threw an exception:

$sql = "SELECT type, * FROM my_table";
$result = $pdo->query($sql)->fetchAll(\PDO::FETCH_GROUP);


Exception:


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM
my_table
WHERE type IN ('typeA', 'typeB', 'typeC')' at line 1' in MyClass.php on line XXX

Answer

Just perpend the asterisk with the table name:

$sql = "SELECT type, my_table.* FROM my_table";
$result = $pdo->query($sql)->fetchAll(\PDO::FETCH_GROUP);
Comments