Raw SQL and result sets

Most of the time, query files are the best fit. Raw SQL is still available when we genuinely need it.

executeSql()

$result = $db->executeSql("select count(*) as total from user");

This is useful for one-off statements, administrative commands, or cases where creating a named query file would not improve clarity.

If we have multiple connections, the third argument is the connection name:

$result = $db->executeSql(
	"select count(id) as total from user_page",
	[],
	"analytics"
);

Working with ResultSet

The result object is consistent across both named queries and raw SQL:

• fetch():?Row returns one Row or null.
• fetchAll():ResultSet<Row> returns an iterable ResultSet of rows.
• affectedRows():int returns the changed row count.
• lastInsertId():string returns the inserted id as a string.
• asArray():array<string, bool|int|float|string> returns plain arrays.
• ResultSet is iterable and countable.

$resultSet = $db->fetchAll("user/getAllActive");

foreach($resultSet as $row) {
	echo $row->getString("email"), PHP_EOL;
}

Multi-statement SQL files

One query file may contain more than one SQL statement, separated by semicolons.

That means we can keep a tightly related set of setup statements in one place when it improves readability. The statements are split and executed in order.

Note

For schema changes over time, migrations are still the better tool. Multi-statement query files are for query execution; migrations are for controlled schema history.

---

Next, move on to Type-safe getters to see the small helpers that turn common single-column queries into clean PHP values.