PHP mysqli operation database

Getting Started

Since the mysql connection method has been abolished, additional components need to be downloaded to use mysql_connect() in php7.

There are two ways to use mysqli: process-oriented and object-oriented.

mysqli provides three classes:

● mysqli connection related

● mysqli_result processing result set

● mysqli_stmt pre- Processing class

Database connection

<?php
$db_host = &#39;localhost&#39;;
$db_name = &#39;test&#39;;
$db_user = &#39;root&#39;;
$db_pwd = &#39;&#39;;
//面向对象方式
$mysqli = new mysqli($db_host, $db_user, $db_pwd, $db_name);
//面向对象的昂视屏蔽了连接产生的错误，需要通过函数来判断
if(mysqli_connect_error()){
    echo mysqli_connect_error();
}
//设置编码
$mysqli->set_charset("utf8");//或者 $mysqli->query("set names &#39;utf8&#39;")
//关闭连接
$mysqli->close();
//面向过程方式的连接方式
$mysqli = mysqli_connect($db_host, $db_user, $db_pwd, $db_name);
//判断是否连接成功
if(!$mysqli ){
    echo mysqli_connect_error();
}
//关闭连接
mysqli_close($mysqli);
?>

Database query

General: query(sql) can be used to execute sql statements, execute If it fails, it will return false. If the select is successful, it will return the result set object. Otherwise, it will return true. As long as it is not false, it means the SQL statement was executed successfully.

<?php
//无结果集示例
$sql = "insert into table_name (`name`, `address`) values (&#39;xiaoming&#39;, &#39;adddddrrreess&#39;)";
$result = $mysqli->query($sql);
//或者
$sql = "delete from table_name where name=&#39;xiaoming&#39;";
$result = $mysqli->query($sql);
if($result === false){
    echo $mysqli->error;
    echo $mysqli->errno;
}
//影响条数
echo $mysqli->num_rows;
//插入的id
echo $mysqli->insert_id;
$mysqli->close();

With result set

<?php
$sql = "select * from table_name";
$result = $mysqli->query($sql);
if($result === false){//执行失败
    echo $mysqli->error;
    echo $mysqli->errno;
}
//行数
echo $result->num_rows;
//列数 字段数
echo $result->field_count;
//获取字段信息
$field_info_arr = $result->fetch_fields();
//移动记录指针
//$result->data_seek(1);//0 为重置指针到起始
//获取数据
while($row = $result->fetch_assoc()){
    echo $row[&#39;name&#39;];
    echo $row[&#39;address&#39;];
}
//也可一次性获取所有数据
//$result->data_seek(0);//如果前面有移动指针则需重置
$data = $result->fetch_all(MYSQLI_ASSOC);
$mysqli->close();

Preprocessing example

Preprocessing can effectively prevent the occurrence of sql injection, mysqli_stmt It is a preprocessing class

<?php
$sql = "insert inro table_name (&#39;name&#39;,&#39;address&#39;) values (?,?)";
//获得预处理对象
$stmt = $mysqli->prepare($sql);
//绑定参数 第一个参数为绑定的数据类型
/*
i:integer 整型
d:double 浮点型
s:string 字符串
b:a blob packets blob数据包
*/
$name = "xiaoming";
$address = "adddressss";
$stmt->bind_param("ss", $name, $address);//绑定时使用变量绑定
//执行预处理
$stmt->execute();
/*
//可重新绑定 多次执行
$stmt->bind_param("ss", $name, $address);
$stmt->execute();
*/
//插入的id 多次插入为最后id
echo $stmt->insert_id;
//影响行数 也是最后一次执行的
echo $stmt->affected_rows;
//错误号
echo $stmt->errno;
//错误信息
echo $stmt->error;
//关闭
$stmt->close();
$mysqli->close();
下面示例select的预处理
//注释部分省略
$sql = "select * from table_name where id<?";
$stmt = $mysqli->prepare($sql);
$id = 30;
$stmt->bind_param("i", $id);
$stmt->execute();
//获取结果集
$result = $stmt->get_result();//结果集取后的操作就和之前一样了
//获取所有数据
$data = $result->fetch_all(MYSQLI_ASSOC);
$result->close();
$mysqli->close();

Execute multiple sql statements multiquery at one time (not recommended), the execution result is not a result set, affected_rows is the number of the last affected items

<?php
$sql_arr = array(
    "insert into table_name (`name`,`address`) values (&#39;xiaoming&#39;,&#39;a&#39;)",
    "insert into table_name (`name`,`address`) values (&#39;xiaohong&#39;,&#39;a&#39;)",
    &#39;delete from table_name where id=23&#39;,
);
$sql = implode(&#39;;&#39;, $sql_arr);
$result = $mysqli->multi_query($sql);
if($result === false){
    echo $mysqli->error;
}
$mysqli->close();

More PHP related knowledge, Please visit PHP Chinese website!

The above is the detailed content of PHP mysqli operation database. For more information, please follow other related articles on the PHP Chinese website!