mysqli 和pdo 连接数据库

1.mysqli操作数据库

• 1.1 INSERT 插入数据

<?php
//引入数据库配置文件并实例化mysqli对象
require 'conn.php';
//创建数据库查询语句
$sql = "INSERT INTO `users`(`name`,`pwd`,`age`,`tel`) VALUES (?,?,?,?)";
//mysql专有插入语句，效率高
//$sql = 'INSERT `users` SET `name`=?,`pwd`=?,`age`=?,`tel`=?;';
//创建预处理对象
$stmt = $mysqli->prepare($sql);
//$stmt->bind_param('ssis',$name,$pwd,$age,$tel);
$stmt->bind_param('ssis', $name, $pwd, $age, $tel);
//单条数据插入
//$name='Jeanne';
//$pwd=md5('123');
//$age=52;
//$tel='10086';
//执行查询
//$stmt->execute() or die('新增数据失败'.$stmt->error);
//printf('新增数据成功%s条,数据id为%d',$stmt->affected_rows,$stmt->insert_id);
//将数组中的值插入
//$data = [
//    'name' => 'fate',
//    'pwd'=>sha1('123'),
//    'age'=>33,
//    'tel'=>'1890011228'
//];
//结构数组为变量
//extract($data);
//执行查询
//$stmt->execute() or die('新增数据失败'.$stmt->error);
//printf('新增数据成功%s条,数据id为%d',$stmt->affected_rows,$stmt->insert_id);
//插入多条数据
$users = [
    ['name'=> 'Quella','age'=>33, 'pwd'=>sha1('1256'),'tel'=>'188938801'],
    ['name'=> 'Rafael','age'=>12, 'pwd'=>sha1('12456'),'tel'=>'178922801'],
    ['name'=> 'Mica','age'=>55, 'pwd'=>sha1('12346'),'tel'=>'168448801'],
    ['name'=> '骑着蜗牛狂奔','age'=>65, 'pwd'=>sha1('13456'),'tel'=>'138966801'],
    ['name'=> '李白','age'=>111, 'pwd'=>sha1('23456'),'tel'=>'118988801'],
];
//循环插入
foreach ($users as $user){
    extract($user);
    $stmt->execute() or die('插入数据出错:'.$stmt->errno.'<br>'.$stmt->error);
   printf('新增数据成功%s条,数据id为%d',$stmt->affected_rows,$stmt->insert_id);
}
$mysqli->close();

• 1.2 select 查询数据

<?php
require 'conn.php';
$sql = "select `name`,`age`,`tel` from users where `id`>?";
//$sql = 'SELECT * FROM `users` WHERE `id` > ?';
//获取预处理对象
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$id = 20;
$stmt->execute() or die($stmt->error);
//获取查询返回的结果集
$res = $stmt->get_result();
//print_r($res->num_rows);
if ($res->num_rows === 0) {
    exit('没有查询到数据');
}
//使用while查询数据
//while ($user = $res->fetch_assoc()) {
//    vprintf('姓名：%s,年龄: %d,手机: %s <br>', $user);
//}
//使用foreach查询
foreach ($res->fetch_all() as $user){
    vprintf('姓名：%s,年龄: %d,手机: %s <br>', $user);
}
//释放结果集
$res->free();
//关闭连接
$mysqli->close();

• 1.3 update 更新数据

<?php
require 'conn.php';
$sql="update `users` set `name`=?,`age`=?,`pwd`=?,`tel`=? where `id`=?";
$stmt=$mysqli->prepare($sql);
$stmt->bind_param('sissi',$name,$age,$pwd,$tel,$id);
//$id=21;
//$name='Jeanne-s';
//使用数组更新
$data=[
    'name'=>'samss',
    'age'=>22,
    'pwd'=>sha1(123),
    'tel'=>'111112222',
    'id'=>22
];
extract($data);
$stmt->execute() or die('更新数据失败:'.$stmt->error);
printf('更新数据成功%s条',$stmt->affected_rows);
$mysqli->close();

• 1.4 delete 删除数据

<?php
require 'conn.php';
$sql="delete from `users` where `id`=?";
//$sql="delete from `users` where `age`=?";
$stmt=$mysqli->prepare($sql);
$stmt->bind_param('i',$age);
$id=22;
//$age=0;
$stmt->execute() or die('删除失败'.$stmt->error);
printf ("成功删除%s条数据",$stmt->affected_rows);
$mysqli->close();

2.PDO 操作数据库

• 2.1 INSERT 插入数据

<?php
require 'conn.php';
$sql = "INSERT INTO `users`(`name`,`pwd`,`age`,`tel`) VALUES (?,?,?,?)";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(1,$name,PDO::PARAM_STR,50);
$stmt->bindParam(2,$pwd,PDO::PARAM_STR,50);
$stmt->bindParam(3,$age,PDO::PARAM_INT,30);
$stmt->bindParam(4,$tel,PDO::PARAM_STR,50);
$users = [
    ['name'=> 'Adalia','age'=>33, 'pwd'=>sha1('1256'),'tel'=>'188938801'],
    ['name'=> 'Brenda','age'=>12, 'pwd'=>sha1('12456'),'tel'=>'178922801'],
    ['name'=> 'Alice','age'=>55, 'pwd'=>sha1('12346'),'tel'=>'168448801'],
    ['name'=> '骑牛狂奔','age'=>65, 'pwd'=>sha1('13456'),'tel'=>'138966801'],
    ['name'=> 'Catherine','age'=>111, 'pwd'=>sha1('23456'),'tel'=>'118988801'],
];
foreach ($users as $user){
    extract($user);
    $stmt->execute() or die(print_r($stmt->errorInfo(),true));
    if($stmt->rowCount()>0){
        printf("成功添加 %s 条数据",$stmt->rowCount());
    }
 }
//关闭数据连接
unset($pdo);

• 2.2 select 查询数据

<?php
require 'conn.php';
$sql = "select `id`,`name`,`age` from `users` where id>= ?";
$stmt = $pdo->prepare($sql);
$id = 22;
$stmt->bindParam(1, $id, PDO::PARAM_INT, 30);
$stmt->execute() or die(print_r('查询失败' . $stmt->errorInfo()));
foreach ($stmt->fetchAll() as $user) {
    vprintf("id:%s,name:%s,age:%s<br>", $user);
}

• 2.3 update 更新数据

<?php
require 'conn.php';
$sql="update `users` set `name`=?  where id=? ";
$stmt=$pdo->prepare($sql);
//$stmt->bindParam(1,$name,PDO::PARAM_STR,50);
//$stmt->bindParam(2,$id,PDO::PARAM_INT,20);
//使用数组直接绑定
$stmt->execute(['阿大',55]);
if($stmt->rowCount()>0):
    print_r('成功更新'.$stmt->rowCount().'条数据');
endif;
//关闭数据连接
unset($pdo);

• 2.4 delete 删除数据

<?php
require 'conn.php';
$sql = "delete from `users` where `id`=?";
//预处理对象
$stmt = $pdo->prepare($sql);
//匿名占位符绑定
$stmt->bindParam(1, $id, PDO::PARAM_INT, 30);
$id = 42;
$stmt->execute() or die(print_r('删除失败' . $stmt->errorInfo(),true) );
printf("成功删除%s条数据", $stmt->rowCount());
//关闭数据连接
unset($pdo);

总结:熟悉了php操作数据库的基本方法和数据库查询语句的基本写法