pdo扩展为php提供了一个轻量级的一致接口,可以支持多种数据库,下面我们试着使用pdo扩展来操作mysql,实现数据的增删查改。
<?php
$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'root';
$password = '123456';
//链接数据库
try {
$params = [
PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8", //设置编码
PDO::ATTR_EMULATE_PREPARES => false, //使用预处理
];
$db = new PDO($dsn, $user, $password, $params);
} catch (PDOException $e) {
echo $e->getMessage();
exit;
}
//查询多条数据
$sth = $db->prepare('SELECT id, title FROM article WHERE id =?');
$sth->execute([1]);
$res1 = $sth->fetchAll(PDO::FETCH_ASSOC); //有多种方式可以设置返回的值
print_r($res1);
//查询一条数据
$sth = $db->prepare('SELECT id, title FROM article WHERE id =?');
$sth->execute([1]);
$res2 = $sth->fetch(PDO::FETCH_ASSOC);
print_r($res2);
$sth = null; //释放结果集
//取一列
$sth = $db->prepare('SELECT title FROM article WHERE 1');
$sth->execute();
$res3 = $sth->fetchAll(PDO::FETCH_COLUMN);
print_r($res3);
$sth = null; //释放结果集
//添加
$sth = $db->prepare("INSERT INTO `article`(`title`,`content`,`created_at`)VALUES(?,?,?)");
$sth->execute(['标题', '内容', time()]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
echo "自增ID:", $db->lastInsertId(), "<br/>";
//修改
$sth = $db->prepare("UPDATE `article` SET `title`=? WHERE `id`=?");
$sth->execute(['标题12', 2]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
//删除
$sth = $db->prepare("DELETE FROM `article` WHERE `id`=?");
$sth->execute([3]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
//事务
try {
$db->beginTransaction();
$sth = $db->prepare("UPDATE `article` SET `title`=? WHERE `id`=?");
$sth->execute(['事务1', 1]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
$sth = $db->prepare("UPDATE `article` SET `title`=? WHERE `id`=?");
$sth->execute(['事务1', 2]);
echo "受影响的行数:", $sth->rowCount(), "<br/>";
//有时候,你可能需要手动抛出异常,来进行回滚操作
// throw new Exception("错误信息", 1);
$db->commit();
echo "成功!";
} catch (Exception $e) {
$db->rollBack();
echo $e->getMessage();
}
$db = null; //关闭数据库连接
建表语句:
CREATE TABLE `article` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content` text COLLATE utf8mb4_unicode_ci,
`created_at` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章表';
INSERT INTO `test`.`article` (`id`, `title`, `content`, `created_at`) VALUES ('1', '标题12', '测试内容', '234');
INSERT INTO `test`.`article` (`id`, `title`, `content`, `created_at`) VALUES ('2', '标题12', '测试内容2', '2323');