php操作MySQL(pdo)

时间:2019-07-03作者:klpeng分类:IT综合浏览:3263评论:0

      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');

 

打赏
文章版权声明:除非注明,否则均为彭超的博客原创文章,转载或复制请以超链接形式并注明出处。
相关推荐

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

猜你喜欢