57.mysqli扩展库(4)

1. sql 语句执行原理

2. mysqli 预处理

php
header('Content-Type:text/html;charset=utf8');

//预编译演示

//需求，使用预编译的方式，向数据库添加3个用户

//1.创建 mysqli 对象

$mysqli = new MySQLi('localhost','root','123456','test');

//2.创建预编译对象

$sql = "insert into user1 (name,password,email,age) values(?,?,?,?)";//？ 相当于占位符，用数据顶替?
$mysqli_stmt = $mysqli->prepare($sql) or die($mysqli->error);

//3.绑定参数
$name = '小红';
$password='xiaohong';
$email = 'aa@sina.com';
$age = '200';

//4.参数绑定->给？赋值,这里类型和顺序都要一一对应
$mysqli_stmt->bind_param('sssi',$name,$password,$email,$age);

//5.执行
$b = $mysqli_stmt->execute();
if(!$b){
    die("操作失败" . $mysqli_stmt->error);
}else{
    echo "操作成功";
}

//释放资源
$mysqli->close();

批量增加：

php
header('Content-Type:text/html;charset=utf8');

//预编译演示

//需求，使用预编译的方式，向数据库添加3个用户

//1.创建 mysqli 对象

$mysqli = new MySQLi('localhost','root','123456','test');

//2.创建预编译对象

$sql = "insert into user1 (name,password,email,age) values(?,?,?,?)";//？ 相当于占位符，用数据顶替?
$mysqli_stmt = $mysqli->prepare($sql) or die($mysqli->error);

//3.绑定参数
$name = '小红';
$password='xiaohong';
$email = 'aa@sina.com';
$age = '200';

//4.参数绑定->给？赋值,这里类型和顺序都要一一对应
$mysqli_stmt->bind_param('sssi',$name,$password,$email,$age);

//5.执行
$b = $mysqli_stmt->execute();

//6. 继续添加，这里可以用循环
$name = "老妖";
$password="laoyao";
$email="laoyao@qq.com";
$age='100';
$mysqli_stmt->bind_param('sssi',$name,$password,$email,$age);
$b = $mysqli_stmt->execute();

$name = "存储";
$password="cuenchu";
$email="cuenchu@qq.com";
$age='10';
$mysqli_stmt->bind_param('sssi',$name,$password,$email,$age);
$b = $mysqli_stmt->execute();

if(!$b){
    die("操作失败" . $mysqli_stmt->error);
}else{
    echo "操作成功";
}

//释放资源
$mysqli->close();

3.使用预处理进行查询

php
header('Content-Type:text/html;charset=utf8');

//预编译演示

//需求，使用预编译的方式，查询所有id>5的用户的id,name,email,age
// id 为变化的

$mysqli = new MySQLi('localhost','root','123456','test');

if($mysqli->connect_error){
    die($mysqli->connect_error);
}

//创建一个预定义对象
$sql = "select id,name,email from user1 where id>?";
$mysqli_stmt = $mysqli->prepare($sql);
$id=5;
//绑定参数,即给？赋值
$mysqli_stmt->bind_param("i",$id);

//绑定结果集
$mysqli_stmt->bind_result($id,$name,$email);//可以不跟字段一样,引用传递

//执行
$mysqli_stmt->execute();

//取出绑定的值
while($mysqli_stmt->fetch()){
    echo "
--$id--$name--$email";
}

//关闭资源
//释放结果
$mysqli_stmt->free_result();
//关闭预编译的语句
$mysqli_stmt->close();
//关闭连接
$mysqli->close();

再次执行新的 sql 查询：

php
header('Content-Type:text/html;charset=utf8');

//预编译演示

//需求，使用预编译的方式，查询所有id>5的用户的id,name,email,age
// id 为变化的

$mysqli = new MySQLi('localhost','root','123456','test');

if($mysqli->connect_error){
    die($mysqli->connect_error);
}

//创建一个预定义对象
$sql = "select id,name,email from user1 where id>?";
$mysqli_stmt = $mysqli->prepare($sql);
$id=5;
//绑定参数,即给？赋值
$mysqli_stmt->bind_param("i",$id);

//绑定结果集
$mysqli_stmt->bind_result($id,$name,$email);//可以不跟字段一样,引用传递

//执行
$mysqli_stmt->execute();

//取出绑定的值
while($mysqli_stmt->fetch()){
    echo "
--$id--$name--$email";
}

//还想执行另外一个 sql 指令
echo "
*****绑定新的id号******";
$id=10;
//绑定参数,即给？赋值
$mysqli_stmt->bind_param("i",$id);

//绑定结果集,这时候无需再绑
//$mysqli_stmt->bind_result($id,$name,$email);//可以不跟字段一样,引用传递

//执行
$mysqli_stmt->execute();

//取出绑定的值
while($mysqli_stmt->fetch()){
    echo "
--$id--$name--$email";
}

//关闭资源
//释放结果
$mysqli_stmt->free_result();
//关闭预编译的语句
$mysqli_stmt->close();
//关闭连接
$mysqli->close();

预编译可以自动防止 sql 注入

4. 其他函数的使用

header('Content-Type:text/html;charset=utf8');

function showTable($table_name) {
    $mysqli = new MySQLi('localhost','root','123456','test');

    if($mysqli->connect_error){
        die($mysqli->connect_error);
    }

    //$sql = "select * from $table_name";
    $sql = "desc user1";
    $res = $mysqli->query($sql);

    //如何获取返回总行数和列数
    echo "共有 行" . $res->num_rows . "--列" . $res->field_count;

    echo "";
    //如何取出表头，从$res取出while($field = $res->fetch_field()){
        echo""; //用 {} 解析变量
    }
    echo"";


    //循环取出数据while($row = $res->fetch_row()){
        echo"";
        foreach($rowas$k=>$v ){
            echo"";

        }
        echo"";
    }
    echo"

     
{$field->name}
 
     
   
$v
 
 
";

    //关闭资源

    $res->free();
    $mysqli->close();
}

showTable("user1");

5. PDO 好处