README

You can use this small class to query your MySQL Server from back or/and frontend projects.

View on GitLab.

Forked from GitHub.

MIT License hereby included by reference and link.

Use Autoload

composer require c11k/pdo

require_once 'vendor/autoload.php';
$database = new \C11K\Pdo\Database();

Configuration

The class expects 5 environment variables which will be placed in the PDO DSN as follows:

$this->dbh = new \PDO(
    getenv('DB_PREFIX') . 'host=' . getenv('DB_HOST') . ';dbname=' . getenv('DB_NAME'),
    getenv('DB_USER'),
    getenv('DB_PASS')
);

DB_PREFIX should be one of the installed and valid PDO Drivers including the ending colon. At this time, only mysql:, sqlite:, sqlite::memory:, and pgsql: are included. If you need another driver, fork, add, and Pull Request.

If you require a port number, add the optional environment variable DB_PORT.

Usage

Insert Data
As an Array
Get the inserted ID
Transactions
Getting Data (1 row)
Getting data (several rows)
Get the row count
Update Data
Update as an Array
Delete data
Migrating from MySQLi

Insert data

$database = new \C11K\Pdo\Database();
$sql = "INSERT INTO table (name, lastname) VALUES (:name, :lastname)";
$database->prepare($sql);*/
$database->bind(':name', 'John');
$database->bind(':lastname', 'Doe');
$database->execute();

As an array

$database = new \C11K\Pdo\Database();
$sql = "INSERT INTO table (name, lastname) VALUES (:name, :lastname)";
$database->prepare($sql);
$database->bindArray([
    ':name' => 'Jane',
    ':lastname' => 'Doe'
]);
$database->execute();

Get the inserted ID

$database->lastInsertId();

Transactions

If your database engine supports transactions, simply beginTransaction() and endTransaction(). Cancel a transaction with cancelTransaction().

$database = new \C11K\Pdo\Database();
$database->beginTransaction();
  $sql = "INSERT INTO table (name, lastname) VALUES (:name, :lastname)";
  $database->prepare($sql);
  $database->bind(':name', 'John');
  $database->bind(':lastname', 'Doe');
  $database->execute();

  $database->bind(':name', 'Jane');
  $database->bind(':lastname', 'Smith');
  $database->execute();

  $database->bind(':name', 'Mary');
  $database->bind(':lastname', 'Redford');
  $database->execute();
$database->endTransaction();

Getting data (1 row)

$database = new \C11K\Pdo\Database();
$sql = "SELECT * FROM table WHERE name = :name";
$database->prepare($sql);
$database->bind(':name', 'Jenny');
$row = $database->single();     // Note no execute()
echo $row['name'];

Getting data (several rows)

$database = new \C11K\Pdo\Database();
$sql = "SELECT * FROM table WHERE lastname = :lastname";
$database->prepare($sql);
$database->bind(':lastname', 'Smith');
$rows = $database->resultSet();
foreach($rows as &$row) {
    echo $row['lastname'];
}

unset($row);

// Or, send a PDO fetch_style:
$rows = $database->resultSet(\PDO::FETCH_NUM);
foreach($rows as &$row) {
    echo $row[2];
}

unset($row);

NOTE If you loop by reference, MAKE SURE you destroy the reference with unset. See the PHP documentation for more details.

Get the row count

Returns the number of rows affected by a DELETE, INSERT, or UPDATE statement. For most databases, this does not return the number of rows affected by a SELECT statement.

`$database->rowCount();`

Update data

$id = 14;
$sql = "UPDATE table SET name = :name, lastname = :lastname WHERE id = :id";
$database->prepare($sql);
$database->bind(':id', $id);
$database->bind(':name', 'Mary');
$database->bind(':lastname', 'Jane');
$database->execute();

Update as an array

$id = 14;
$sql = "UPDATE table SET name = :name, lastname = :lastname WHERE id = :id";
$database->prepare($sql);
$database->bindArray(array(
  ':id' => $id,
  ':name' => 'Mary',
  ':lastname' => 'Jane'
));
$database->execute();

$id = 14;
$sql = "UPDATE table SET name = ?, lastname = ? WHERE id = ?";
$database->prepare($sql);
$database->bindArray(array(
  1' => $id,
  2 => 'Mary',
  3 => 'Jane'
));
$database->execute();

Delete data

$id = 14;
$sql = "DELETE FROM table WHERE id = :id";
$database->prepare($sql);
$database->bind(':id', $id);
$database->execute();

Migrating from MySQLi

You can migrate from mysqli_ commands to PDO with this class by changing the mysqli_snakecase function. Simply drop the `mysqli` part, then convert the remaining underscores to camelCase.

mysqli_fetch_assoc() converts to $database->fetchAssoc(). mysqli_real_escape_string() converts to $database->realEscapeString(). Please note that mysqli_query($sql) may convert to $database->rawQuery($sql), and it sends the query straight to the database without the PDO binding and preparation. This is for an intermediary step in migrating an entire set of code from mysqli_ to PDO instead of migrating in one fell swoop.

You have been warned, but this is the way that the mysqli_query() works, anyway, so only use this as a migration step, and make sure to come back to your code and finish converting to PDO. Nothing will stop you from skipping the intermediary step, though. It simply depends on the size of the work to keep your existing project working.

First, add this class to your code as an autoload. If you're using composer, you should already have this autoloaded.
Add your environment variables as required in Configuration.
Add $database = new $database = new \C11K\Pdo\Database(); to your setup scripts.
Replace mysqli_ with $database-> and convert underscores to camelCase.
ie Replace mysqli_query() with $database->rawQuery(). Replace mysqli_fetch_assoc() with $database->fetchAssoc().
Then, go back through your code and replace rawQuery() with query() or prepare(), convert your parameters to named parameters, bind() or bindArray() your parameters, and execute.

From:

$sql = "SELECT * FROM users WHERE email = " . $email;
$result = mysqli_query($sql);
while ($row = mysqli_fetch_assoc($result)) {
    // Do stuff
}

mysqli_free_result($results)

To:

$sql = "SELECT * FROM users WHERE email = :email";
$database->prepare($sql);
$database->bind(":email", $email);
$database->execute();
while ($row = $database->fetchAssoc()) {
    // Do Stuff
}

$database->freeResult($results);

c11k / pdo

Maintainers

Details