davidlienhard/database

🐘 php library for easy access to databases

Setup

You can install through composer with:

composer require davidlienhard/database:^3

Note: davidlienhard/database requires PHP 8.4

Examples

Connect to the Database-Server

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Exceptions\ExceptionasDatabaseException;
useDavidLienhard\Database\Mysqli;
try {
$db = newMysqli;
$db->connect("hostname", "username", "password", "dbname");
} catch (DatabaseException$e) {
echo"unable to connect to the database host";
exit(1);
}

Fetch Results as Objects (Recommended)

Fetching Multiple Rows as Objects

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Mysqli;
$userResult = $db->query(
"SELECT `userID`, `userName`, `userLevel` FROM `user`"
);
while ($userData = $userResult->fetch_object()) {
echo$userData->get('userID').": ".$userData->get('userName').PHP_EOL;
}

Fetching a Single Row as Object

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Exceptions\NoRowsException;
useDavidLienhard\Database\Mysqli;
try {
$userResult = $db->query(
"SELECT `userID`, `userName`, `userLevel` FROM `user` WHERE `userID` = ?",
newDBParam("i", $userId)
);
$user = $userResult->fetch_single_object();
echo$user->get('userName');
} catch (NoRowsException$e) {
echo"user not found";
}

Fetch Results as Arrays

Fetching Multiple Rows as Associative Arrays

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Mysqli;
$userResult = $db->query(
"SELECT `userID`, `userName` FROM `user`"
);
while ($userData = $userResult->fetch_array_assoc()) {
echo$userData['userID'].": ".$userData['userName'].PHP_EOL;
}

Fetching a Single Row as Associative Array

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Exceptions\NoRowsException;
useDavidLienhard\Database\Mysqli;
try {
$userResult = $db->query(
"SELECT `userID`, `userName` FROM `user` WHERE `userID` = ?",
newDBParam("i", $userId)
);
$user = $userResult->fetch_single_array_assoc();
echo$user['userName'];
} catch (NoRowsException$e) {
echo"user not found";
}

Select Query with Parameters

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Mysqli;
useDavidLienhard\Database\ParameterasDBParam;
$userResult = $db->query(
"SELECT `userID`, `userName` FROM `user` WHERE `userLevel` = ? and `userType` = ?",
newDBParam("i", $userLevel),
newDBParam("s", $userType)
);
while ($user = $userResult->fetch_object()) {
echo$user->get('userID').": ".$user->get('userName').PHP_EOL;
}

Insert Query

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Exceptions\ExceptionasDatabaseException;
useDavidLienhard\Database\Mysqli;
useDavidLienhard\Database\ParameterasDBParam;
try {
$db->query(
"INSERT INTO `user` SET `userName` = ?, `userLevel` = ?, `userType` = ?",
newDBParam("s", $userName),
newDBParam("i", $userLevel),
newDBParam("s", $userType)
);
} catch (DatabaseException$e) {
echo"unable to insert into table";
exit(1);
}

Update Query

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Exceptions\ExceptionasDatabaseException;
useDavidLienhard\Database\Mysqli;
useDavidLienhard\Database\ParameterasDBParam;
try {
$db->query(
"UPDATE `user` SET `userName` = ?, `userLevel` = ? WHERE `userID` = ?",
newDBParam("s", $userName),
newDBParam("i", $userLevel),
newDBParam("i", $userId)
);
} catch (DatabaseException$e) {
echo"unable to update table";
exit(1);
}

Transactions

<?phpdeclare(strict_types=1);
useDavidLienhard\Database\Mysqli;
useDavidLienhard\Database\ParameterasDBParam;
try {
$db->begin_transaction();
$db->query(
"INSERT INTO `user` SET `userName` = ?",
newDBParam("s", $userName)
);
$db->query(
"INSERT INTO `user_log` SET `action` = ?",
newDBParam("s", "user_created")
);
$db->commit();
} catch (Exception$e) {
$db->rollback();
echo"transaction failed";
}

API Overview

Fetch Methods

• fetch_object(ResultType $resultType = assoc): RowInterface|null - Returns a RowInterface object that wraps the row data. Use $row->get('columnName') or $row->getAll() to access data.
• fetch_single_object(ResultType $resultType = assoc): RowInterface - Like fetch_object() but throws NoRowsException if no rows are available.
• fetch_array_assoc(): array|null - Returns an associative array with column names as keys.
• fetch_single_array_assoc(): array - Like fetch_array_assoc() but throws NoRowsException if no rows are available.
• fetch_array_num(): array|null - Returns an enumerated array with numeric indices.
• fetch_single_array_num(): array - Like fetch_array_num() but throws NoRowsException if no rows are available.
• fetch_array(ResultType $resultType): array|null - Generic fetch returning either associative or numeric arrays based on $resultType.

Deprecated Methods

The following methods are deprecated and should not be used in new code:

• fetch_assoc() - Use fetch_array_assoc() instead
• fetch_row() - Use fetch_array_num() instead

License

The MIT License (MIT). Please see LICENSE for more information.