精美视频网站源码 视频源码 视频网站PHP源码 支持FFMPEG 送APP源代码

MysqliDb -- Simple MySQLi wrapper and object mapper with prepared statements <hr> ### Table of Contents **[Initialization](#initialization)** **[Objects mapping](#objects-mapping)** **[Insert Query](#insert-query)** **[Update Query](#update-query)** **[Select Query](#select-query)** **[Delete Query](#delete-query)** **[Insert Data](#insert-data)** **[Insert XML](#insert-xml)** **[Running raw SQL queries](#running-raw-sql-queries)** **[Query Keywords](#query-keywords)** **[Where Conditions](#where--having-methods)** **[Order Conditions](#ordering-method)** **[Group Conditions](#grouping-method)** **[Properties Sharing](#properties-sharing)** **[Joining Tables](#join-method)** **[Subqueries](#subqueries)** **[EXISTS / NOT EXISTS condition](#exists--not-exists-condition)** **[Has method](#has-method)** **[Helper Methods](#helper-methods)** **[Transaction Helpers](#transaction-helpers)** **[Error Helpers](#error-helpers)** **[Table Locking](#table-locking)** ## Support Me This software is developed during my free time and I will be glad if somebody will support me. Everyone's time should be valuable, so please consider donating. [Donate with paypal](https://www.paypal.com/cgi-bin/webscr?cmd=_donations&business=a%2ebutenka%40gmail%2ecom&lc=DO&item_name=mysqlidb&currency_code=USD&bn=PP%2dDonationsBF%3abtn_donateCC_LG%2egif%3aNonHosted) ### Installation To utilize this class, first import MysqliDb.php into your project, and require it. ```php require_once ('MysqliDb.php'); ``` ### Installation with composer It is also possible to install library via composer ``` composer require joshcam/mysqli-database-class:dev-master ``` ### Initialization Simple initialization with utf8 charset set by default: ```php $db = new MysqliDb ('host', 'username', 'password', 'databaseName'); ``` Advanced initialization: ```php $db = new MysqliDb (Array ( 'host' => 'host', 'username' => 'username', 'password' => 'password', 'db'=> 'databaseName', 'port' => 3306, 'prefix' => 'my_', 'charset' => 'utf8')); ``` table prefix, port and database charset params are optional. If no charset should be set charset, set it to null Also it is possible to reuse already connected mysqli object: ```php $mysqli = new mysqli ('host', 'username', 'password', 'databaseName'); $db = new MysqliDb ($mysqli); ``` If no table prefix were set during object creation its possible to set it later with a separate call: ```php $db->setPrefix ('my_'); ``` If connection to mysql will be dropped Mysqlidb will try to automatically reconnect to the database once. To disable this behavoir use ```php $db->autoReconnect = false; ``` If you need to get already created mysqliDb object from another class or function use ```php function init () { // db staying private here $db = new MysqliDb ('host', 'username', 'password', 'databaseName'); } ... function myfunc () { // obtain db object created in init () $db = MysqliDb::getInstance(); ... } ``` ### Multiple database connection If you need to connect to multiple databases use following method: ```php $db->addConnection('slave', Array ( 'host' => 'host', 'username' => 'username', 'password' => 'password', 'db'=> 'databaseName', 'port' => 3306, 'prefix' => 'my_', 'charset' => 'utf8') ); ``` To select database use connection() method ```php $users = $db->connection('slave')->get('users'); ``` ### Objects mapping dbObject.php is an object mapping library built on top of mysqliDb to provide model representation functionality. See <a href='dbObject.md'>dbObject manual for more information</a> ### Insert Query Simple example ```php $data = Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe' ); $id = $db->insert ('users', $data); if($id) echo 'user was created. Id=' . $id; ``` Insert with functions use ```php $data = Array ( 'login' => 'admin', 'active' => true, 'firstName' => 'John', 'lastName' => 'Doe', 'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")), // password = SHA1('secretpassword+salt') 'createdAt' => $db->now(), // createdAt = NOW() 'expires' => $db->now('+1Y') // expires = NOW() + interval 1 year // Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear ); $id = $db->insert ('users', $data); if ($id) echo 'user was created. Id=' . $id; else echo 'insert failed: ' . $db->getLastError(); ``` Insert with on duplicate key update ```php $data = Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe', "createdAt" => $db->now(), "updatedAt" => $db->now(), ); $updateColumns = Array ("updatedAt"); $lastInsertId = "id"; $db->onDuplicate($updateColumns, $lastInsertId); $id = $db->insert ('users', $data); ``` Insert multiple datasets at once ```php $data = Array( Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe' ), Array ("login" => "other", "firstName" => "Another", "lastName" => 'User', "password" => "very_cool_hash" ) ); $ids = $db->insertMulti('users', $data); if(!$ids) { echo 'insert failed: ' . $db->getLastError(); } else { echo 'new users inserted with following id\'s: ' . implode(', ', $ids); } ``` If all datasets only have the same keys, it can be simplified ```php $data = Array( Array ("admin", "John", "Doe"), Array ("other", "Another", "User") ); $keys = Array("login", "firstName", "lastName"); $ids = $db->insertMulti('users', $data, $keys); if(!$ids) { echo 'insert failed: ' . $db->getLastError(); } else { echo 'new users inserted with following id\'s: ' . implode(', ', $ids); } ``` ### Replace Query <a href='https://dev.mysql.com/doc/refman/5.0/en/replace.html'>Replace()</a> method implements same API as insert(); ### Update Query ```php $data = Array ( 'firstName' => 'Bobby', 'lastName' => 'Tables', 'editCount' => $db->inc(2), // editCount = editCount + 2; 'active' => $db->not() // active = !active; ); $db->where ('id', 1); if ($db->update ('users', $data)) echo $db->count . ' records were updated'; else echo 'update failed: ' . $db->getLastError(); ``` `update()` also support limit parameter: ```php $db->update ('users', $data, 10); // Gives: UPDATE users SET ... LIMIT 10 ``` ### Select Query After any select/get function calls amount or returned rows is stored in $count variable ```php $users = $db->get('users'); //contains an Array of all users $users = $db->get('users', 10); //contains an Array 10 users ``` or select with custom columns set. Functions also could be used ```php $cols = Array ("id", "name", "email"); $users = $db->get ("users", null, $cols); if ($db->count > 0) foreach ($users as $user) { print_r ($user); } ``` or select just one row ```php $db->where ("id", 1); $user = $db->getOne ("users"); echo $user['id']; $stats = $db->getOne ("users", "sum(id), count(*) as cnt"); echo "total ".$stats['cnt']. "users found"; ``` or select one column value or function result ```php $count = $db->getValue ("users", "count(*)"); echo "{$count} users found"; ``` select one column value or function result from multiple rows: ```php $logins = $db->getValue ("users", "login", null); // select login from users $logins = $db->getValue ("users", "login", 5); // select login from users limit 5 foreach ($logins as $login) echo $login; ``` ### Insert Data You can also load .CSV or .XML data into a specific table. To insert .csv data, use the following syntax: ```php $path_to_file = "/home/john/file.csv"; $db->loadData("users", $path_to_file); ``` This will load a .csv file called **file.csv** in the