[lang-ref] ( example_db_access ) ( php )

<?php
	public function testExampleDbAccess(): void
	{
		// new SQLite3(':memory:')
		$db = new SQLite3(':memory:');

		// create table
		$sql = <<<SQL
CREATE TABLE IF NOT EXISTS users(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
)
SQL;
		$this->assertTrue($db->exec($sql));

		// insert
		$name = 'user1';
		$stmt = $db->prepare('INSERT INTO users(name) VALUES(:name)');
		$this->assertNotFalse($stmt);
		$stmt->bindValue(':name', $name, SQLITE3_TEXT);

		$result = $stmt->execute();
		$this->assertNotFalse($result);
		$result->finalize();

		$rowid = $db->lastInsertRowID();

		// select
		$userId = $rowid;
		$stmt = $db->prepare('SELECT * FROM users WHERE id=:id');
		$this->assertNotFalse($stmt);
		$stmt->bindValue(':id', $userId, SQLITE3_INTEGER);

		$result = $stmt->execute();
		$this->assertNotFalse($result);

		$row = $result->fetchArray(SQLITE3_ASSOC);
		$result->finalize();

		$this->assertIsArray($row);
		$this->assertSame('user1', $row['name']);

		// disconnect
		$db->close();
	}

<?php
	public function testExampleDbAccessAlternative(): void
	{
		// PDO('sqlite::memory:')
		$pdo = new PDO('sqlite::memory:');
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

		// create table
		$sql = <<<SQL
CREATE TABLE IF NOT EXISTS users(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
)
SQL;
		$pdo->exec($sql);

		// insert
		$name = 'user1';
		$stmt = $pdo->prepare('INSERT INTO users(name) VALUES(:name)');
		$this->assertNotFalse($stmt);

		$ok = $stmt->execute([
			':name' => $name,
		]);
		$this->assertTrue($ok);

		$rowId = (int) $pdo->lastInsertId();

		// select
		$userId = $rowId;
		$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
		$this->assertNotFalse($stmt);

		$ok = $stmt->execute([
			':id' => $userId,
		]);
		$this->assertTrue($ok);

		$row = $stmt->fetch();
		$this->assertIsArray($row);
		$this->assertSame('user1', $row['name']);

		// disconnect
		$pdo = null;
	}