Wallogit.com
2017 © Pedro Peláez
Easy to use mysqli wrapper
, (*1)
EasyDb is a simple wrapper over standart myslqi extension., (*2)
What does this library offer you?, (*3)
insert... on duplicate update queries without ton of code.Use composer to install library., (*4)
composer require silverslice/easydb, (*5)
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., (*6)
? - smart, (*7)
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., (*8)
?i - integer, (*9)
Value will be cast to int with intval function., (*10)
?s - string, (*11)
Value will be escaped with mysqli internal escape function and will be wrapped with single quote., (*12)
?f - float, (*13)
Value will be cast to float with floatval function., (*14)
?a - array, (*15)
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), (*16)
$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, (*17)
This placeholder expects column-value array and generates code for SET clouse, (*18)
$db->query('UPDATE products SET ?u', ['key' => 'foo', 'value' => 1]);
// generated sql: UPDATE products SET `key` = 'foo', `value` = 1
?e - escape, (*19)
Value will be escaped with mysqli internal escape function., (*20)
?p - part, (*21)
Value will be inserted as is. Use this placeholder to insert already prepared part of sql query., (*22)
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., (*23)
$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., (*24)
Example:, (*25)
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, (*26)
Parameters: - string $query - The SQL query - mixed $param1 - Parameter for the first placeholder - mixed $param2 - Parameter for the second placeholder - ..., (*27)
getOne($query, $param1, $param2...)
Returns: - string|null, (*28)
Peforms query and fetchs first result row as an associative array, (*29)
getAssoc($query, $param1, $param2...)
Returns: - array|null, (*30)
Peforms query and fetchs all result rows as an associative array, (*31)
getAll($query, $param1, $param2...)
Returns: - array, (*32)
Peforms query and fetchs one column from the result set as an enumerate array, (*33)
getColumn($query, $param1, $param2...)
Returns: - array, (*34)
Peforms query and fetchs key-value pairs from the result set., (*35)
getPairs($query, $param1, $param2...)
Returns: - array, (*36)
Peforms query and fetchs key-values pairs from the result set., (*37)
getAllKeyed($query, $param1, $param2...)
Returns: - array|bool, (*38)
Inserts row into table, (*39)
insert($table, $params, $ignore = false)
Parameters: - string $table - Table name - array $params - Column-value pairs - bool $ignore - Use or not IGNORE keyword, (*40)
Returns: - mixed - Inserted row id or true if table hasn't autoincrement field, (*41)
Updates table rows, (*42)
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, (*43)
Returns: - int - The number of affected rows, (*44)
Inserts or updates table row using INSERT... ON DUPLICATE KEY UPDATE clause, (*45)
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, (*46)
Returns: - int - The number of affected rows: 1 if row was inserted or 2 if row was updated, (*47)
Inserts multiple rows into table, (*48)
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, (*49)
Returns: - int - The number of affected rows, (*50)
Deletes table rows, (*51)
delete($table, $where = array())
Parameters: - string $table - Table name - array $where - UPDATE WHERE clause(s). Several conditions will be concatenated with AND keyword, (*52)
Returns: - int - The number of affected rows, (*53)
Starts a transaction, (*54)
beginTransaction()
Commits the current transaction, (*55)
commit()
Rolls back current transaction, (*56)
rollback()
Runs code in transaction, (*57)
transaction($process)
Parameters: - callable $process - Callback to process, (*58)
Returns: - bool - True if transaction was successful commited, false otherwise, (*59)
Throws:, (*60)
Example:, (*61)
$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., (*62)
$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., (*63)
Executes one or multiple queries which are concatenated by a semicolon, (*64)
multiQuery($queries)
Parameters: - string $queries, (*65)
Returns: - bool, (*66)
Throws:, (*67)
Example:, (*68)
$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)
");
Easy to use mysqli wrapper
MIT
database mysql mysqli db
Easy to use mysqli wrapper
MIT
database mysql mysqli db
Easy to use mysqli wrapper
MIT
database mysql mysqli db