PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(增查改删)

为什么80%的码农都做不了架构师？>>>   

PHP MySQLi:

http://php.net/mysqli

<?phpfunction db() {global $app;static $db; //1个请求内多个函数共用1个连接.if ($db) {return $db;} else {$db = @new mysqli($app['db_host'], $app['db_user'], $app['db_pass'], $app['db_name'], $app['db_port']);}if ($db->connect_errno) {echo $db->connect_error;exit();}$db->set_charset('utf8');return $db; }function insert($title = '', $content = '') {global $app;$db = db();$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');$stmt->bind_param('ss', $title, $content);$stmt->execute();//插入失败,或者没有AUTO_INCREMENT字段,或者不是INSERT语句,insert_id为0.return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? array(true, 'insert_id' => $stmt->insert_id) : array(false, 'insert_id' => $stmt->insert_id); }function select($id = '') {global $app;$db = db();if (!empty($id)) {return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetch_all(MYSQLI_ASSOC);} else {return $db->query('SELECT * FROM posts')->fetch_all(MYSQLI_ASSOC);} }function select_v2($id = '') {global $app;$db = db();if (!empty($id)) {$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');$stmt->bind_param('i', $id);} else {$stmt = $db->prepare('SELECT * FROM posts');}$stmt->execute();//get_result和fetch_all需要mysqlnd支持,PHP从5.4开始内置mysqlnd.return $stmt->get_result()->fetch_all(MYSQLI_ASSOC); }function update($id, $title = '', $content = '') {global $app;$db = db();//var_export($db->query('SELECT @@autocommit')->fetch_all(MYSQLI_ASSOC)); exit(); //返回1可见MySQL默认会自动提交事务.$db->query('SET AUTOCOMMIT=0'); //$db->autocommit(false);//注意,InnoDB引擎会把写操作(insert/update/delete)当做事务来处理.//所以commit提交事务后autocommit记得重新设为true,否则delete等这些写操作因为没有手动commit会自动回滚.$db->query('START TRANSACTION'); //$db->begin_transaction(); PHP从5.5开始才有这个函数.$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');$stmt->bind_param('ssi', $title, $content, $id);$stmt->execute();$db->query('COMMIT'); //$db->commit();//$db->query('ROLLBACK'); //$db->rollback();$db->query('SET AUTOCOMMIT=1'); //commit提交事务后autocommit记得重新设为true.//UPDATE时,如果更新的内容跟原来的内容一样,affected_rows也会返回0.return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? true : false;//affected_rows://返回-1表示查询出错.//返回0表示没有数据被修改.//返回1表示有1条数据被修改.//如果返回的值大于PHP_INT_MAX,这时affected_rows的类型是一个字符串. }function delete($id) {global $app;$db = db();$db->query('DELETE FROM posts WHERE id = '.intval($id));return ($db->affected_rows !== 0 && $db->affected_rows !== -1) ? true : false; }function delete_v2($id) {global $app;$db = db();$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');$stmt->bind_param('i', $id);$stmt->execute();return ($stmt->affected_rows !== 0 && $stmt->affected_rows !== -1) ? true : false; }header('Content-Type: text/plain; charset=utf-8');//mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS tuxbase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;GRANT ALL PRIVILEGES ON tuxbase.* TO tux@127.0.0.1 IDENTIFIED BY '913dab0c6788bb8f0';FLUSH PRIVILEGES;" $app = array('db_host' => '127.0.0.1','db_user' => 'tux','db_pass' => '913dab0c6788bb8f0','db_name' => 'tuxbase','db_port' => 3306 );$table = "CREATE TABLE IF NOT EXISTS posts (id int(10) unsigned NOT NULL AUTO_INCREMENT,post_title varchar(255) NOT NULL DEFAULT '',post_content text NOT NULL DEFAULT '',PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";db()->query('DROP TABLE IF EXISTS posts;') or exit(); db()->query($table) or exit();echo "var_export(insert('标题1', '内容1'));\n"; var_export(insert('标题1', '内容1')); echo "\n\n";echo "var_export(insert('标题2', '内容2'));\n"; var_export(insert('标题2', '内容2')); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";echo "var_export(update(2, '标题2_更新','内容2_更新'));\n"; var_export(update(2, '标题2_更新','内容2_更新')); echo "\n\n";echo "var_export(select(2));\n"; var_export(select(2)); echo "\n\n";echo "var_export(delete(2));\n"; var_export(delete(2)); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";

PHP PDO_MySQL:

http://php.net/pdo

<?phpfunction db() {global $app;static $db;if ($db) {return $db;} else {/* MySQL */try {$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";$db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(PDO::ATTR_PERSISTENT => false,PDO::ATTR_EMULATE_PREPARES => false,PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));} catch (PDOException $e) {echo $e->getMessage();exit();}/* SQLite try {$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');} catch (PDOException $e) {echo $e->getMessage();exit();}*/}return $db; }function insert($title = '', $content = '') {global $app;$db = db();$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');$stmt->bindParam(1, $title, PDO::PARAM_STR);$stmt->bindParam(2, $content, PDO::PARAM_STR);$stmt->execute();//插入失败,lastInsertId为0.return ($stmt->rowCount() !== 0) ? array(true, 'lastInsertId' => $db->lastInsertId()) : array(false, 'lastInsertId' => $db->lastInsertId()); }function select($id = '') {global $app;$db = db();if (!empty($id)) {return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);} else {return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);} }function select_v2($id = '') {global $app;$db = db();if (!empty($id)) {$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');$stmt->bindParam(1, $id, PDO::PARAM_INT);} else {$stmt = $db->prepare('SELECT * FROM posts');}$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC); }function update($id, $title = '', $content = '') {global $app;$db = db();//echo PDO::ATTR_AUTOCOMMIT; //返回0可见PDO默认禁用自动提交事务.//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); //返回1可见MySQL默认会自动提交事务.//SQLite不支持设置PDO::ATTR_AUTOCOMMIT://SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);$db->beginTransaction();//方法1(问号占位符)$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');$stmt->execute(array($title,$content,$id)); //所有值视作PDO::PARAM_STR处理//方法1(命名占位符)//$stmt = $db->prepare('UPDATE posts SET post_title = :title, post_content = :content WHERE id = :id');//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id)); //所有值视作PDO::PARAM_STR处理//方法2(问号占位符)//$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');//$stmt->bindParam(1, $title, PDO::PARAM_STR);//$stmt->bindParam(2, $content, PDO::PARAM_STR);//$stmt->bindParam(3, $id, PDO::PARAM_INT);//$stmt->execute();//方法2(命名占位符)//$stmt = $db->prepare('UPDATE posts SET post_title = :title, post_content = :content WHERE id = :id');//$stmt->bindParam(':title', $title, PDO::PARAM_STR);//$stmt->bindParam(':content', $content, PDO::PARAM_STR);//$stmt->bindParam(':id', $id, PDO::PARAM_INT);//$stmt->execute();//MySQLi只有一种写法(MySQLi不支持命名占位符)//$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');//$stmt->bind_param('ssi', $title, $content, $id);echo 'sleep(3);'."\n";sleep(3);$db->commit();$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为truereturn ($stmt->rowCount() !== 0) ? true : false; }function delete($id) {global $app;$db = db();return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false; }function delete_v2($id) {global $app;$db = db();$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');$stmt->bindParam(1, $id, PDO::PARAM_INT);$stmt->execute();return ($stmt->rowCount() !== 0) ? true : false; }header('Content-Type: text/plain; charset=utf-8');$app = array('db_host' => '127.0.0.1','db_user' => 'tux','db_pass' => '913dab0c6788bb8f0','db_name' => 'tuxbase','db_port' => 3306 );$mysql = "CREATE TABLE IF NOT EXISTS posts (id int(10) unsigned NOT NULL AUTO_INCREMENT,post_title varchar(255) NOT NULL DEFAULT '',post_content text NOT NULL DEFAULT '',PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";$sqlite = "CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY,post_title VARCHAR(255) NOT NULL,post_content TEXT NOT NULL )";db()->query('DROP TABLE IF EXISTS posts;') or exit(); db()->query($mysql) or exit();//并发时,SQLite在insert时因为库文件被其他请求锁住而导致阻塞 echo "var_export(insert('标题1', '内容1'));\n"; var_export(insert('标题1', '内容1')); echo "\n\n";echo "var_export(insert('标题2', '内容2'));\n"; var_export(insert('标题2', '内容2')); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";echo "var_export(update(2, '标题2_更新','内容2_更新'));\n"; var_export(update(2, '标题2_更新','内容2_更新')); echo "\n\n";echo "var_export(select(2));\n"; var_export(select(2)); echo "\n\n";echo "var_export(delete(2));\n"; var_export(delete(2)); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";

PHP PDO_SQLite:

<?phpfunction db() {global $app;static $db;if ($db) {return $db;} else {/* MySQL try {$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";$db = new PDO($dsn, $app['db_user'], $app['db_pass'], array(PDO::ATTR_PERSISTENT => false,PDO::ATTR_EMULATE_PREPARES => false,PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));} catch (PDOException $e) {echo $e->getMessage();exit();}*//* SQLite */try {$db = new PDO('sqlite:'.dirname(__FILE__).'/data.db3');} catch (PDOException $e) {echo $e->getMessage();exit();}}return $db; }function insert($title = '', $content = '') {global $app;$db = db();$stmt = $db->prepare('INSERT INTO posts (post_title, post_content) VALUES (?, ?)');$stmt->bindParam(1, $title, PDO::PARAM_STR);$stmt->bindParam(2, $content, PDO::PARAM_STR);$stmt->execute();return ($stmt->rowCount() !== 0) ? array(true, 'lastInsertId' => $db->lastInsertId()) : array(false, 'lastInsertId' => $db->lastInsertId()); }function select($id = '') {global $app;$db = db();if (!empty($id)) {return $db->query('SELECT * FROM posts WHERE id = '.intval($id))->fetchAll(PDO::FETCH_ASSOC);} else {return $db->query('SELECT * FROM posts')->fetchAll(PDO::FETCH_ASSOC);} }function select_v2($id = '') {global $app;$db = db();if (!empty($id)) {$stmt = $db->prepare('SELECT * FROM posts WHERE id = ?');$stmt->bindParam(1, $id, PDO::PARAM_INT);} else {$stmt = $db->prepare('SELECT * FROM posts');}$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC); }function update($id, $title = '', $content = '') {global $app;$db = db();//echo PDO::ATTR_AUTOCOMMIT; //返回0可见PDO默认禁用自动提交事务.//echo $db->getAttribute(PDO::ATTR_AUTOCOMMIT); exit(); //返回1可见MySQL默认会自动提交事务.//SQLite不支持设置PDO::ATTR_AUTOCOMMIT://SQLite: Uncaught exception 'PDOException' with message 'The auto-commit mode cannot be changed for this driver'//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, false);$db->beginTransaction();$stmt = $db->prepare('UPDATE posts SET post_title = ?, post_content = ? WHERE id = ?');$stmt->execute(array($title,$content,$id)); //所有值视作PDO::PARAM_STR处理//$stmt->execute(array(':title' => $title,':content' => $content,':id' => $id));//$stmt->bind_param('ssi', $title, $content, $id); //对比mysqliecho 'sleep(3);'."\n";sleep(3);$db->commit();//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为truereturn ($stmt->rowCount() !== 0) ? true : false; }function delete($id) {global $app;$db = db();return ($db->query('DELETE FROM posts WHERE id = '.intval($id))->rowCount() !== 0) ? true : false; }function delete_v2($id) {global $app;$db = db();$stmt = $db->prepare('DELETE FROM posts WHERE id = ?');$stmt->bindParam(1, $id, PDO::PARAM_INT);$stmt->execute();return ($stmt->rowCount() !== 0) ? true : false; }header('Content-Type: text/plain; charset=utf-8');$app = array('db_host' => '127.0.0.1','db_user' => 'tux','db_pass' => '913dab0c6788bb8f0','db_name' => 'tuxbase','db_port' => 3306 );$mysql = "CREATE TABLE IF NOT EXISTS posts (id int(10) unsigned NOT NULL AUTO_INCREMENT,post_title varchar(255) NOT NULL DEFAULT '',post_content text NOT NULL DEFAULT '',PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";$sqlite = "CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY,post_title VARCHAR(255) NOT NULL,post_content TEXT NOT NULL )";db()->query('DROP TABLE IF EXISTS posts;') or exit(); db()->query($sqlite) or exit();//并发时,SQLite在insert时因为库文件被其他请求锁住而导致阻塞 echo "var_export(insert('标题1', '内容1'));\n"; var_export(insert('标题1', '内容1')); echo "\n\n";echo "var_export(insert('标题2', '内容2'));\n"; var_export(insert('标题2', '内容2')); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";echo "var_export(update(2, '标题2_更新','内容2_更新'));\n"; var_export(update(2, '标题2_更新','内容2_更新')); echo "\n\n";echo "var_export(select(2));\n"; var_export(select(2)); echo "\n\n";echo "var_export(delete(2));\n"; var_export(delete(2)); echo "\n\n";echo "var_export(select());\n"; var_export(select()); echo "\n\n";

 

转载于:https://my.oschina.net/eechen/blog/663332

总结

以上是生活随笔为你收集整理的PHP MySQLi/PDO_MySQL/PDO_SQLite CRUD(增查改删)的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。