文档章节

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

eechen
 eechen
发布于 2016/04/20 22:04
字数 2149
阅读 1312
收藏 8

PHP MySQLi:

http://php.net/mysqli

<?php

function 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

<?php

function 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记得重新设为true
	return ($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:

<?php

function 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); //对比mysqli
	
	echo 'sleep(3);'."\n";
	sleep(3);
	
	$db->commit();
	//$db->setAttribute(PDO::ATTR_AUTOCOMMIT, true); //commit提交事务后autocommit记得重新设为true
	return ($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";

 

© 著作权归作者所有

共有 人打赏支持
eechen

eechen

粉丝 990
博文 107
码字总数 55962
作品 1
深圳
私信 提问
加载中

评论(8)

qycms_cn
qycms_cn
@ eechen +1024
eechen
eechen
@陨落人间
连mysql和mysqli两套函数都分不清的人就不要自以为是了.http://php.net/manual/zh/mysqli.overview.php 官方文档的建议: mysqli(首选) pdo_mysql(建议) mysql(不建议) 还有phpMyAdmin用的也是mysqli操作MySQL.对于MySQL而言,mysqli远比PDO合适.

全局数组多么方便的东西,用来传递参数再合适不过,去特么狗屁的设计模式.Discuz!的全局数组$_G你不知道?哈哈.一个static静态变量就已经能够避免多次connect数据库,就是这么简单.不服,写一篇博文来PK呀,别特么光说不练眼高手低呀.哈哈.
陨落人间
陨落人间
而且如果是连接几个不同数据库,连工厂模式也不用,。。照上面的兄弟说的,槽点一堆。@彩虹糖tang , @eechen
陨落人间
陨落人间
@eechen , 大神代码,果断差评,$app,居然用全局 global变量,$db连接没有使用单例模式,

$db居然还用mysql函数,还用了@符号
eechen
eechen

引用来自“彩虹糖tang”的评论

你这代码质量确实不咋滴啊,第一个函数就不少槽点(感谢在osc社区为推广php做出的莫大努力)
那就请你写一篇更咋滴的博文让大家学习学习你的写法嘛,看看是你的XX模式好用还是我的全局变量好用.
游走的鱼
彩虹糖tang 。。。。
彩虹糖tang
彩虹糖tang
你这代码质量确实不咋滴啊,第一个函数就不少槽点(感谢在osc社区为推广php做出的莫大努力)
Android Studio 通过一个登录功能介绍SQLite数据库的使用

前言: SQLite简介:是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的,而且目前已经在很...

AnneHan
09/29
0
0
修改IT资产库存管理(ITDB)使用mysql数据库

ITDB默认使用Sqlite作为数据库,本次修改的版本为官方网站1.14版本 1、修改步骤: 将数据库转换到mysql数据库中 修改ITDB程序代码使用mysql数据库 2、转换sqlite数据库到mysql数据库 下载并安...

Tevisky
2015/02/12
0
0
osc上一位哥们的 php编译参数

Ubuntu 12.04, GCC 4.6.3, i5-3230M 编译安装 PHP 5.5.8 耗时6分40多秒time sh -c "./configure.sh && make && make install" > configure.log configure.sh !/bin/bash ./configure --prefi......

渔樵耕读
2014/05/03
0
0
写一个“特殊”的查询构造器 - (一、程序结构,基础封装)

程序的结构 本篇开始,正式进入编码实践中。首先,简单的规划下程序的结构。 如上一篇所说的,我们需要一个基类 PDODriver 用来封装 PDO 的一些公用的方法,Mysql 等每个数据库都新建一个类 ...

MrQ被抢注了
05/11
0
0
开源可自建的 RSS 订阅器 - FreshRSS

FreshRSS 是一款自由免费的 RSS 订阅器,界面美观,支持导入/导出为 OPML 文件、过滤、标记、搜索等,有助于轻松查找和阅读文章,也满足多用户使用,还可以安装很多拓展和更换不同的主题,而...

匿名
10/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

mysql导入较大的数据文件

mysql一次导入较大的sql文件的时候会出现导入的瓶颈,用工具例如sqlyog之类的执行几十M都不行了,直接报内存不足,看来去服务器上执行sql命令才是王道。 服务器上执行有两种思路: 以Linux命...

sensy
10分钟前
2
0
Redis 基础入门

为什么需要Redis 传统的关系数据库MySQL,ORacle等的数据主要还是存储在磁盘上,虽然数据库各自都带有缓存功能,但随着业务量的增大,数据库自身的缓存终将成为瓶颈,次数如果要提供应用的效...

PeakFang-BOK
11分钟前
2
0
VS2017设置“编辑并继续”无效的问题

设置“编辑并继续” 在“工具”菜单上,单击“选项”。 在“选项”对话框中,打开“调试”节点,然后选择“编辑并继续”类别。 若要启用它,请选中“启用‘编辑并继续’”复选框。 若要禁用它...

随你疯
23分钟前
1
0
(5)添加svg支持

(5)添加svg支持 1 安装svg-sprite-loader cnpm install svg-sprite-loader --save svg-sprite-loader是一个webpack loader,可以将多个svg打包成svg-sprite。 2 配置svg-sprite-loader 我们......

neumeng
24分钟前
0
0
17-《深度拆解JVM》之即时编译(上)

一、问题引入 在第一篇中,我们简单了解过即时编译。这是一项用来提升应用程序运行效率的技术。通常而言,代码会先被 Java 虚拟机解释执行,之后反复执行的热点代码则会被即时编译成为机器码...

飞鱼说编程
41分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部