postgresql Accessing Data Programmatically Accessing PostgreSQL from PHP using Pomm2
Example
On the shoulders of the low level drivers, there is pomm. It proposes a modular approach, data converters, listen/notify support, database inspector and much more.
Assuming, Pomm has been installed using composer, here is a complete example:
<?php
use PommProject\Foundation\Pomm;
$loader = require __DIR__ . '/vendor/autoload.php';
$pomm = new Pomm(['my_db' => ['dsn' => 'pgsql://user:pass@host:5432/db_name']]);
// TABLE comment (
// comment_id uuid PK, created_at timestamptz NN,
// is_moderated bool NN default false,
// content text NN CHECK (content !~ '^\s+$'), author_email text NN)
$sql = <<<SQL
SELECT
comment_id,
created_at,
is_moderated,
content,
author_email
FROM comment
INNER JOIN author USING (author_email)
WHERE
age(now(), created_at) < $*::interval
ORDER BY created_at ASC
SQL;
// the argument will be converted as it is cast in the query above
$comments = $pomm['my_db']
->getQueryManager()
->query($sql, [DateInterval::createFromDateString('1 day')]);
if ($comments->isEmpty()) {
printf("There are no new comments since yesterday.");
} else {
foreach ($comments as $comment) {
printf(
"%s has posted at %s. %s\n",
$comment['author_email'],
$comment['created_at']->format("Y-m-d H:i:s"),
$comment['is_moderated'] ? '[OK]' : '');
}
}
Pomm’s query manager module escapes query arguments to prevent SQL injection. When the arguments are cast, it also converts them from a PHP representation to valid Postgres values. The result is an iterator, it uses a cursor internally. Every row is converted on the fly, booleans to booleans, timestamps to \DateTime etc.
