Usage
Usage
Quick Reference
//create table
$db->raw("CREATE TABLE demo (id int auto_increment primary key, name varchar(255))");
//use PDO directly
$db->getPdo()->query('Select username FROM users')->fetchAll();
//use run to query and chain methods
$db->run("SELECT * FROM users")->fetchAll();
$db->run("SELECT * FROM users")->fetch();
$db->run("SELECT * FROM users WHERE id = ?", [$id])->fetch();
//select using array instead of object
$db->run("SELECT * FROM users")->fetch(PDO::FETCH_ASSOC);
//get by id
$db->getById('users', 2);
//get all rows
$db->rows("SELECT title FROM posts");
//get all rows with placeholders
$db->rows("SELECT title FROM posts WHERE user_id = ?", [$user_id]);
//get single row
$db->row("SELECT title FROM posts");
//get single row with placeholders
$db->row("SELECT title FROM posts WHERE user_id = ?", [$user_id]);
//count
$db->count("SELECT id FROM posts");
$db->count("SELECT id FROM posts WHERE category_id = ?", [$category_id]);
//insert
$id = $db->insert('users', ['username' => 'Dave', 'role' => 'Admin']);
//last inserted id
$db->lastInsertId()();
//update
$db->update('users', ['role' => 'Editor'], ['id' => 3]);
//delete from table with a where claus and a limit of 1 record
$db->delete('posts', ['type_id' => 'draft'], $limit = 1);
//delete from table with a where claus and a limit of 10 record
$db->delete('posts', ['type_id' => 'draft'], $limit = 10);
//delete all from table with a where claus and a limit of 10 record
$db->delete('posts', ['type_id' => 'draft'], null);
//delete all from table
$db->deleteAll('posts');
//delete by id from table
$db->deleteById('posts', 2);
//delete by ids from table
$db->deleteById('posts', '2,4,7');
//truncate table
$eb->truncate('posts');
Insert
Data is inserted by calling the insert method it expects the table name followed by an array of key and values to insert in to the database.
$data = [
'firstName' => 'Joe',
'lastnName' => 'Smith',
'email' => 'someone@domain.com'
];
$db->insert('users', $data);
The insert automatically returns the last inserted id by returning lastInsertId to collect the id:
$id = $db->insert('users', $data);
Truncate
To empty a table of all contents call the truncate method. Passing only the table name.
$db->truncate('users');
Querying
All queries use prepared statements, calling ->run() returns a PDO option that can be chained:
Select multiple records:
$db->run("select * FROM users")->fetchAll();
Select a single record:
$db->run("select * FROM users")->fetch();
Select multiple records using ->rows
$db->rows("select * FROM table");
Select single record using ->row
$db->row("select * FROM table");
To select records based on user data instead of passing the data to the query directly use a prepared statement, this is safer and stops any attempt at sql injections.
Names placeholders
$db->row("select username FROM users WHERE id = :id and email = :email", ['id' => 1, ':email' => 'someone@domain.com']);
Anonymous placeholders
$db->row("select username FROM users WHERE id = ? and email = ?", [1, 'someone@domain.com']);
The above query will return the username from a users table where the id and email match. The id and email is passed separately in an array.
Instead of passing in an id and email to the query directly a placeholder is used :id and :email (or ? can be used) then an array is passed the keys in the array matches the placeholder and is bound, so the database will get both the query and the bound data.
Data returned from the query will be returns as an object this can be changed by passing a third param containing PDO::FETCH_ASSOC.
To use the object loop through it, a typical example:
$rows = $db->rows("firstName, lastName FROM username ORDER BY firstName, lastName");
foreach ($rows as $row) {
echo "<p>$row->firstName $row->lastName</p>";
}
Updating
To update an existing record the update method is called. This method expects the table, array of data to update, and a second array containing the where condition.
$data = [
'firstName' => 'Joe',
'lastnName' => 'Smith',
'email' => 'someone@domain.com'
];
$where = ['id' => 2];
$db->update('users', $data, $where);
Or:
$update = [
'data' => [
'firstName' => 'Joe',
'lastnName' => 'Smith',
'email' => 'someone@domain.com'
],
'where' => [
'id' => 2
]
];
$db->update('users', $update['data'], $update['where']);
Accessing PDO
You can call getPdo() to get access to PDO directly:
$db->getPdo()
This allows to chain calls:
$db->getPdo()->query($sql)->fetch();
Raw
A raw query is a query that does not run through a prepared statement and will execute the query passed directly. Useful when creating a table.
$db->raw("CREATE TABLE IF NOT EXISTS users (
id INT(11) NOT NULL AUTO_INCREMENT,
firstName VARCHAR(255) NOT NULL,
lastnName VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY (id))"
);
Delete Multiple In
To delete multiple records where ids are in a specific column, this uses WHERE id IN (4,5,6)
$db->deleteByIds('users', 'id', '4,5,6');
Delete by Id
To delete a record by its table and id
$db->deleteById('users', $id);
Count
To count records call the count method. This method expects the table name and column name (optional).
$db->count('users');
If the table has no column
$db->count('users', 'user_id');
Select Single Record
Using row() will return only a single result. Like rows it accepts params being passed in an array as a second argument.
Names placeholders
$db->row("column FROM table where id=:id", ['id' => 23]);
Anonymous placeholders
$db->row("column FROM table where id=?", [23]);
Another way to select a single record using the table and id by calling ->getById
$db->getById('users', $id);
Delete
To delete records call the delete method. This method expects the table name and an array of the where condition.
$where = ['id' => 2];
$db->delete('users', $where);
This will delete a single record to set the limit pass a third parameter containing the number to limit to or to remove the limit pass null as a third param.
$db->delete('users', $where, 10); //delete 10 records matcing the where
$db->delete('users', $where, null); //delete all records matching the where
Delete All
To delete all records for a given table
$db->deleteAll('users');