CODEWITHSUNDEEP
phpmysqlpdo
Elin Y.
Elin Y.

Reputation: 1173

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

Upvotes: 2

Views: 1696

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157839

Just prepend the asterisk with the table name:

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

Upvotes: 4

Related Questions