Use PDO to operate MySQL database

The content of this article is about using PDO to operate MySQL database. It has certain reference value. Now I share it with everyone. Friends in need can refer to it

Create the database first. Copy the code to the TXT file, then save it as 'Create database.txt' and finally modify it to 'Create database.sql', and finally use navicat to run the database file. The database is created.

The code is as follows:

CREATE TABLE `user` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `name` varchar(25) NOT NULL DEFAULT '',  
  `gender` tinyint(1) NOT NULL DEFAULT '1',  
  `age` int(11) NOT NULL DEFAULT '0',  
  `flag` tinyint(1) NOT NULL DEFAULT '1',  
  PRIMARY KEY (`id`)  
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;  
  
/*Data for the table `user` */  
  
insert  into `user`(`id`,`name`,`gender`,`age`,`flag`) values (1,'allen',1,20,1),(2,'alice',2,18,1),(3,'bob',1,21,1),(4,'dave',1,25,1),(5,'eve',2,20,1),(6,'joy',1,21,1),(7,'june',1,23,1),(8,'linda',2,22,1),(9,'lisa',2,22,1),(10,'liz',2,23,1);

Then create a new PHP file. pdo.php

<?php  
  
$db = array(  
    &#39;host&#39; => &#39;127.0.0.1&#39;,         //设置服务器地址  
    &#39;port&#39; => &#39;3306&#39;,              //设端口   
    &#39;dbname&#39; => &#39;test&#39;,             //设置数据库名        
    &#39;username&#39; => &#39;root&#39;,           //设置账号  
    &#39;password&#39; => &#39;yangji0321&#39;,      //设置密码  
    &#39;charset&#39; => &#39;utf8&#39;,             //设置编码格式  
    &#39;dsn&#39; => &#39;mysql:host=127.0.0.1;dbname=test;port=3306;charset=utf8&#39;,   //这里不知道为什么，也需要这样再写一遍。  
);  
  
//连接  
$options = array(  
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //默认是PDO::ERRMODE_SILENT, 0, (忽略错误模式)  
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认是PDO::FETCH_BOTH, 4  
);  
  
try{  
    $pdo = new PDO($db[&#39;dsn&#39;], $db[&#39;username&#39;], $db[&#39;password&#39;], $options);  
}catch(PDOException $e){  
    die(&#39;数据库连接失败:&#39; . $e->getMessage());  
}  
  
//或者更通用的设置属性方式:  
//$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    //设置异常处理方式  
//$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);   //设置默认关联索引遍历  
  
echo &#39;<pre/>&#39;;  
  
//1 查询  
  
//1)使用query  
$stmt = $pdo->query(&#39;select * from user limit 2&#39;); //返回一个PDOStatement对象  
  
//$row = $stmt->fetch(); //从结果集中获取下一行，用于while循环  
$rows = $stmt->fetchAll(); //获取所有  
  
$row_count = $stmt->rowCount(); //记录数，2  
print_r($rows);  
  
  
  
  
  
  
  
echo &#39;<br>&#39;;  
  
//2)使用prepare 推荐!  
$stmt = $pdo->prepare("select * from user where name = ? and age = ? ");  
$stmt->bindValue(1,&#39;allen&#39;);  
$stmt->bindValue(2,20);  
$stmt->execute();  //执行一条预处理语句 .成功时返回 TRUE, 失败时返回 FALSE   
$rows = $stmt->fetchAll();  
$row_count = $stmt->rowCount(); //记录数，2  
print_r($rows);  
print_r($row_count);  
  
  
  
echo &#39;<br>&#39;;  
  
  
  
  
//2 新增、更新、删除  
//A.1)普通操作  
//$count  =  $pdo->exec("insert into user(name,gender,age)values(&#39;test&#39;,2,23)"); //返回受影响的行数   
//echo $pdo->lastInsertId();  
  
//$count  =  $pdo->exec("update user set name=&#39;test2&#39; where id = 15"); //返回受影响的行数  
//$count  =  $pdo->exec("delete from  user where id = 15"); //返回受影响的行数  
  
  
//A.2)使用prepare 推荐!  
  
$stmt = $pdo->prepare("insert into user(name,gender,age)values(?,?,?)");  
$stmt->bindValue(1, &#39;test&#39;);  
$stmt->bindValue(2, 2);  
$stmt->bindValue(3, 23);  
$stmt->execute();  
$count = $stmt->rowCount();//受影响行数  
echo &#39;prepare方法影响行数：&#39;.$count;    
echo &#39;<br>&#39;;  
  
  
//A.3)使用prepare 批量新增  
  
$stmt = $pdo->prepare("insert into user(name,gender,age)values(?,?,?)");  
$stmt->bindParam(1, $name);  
$stmt->bindParam(2, $gender);  
$stmt->bindParam(3, $age);  
  
$data = array(  
    array(&#39;t1&#39;, 1, 22),  
    array(&#39;t2&#39;, 2, 23),  
);  
  
foreach ($data as $vo){  
    list($name, $gender, $age) = $vo;  
    $stmt->execute();  
}  
  
  
  
  
//B)更新操作  
echo &#39;<br>&#39;;  
  
$stmt = $pdo->prepare("UPDATE `user` SET `age`=? WHERE (`name`= ? )");  
$stmt->bindValue(1, &#39;20&#39;);  
$stmt->bindValue(2, &#39;allen&#39;);  
$num = $stmt->execute();  
$count = $stmt->rowCount();//受影响行数  
echo &#39;更新操作影响行数：&#39;.$count;    
  
  
  
  
  
  
  
  
  
//删除操作  
$stmt = $pdo->prepare("DELETE FROM `user` WHERE (`name`= ? )");  
$stmt->bindValue(1, &#39;t1&#39;);  
$num = $stmt->execute();  
$count = $stmt->rowCount();//受影响行数  
echo &#39;删除操作影响行数：&#39;.$count;    
  
  
  
  
# 【示例5：统计数据】：统计company表有多少条数据  
echo &#39;<br>&#39;;  
  
$num = $pdo->query("select count(*) from user");  
$count = $num->fetchColumn();  
echo &#39;共有数据：【&#39;.$count.&#39;】条&#39;;  
  
  
  
  
?>  
  
  
pdo::query()方法  
当执行返回结果集的select查询时,或者所影响的行数无关紧要时,应当使用pdo对象中的query()方法.  
如果该方法成功执行指定的查询,则返回一个PDOStatement对象.  
如果使用了query()方法,并想了解获取数据行总数,可以使用PDOStatement对象中的rowCount()方法获取.  
  
pdo::exec()方法  
当执行insert,update,delete没有结果集的查询时,使用pdo对象中的exec()方法去执行.  
该方法成功执行时,将返回受影响的行数.注意,该方法不能用于select查询.  
  
  
  
  
PDO事务：  
  
$pdo->beginTransaction();//开启事务处理  
  
try{  
    //PDO预处理以及执行语句...  
      
    $pdo->commit();//提交事务  
}catch(PDOException $e){  
    $pdo->rollBack();//事务回滚  
      
    //相关错误处理  
    throw $e;  
}

Related recommendations:

PHP database is based on PDO operation class (mysql)

PDO queries one row at a time data

The above is the detailed content of Use PDO to operate MySQL database. For more information, please follow other related articles on the PHP Chinese website!