PHP: PDO (mysql)

The usual WordPress / Laravel-on-MySQL path: PDO over the MySQL protocol; creates a table, inserts and counts. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_DSN="mysql:host=old-mysql;port=3306;dbname=app;charset=utf8mb4"
DB_DSN="mysql:host=machsql-host;port=3306;dbname=app;charset=utf8mb4"

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• PHP 8.5 with the pdo_mysql extension

Run

export DB_DSN="mysql:host=machsql-host;port=3306;dbname=app;charset=utf8mb4" DB_PASSWORD=...
php main.php

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.php

<?php
// PDO with the mysql driver.
declare(strict_types=1);

$pdo = new PDO(getenv('DB_DSN'), getenv('DB_USER') ?: 'app', getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE IF NOT EXISTS todo (id BIGINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE)');
$insert = $pdo->prepare('INSERT INTO todo (title) VALUES (?)');
$insert->execute(['write the examples']);
$id = $pdo->lastInsertId();
$open = $pdo->query('SELECT count(*) FROM todo WHERE NOT done')->fetchColumn();
printf("inserted todo %d, %d open\n", $id, $open);