jpi/database

Simple extension to PDO

v2.0.0 2024-04-15 22:24 UTC

Requires

• php: ^7.1 || ^8.0
• ext-pdo: *

Requires (Dev)

• jpi/codestyles: ^1.0

GPL-3.0-only

• Jahidul Pabel Islam

• database
• db
• mysql
• pdo

README

Simple extension to PDO with some extra convenient methods including simplified parameter binding and easy data fetching. Fully compatible with standard PDO usage, making it easy to use as a drop-in replacement.

Installation

Use Composer

$ composer require jpi/database 

Usage

Initialisation

First, create an instance of the Database class by providing PDO connection parameters:

$connection = new \JPI\Database(
    "mysql:host=localhost;dbname=your_database",
    "username",
    "password"
);

Available Methods

Extra Methods:

• prep(string, array): PDOStatement: when you want to bind some parameters to a query
• run(string, array): PDOStatement: when you bind some parameters to a query and want to execute it
• selectAll(string, array): array: for a SELECT query, returns a multidimensional array of all the rows found
• selectFirst(string, array): array|null: for a SELECT query that has LIMIT 1, returns an associative array of the first row found (if any)
• getLastInsertedId(): int|null: helpful after an INSERT query, returns the ID of the newly inserted row

Overridden Methods:

• exec(string, array): int: for INSERT, UPDATE and DELETE queries, returns the number of rows affected

All methods except getLastInsertedId take the query as the first parameter (required), and an array of params to bind to the query (optional).

Examples:

(Assuming instance has been created and set to a variable named $connection)

prep:

// Prepare a statement with bound parameters (without executing)
$statement = $connection->prep(
    "SELECT * FROM users WHERE email = :email;",
    ["email" => "[e-mail]"]
);

// You can now execute it later
$statement->execute();

run:

// Prepare and execute a query in one step
$statement = $connection->run(
    "SELECT * FROM users WHERE email = :email;",
    ["email" => "[e-mail]"]
);

// Fetch results from the statement
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

selectAll:

$rows = $connection->selectAll("SELECT * FROM users;");

/**
$rows = [
    [
        "id" => 1,
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[e-mail]",
        "password" => "password123",
        ...
    ],
    [
        "id" => 2,
        "first_name" => "Test",
        "last_name" => "Example",
        "email" => "[e-mail]",
        "password" => "password123",
        ...
    ],
    ...
];
*/

selectFirst:

$row = $connection->selectFirst("SELECT * FROM users LIMIT 1;");

/**
$row = [
    "id" => 1,
    "first_name" => "Jahidul",
    "last_name" => "Islam",
    "email" => "[e-mail]",
    "password" => "password",
    ...
];
*/

exec:

// INSERT
$numberOfRowsAffected = $connection->exec(
    "INSERT INTO users (first_name, last_name, email, password) VALUES (:first_name, :last_name, :email, :password);",
    [
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[e-mail]",
        "password" => "password",
    ]
);

// UPDATE
$numberOfRowsAffected = $connection->exec(
    "UPDATE users SET first_name = :first_name WHERE id = :id;",
    [
        "id" => 1,
        "first_name" => "Pabel",
    ]
);

// DELETE
$numberOfRowsAffected = $connection->exec("DELETE FROM users WHERE id = :id;", ["id" => 1]);

getLastInsertedId:

// INSERT a new user
$connection->exec(
    "INSERT INTO users (first_name, last_name, email, password) VALUES (:first_name, :last_name, :email, :password);",
    [
        "first_name" => "Jahidul",
        "last_name" => "Islam",
        "email" => "[e-mail]",
        "password" => "password",
    ]
);

// Get the ID of the newly inserted row
$newRowId = $connection->getLastInsertedId();
// $newUserId = 3

Support

If you found this library interesting or useful please spread the word about this library: share on your socials, star on GitHub, etc.

If you find any issues or have any feature requests, you can open a issue or email me @ jahidulpabelislam.com 😏.

Authors

• Jahidul Pabel Islam [e-mail]

Licence

This module is licensed under the General Public Licence - see the licence file for details.