EasyDb is a simple wrapper over standart myslqi extension.
What does this library offer you?
- Use placehoders with different types to pass data into query safely.
- Select one cell or all rows with only one method.
- Insert and update rows by passing column-value arrays.
- Use transactions with only one method.
- Executes multiple queries to load your sql dumps.
- Peforms multiple inserts and
insert... on duplicate update
queries without ton of code.
Use composer to install library.
composer require silverslice/easydb
use Silverslice\EasyDb\Database;
// connect options
$options = [
'host' => 'localhost',
'username' => 'root',
'password' => '',
'dbname' => 'testdb',
'charset' => 'utf8'
];
// mysql options (not required)
$mysqlOptions = [MYSQLI_INIT_COMMAND => 'SET AUTOCOMMIT = 0'];
$db = new Database($options, $mysqlOptions);
EasyDb uses placeholders to place data in the query.
?
- smart
In most cases you can use this placeholder. It automatically detects your value and correctly formats it.
- Integer value will be inserted as is.
- Null value will be inserted as NULL keyword.
- Database expressions will be inserted as is.
- In other cases value will be inserted as string.
?i
- integer
Value will be cast to int with intval function.
?s
- string
Value will be escaped with mysqli internal escape function and will be wrapped with single quote.
?f
- float
Value will be cast to float with floatval function.
?a
- array
This placeholder expects array, correctly escapes and quotes its values, and separates them by comma.
It is useful in queries like SELECT * FROM table WHERE id IN (1, 2, 3)
$db->getAll('SELECT * FROM table WHERE id IN (?a)', [1, 2, 3]);
// generated sql: SELECT * FROM table WHERE id IN (1, 2, 3)
?u
- set
This placeholder expects column-value array and generates code for SET clouse
$db->query('UPDATE products SET ?u', ['key' => 'foo', 'value' => 1]);
// generated sql: UPDATE products SET `key` = 'foo', `value` = 1
?e
- escape
Value will be escaped with mysqli internal escape function.
?p
- part
Value will be inserted as is. Use this placeholder to insert already prepared part of sql query.
If your query depends on some conditions you can use prepare
method to replace all placeholders in string with passed parameters.
This string may be inserted into query safely.
$pricePart = '';
if ($price) {
$pricePart = $db->prepare(' AND price < ?', $price);
}
$db->getAll('SELECT * FROM products WHERE category_id = ? ?p', $categoryId, $pricePart);
EasyDb converts all database errors to \Silverslice\EasyDb\Exception
. Use getQuery
method to get the query which caused an error.
Example:
try {
$this->db->query("SELECTT 1");
} catch (Exception $e) {
// get error code
$code = $e->getCode();
// get query with error
$query = $e->getQuery();
// get error message: code, error and query
$message = $e->getMessage();
}
Peforms query and fetchs first cell from the first result row
Parameters:
- string $query - The SQL query
- mixed $param1 - Parameter for the first placeholder
- mixed $param2 - Parameter for the second placeholder
- ...
getOne($query, $param1, $param2...)
Returns:
- string|null
Peforms query and fetchs first result row as an associative array
getAssoc($query, $param1, $param2...)
Returns:
- array|null
Peforms query and fetchs all result rows as an associative array
getAll($query, $param1, $param2...)
Returns:
- array
Peforms query and fetchs one column from the result set as an enumerate array
getColumn($query, $param1, $param2...)
Returns:
- array
Peforms query and fetchs key-value pairs from the result set.
getPairs($query, $param1, $param2...)
Returns:
- array
Peforms query and fetchs key-values pairs from the result set.
getAllKeyed($query, $param1, $param2...)
Returns:
- array|bool
Inserts row into table
insert($table, $params, $ignore = false)
Parameters:
- string $table - Table name
- array $params - Column-value pairs
- bool $ignore - Use or not IGNORE keyword
Returns:
- mixed - Inserted row id or true if table hasn't autoincrement field
Updates table rows
update($table, $params, $where = array())
Parameters:
- string $table - Table name
- array $params - Column-value pairs
- array $where - UPDATE WHERE clause(s). Several conditions will be concatenated with AND keyword
Returns:
- int - The number of affected rows
Inserts or updates table row using INSERT... ON DUPLICATE KEY UPDATE clause
insertUpdate($table, $insert, $update = array())
Parameters:
- string $table - Table name
- array $insert - Column-value pairs to insert
- array $update - Column-value pairs to update if key already exists in table
Returns:
- int - The number of affected rows: 1 if row was inserted or 2 if row was updated
Inserts multiple rows into table
multiInsert($table, $fields, $data, $ignore = false)
Parameters:
- string $table - Table name
- array $fields - Field names
- array $data - Two-dimensional array with data to insert
- bool $ignore - Use or not IGNORE keyword
Returns:
- int - The number of affected rows
Deletes table rows
delete($table, $where = array())
Parameters:
- string $table - Table name
- array $where - UPDATE WHERE clause(s). Several conditions will be concatenated with AND keyword
Returns:
- int - The number of affected rows
Starts a transaction
beginTransaction()
Commits the current transaction
commit()
Rolls back current transaction
rollback()
Runs code in transaction
transaction($process)
Parameters:
- callable $process - Callback to process
Returns:
- bool - True if transaction was successful commited, false otherwise
Throws:
- \Silverslice\EasyDb\Exception
Example:
$this->db->transaction(function () {
$this->db->query("INSERT INTO test (id, code, price) VALUES (3003, '1', 1)");
$this->db->query("INSERT INTO test (id, code, price) VALUES (3004, '1', 1)");
});
Columns in SQL queries are sometimes expressions, not simply column names from a table. You can create an object of type Expression to insert expression into sql.
$this->db->insert('test', [
'time' => new Expression('NOW()')
]);
// or use expression alias
$this->db->insert('test', [
'time' => $this->db->expression('NOW()')
]);
Use multiQuery
method to execute several queries which are concatenated by a semicolon, for example to load sql dump from file.
Executes one or multiple queries which are concatenated by a semicolon
multiQuery($queries)
Parameters:
- string $queries
Returns:
- bool
Throws:
- \Silverslice\EasyDb\Exception
Example:
$this->db->multiQuery("
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`code` char(15) NOT NULL,
`name` varchar(200) NOT NULL,
`price` decimal(10,2) unsigned DEFAULT NULL,
`order` int(11) unsigned DEFAULT 0,
PRIMARY KEY (`id`),
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test (id, code, name, price)
VALUES
(1, '001', 'Cup', 20.00),
(2, '002', 'Plate', 30.50)
");