1、编程:MySQLi面向对象中的查询操作
a.增
实例
<?php
require 'sql.php';
$sql = "INSERT IGNORE `staff` SET `name`= ?, `salary`= ?";
$stmt = $mysqli ->prepare($sql);
$name = 'king';
$salary = 9800;
$stmt ->bind_param('si',$name,$salary);
if ($stmt ->execute()){
if ($stmt ->affected_rows >0){
echo '<br>成功的插入'.$stmt ->affected_rows. '条记录，新增记录的主键是'. $stmt ->insert_id;
}else{
echo '<br>没有新增记录';
}
}else{
exit($stmt ->errno. ':' .$stmt ->error);
}
$stmt ->close();
$mysqli ->close();运行实例 »
点击 "运行实例" 按钮查看在线实例
实例
<?php
require 'sql.php';
$sql = "INSERT IGNORE `staff` SET `name` = ? , `salary` = ? ";
$stmt = $mysqli ->prepare($sql);
$data[] = ['name' =>'tony','salary' =>5800];
$data[] = ['name' =>'arthur','salary' =>15800];
$data[] = ['name' =>'kitty','salary' =>8800];
$stmt ->bind_param('si',$name,$salary);
foreach ($data as $staff){
$name = $staff['name'];
$salary = $staff['salary'];
if ($stmt ->execute()){
if ($stmt ->affected_rows >0){
echo '<br>成功的新增'. $stmt ->affected_rows . '条记录，新增记录的主键id是：' .$stmt ->insert_id;
}else {
echo '<br>没有新增记录';
}
}else ($stmt ->errno .':' . $stmt ->error);
}
$stmt ->close();
$mysqli ->close();运行实例 »
点击 "运行实例" 按钮查看在线实例
b.改
实例
<?php
require 'sql.php';
$sql = "UPDATE `staff` SET `salary` = ? WHERE `id` = ?";
$stmt = $mysqli ->prepare($sql);
$salary = 8888;
$id = 21;
$stmt ->bind_param('ii',$salary,$id);
if ($stmt ->execute()){
if ($stmt ->affected_rows >0){
echo '<br>成功的更新' . $stmt ->affected_rows .'条记录';
}else{
echo '<br>没有更新记录';
}
}else{
exit($stmt ->errno . ':' .$stmt ->error);
}
$stmt ->close();
$mysqli ->close();运行实例 »
点击 "运行实例" 按钮查看在线实例
c.删
实例
<?php
require 'sql.php';
$sql = "DELETE FROM `staff` WHERE `id` = ?";
$stmt = $mysqli ->prepare($sql);
$id = 19;
$stmt ->bind_param('i' ,$id);
if ($stmt ->execute()){
if ($stmt ->affected_rows >0){
echo '<br>成功的删除了' . $stmt ->affected_rows . '条记录';
}else{
echo '<br>删除记录不成功';
}
}else{
exit($stmt ->errno . ':' .$stmt ->error);
}
$stmt ->close();
$mysqli ->close();运行实例 »
点击 "运行实例" 按钮查看在线实例
d.查
实例
<?php
require 'sql.php';
$sql = "SELECT `id`,`name`,`salary` FROM `staff` WHERE `salary` > ?";
$stmt = $mysqli ->stmt_init();
if ($stmt ->prepare($sql)){
$stmt ->bind_param('i' ,$salary);
$salary = 5000;
if ($stmt ->execute()){
$stmt ->store_result();
$stmt ->bind_result($id,$name,$salary);
if ($stmt ->num_rows >0){
while ($stmt ->fetch()){
echo '<p>id:'.$id.'---姓名:'.$name.'---工资:'.$salary.'</p>';
}
}else{
exit('<p>当前表中无此数据</p>');
}
$stmt ->free_result();
}else{
exit($stmt ->errno .':'. $stmt->error);
}
}else{
exit($stmt ->errno .':'. $stmt ->error);
}
$stmt ->close();
$mysqli ->close();运行实例 »
点击 "运行实例" 按钮查看在线实例
2、问答: PDO 的优势
第一个优势"简单",代码优化了,更优雅和精简!
第二个也是相比较mysqli最大的优势可以面向所有类型的数据库进行操作 ,简单且牛鼻!
3、编程:PDO 连接数据库
实例
<?php
echo '<meta charset="UTF-8">';
$dsn = 'mysql:host=127.0.0.1;dbname=php';
$user = 'root';
$pass = 'root';
try {
$pdo = new PDO($dsn,$user,$pass);
}catch (PDOException $e){
die('Connect ERROR! :'. $e ->getMessage());
}
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
4、 编程:PDO新增数据
实例
<?php
echo '<meta charset="utf-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php',
'root','root');
$stmt = $pdo->prepare("INSERT `user` SET `name`= :name,`email`= :email,`password`= sha1(:password)");
$stmt ->execute(['name'=>'zy','email'=>'zy@qq.cn','password'=>'123']);
echo '<h3>成功添加了'. $stmt->rowCount(). '条记录</h3>';
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
5、编程: PDO更新数据
实例
<?php
echo '<meta charset="UTF-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$sql = "UPDATE `user` SET `email` = :email WHERE `id` = :id";
$stmt = $pdo ->prepare($sql);
$stmt ->execute(['email'=>'zy@php.cn','id'=>31]);
echo '<h3>成功更新了'. $stmt ->rowCount(). '条记录';
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
6、编程: PDO 删除数据
实例
<?php
echo '<meta charset="UTF-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$sql = "DELETE FROM `user` WHERE `id` = :id";
$stmt = $pdo ->prepare($sql);
if ($stmt ->execute(['id' =>3])){
echo '<h3>成功删除了' .$stmt ->rowCount() .'条记录';
}else{
echo '<h3>删除失败</h3>';
print_r($stmt ->errorInfo());
exit();
}
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
7、编程:ＰＤＯ查询数据
实例查询1
<?php
echo '<meta charset="UTF-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$sql = "SELECT `name`,`salary` FROM `staff` WHERE `id` < :id";
$stmt = $pdo ->prepare($sql);
$stmt ->execute(['id'=>5]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo var_export($row),'<br>';
}
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
实例查询二
<?php
echo '<meta charset="UTF-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$sql = "SELECT count(*) FROM `staff` WHERE `salary` > :salary";
$stmt = $pdo ->prepare($sql);
$stmt ->execute(['salary'=>6000]);
echo '<h3>工资大于6000的人数:'. $stmt ->fetchColumn();
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
实例查询三
<?php
echo '<meta charset="UTF-8">';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$sql = "SELECT `name`,`salary` FROM `staff` WHERE `id`< :id";
$stmt = $pdo ->prepare($sql);
$stmt ->execute(['id'=>6]);
$stmt ->bindColumn('name',$name);
$stmt ->bindColumn('salary',$salary);
while ($stmt ->fetch(PDO::FETCH_BOUND)){
echo '姓名:'.$name.'|'.'工资:'.$salary.'<br>';
}
$stmt = null;
$pdo = null;运行实例 »
点击 "运行实例" 按钮查看在线实例
8、问答： 获取结果集记录数量的正确方式是什么？
首先如果是泛查询 , sql的SELECT后面要跟count(*) , 否则有可能会得不到数据;
其次如果是确定的列值 , 则需要将结果集(列)绑定到变量中以PDO::FETCH_BOUND为参数将结果遍历出来;