This article mainly introduces the MySQLI method of operating MySQL database with PHP. It has certain reference value. Now I share it with you. Friends in need can refer to it.
Three ways to operate MySQL with PHP This method
Non-permanent connection, every time the database is used, a new process will be opened,
Disadvantages:
The performance is low and will be abandoned after PHP5.0.
Permanent connection, reduce server pressure, only supports MySQL
Disadvantages:
Takes up memory
can realize the common functions of MySQLi and supports most databases.
MySQLI method (object-oriented)
$mysqli = new mysqli(‘host’,‘username’,‘password’,‘database’)
$mysqli -> query($sql);
$result -> fetch_array([MYSQLI_ASSOC]) 一个 $result -> fetch_assoc() 一个 $result -> fetch_all([MYSQLI_ASSOC]) 全部
The default method one is index array
, after adding parameters it becomes associative array
.
Method two is equivalent to method one plus parameters.
$mysqli -> close();
<?php // MySQLi增删改例子 // MySQLi方式连接数据库 $mysqli = new mysqli('localhost','root','123456789','test'); // 执行SQL语句 // 使用utf-8编码 $mysqli -> query('set names utf8'); // 插入 //$result = $mysqli -> query("INSERT users(name,money) VALUES('甜筒',4) "); //$result = $mysqli -> query("INSERT users(name,money) VALUES('麦辣鸡腿堡',16) "); // 修改 //$result = $mysqli -> query("UPDATE users SET `name`= '麦旋风' WHERE `id` = 2"); // 删除 //$result = $mysqli -> query("DELETE FROM users WHERE `id` = 5"); //var_dump($result); /*********************查询*****************/ $result = $mysqli -> query("SELECT * FROM users"); // 获取结果集 $data = $result -> fetch_all(MYSQLI_ASSOC); var_dump($data);
A transaction is a database operation that combines multiple logical tasks into one execution unit (Total success or failure).
$mysqli -> autocommit(false)
This method will cause the transaction to be submitted immediately, so the parameter false needs to be filled in.
$mysqli -> rollback()
If there is any error in the data, you can use this method to restore the data.
$mysqli -> commit()
$mysqli -> close()
When we use query() to execute a SQL statement, he does not It will take care of the changes in the database and whether the SQL statement is wrong.
So we cannot rely on the return value of query() to determine whether the operation is successful or not.
To use the number of affected rows to determine:
$mysqli -> affected_rows
query('set names utf8'); // 开启事务 $mysqli -> autocommit(false); //SQL $sql1 = "UPDATE users SET `money`=`money`+1 WHERE `id` = 1"; $sql2 = "UPDATE users SET `money`=`money`-1 WHERE `id` = 11"; // 执行SQL语句 $result = $mysqli -> query($sql1); $r1 = $mysqli -> affected_rows; $result = $mysqli -> query($sql2); $r2 = $mysqli -> affected_rows; if($r1 > 0 && $r2 > 0){ // 提交事务 $mysqli -> commit(); echo '操作成功'; }else{ // 事务回滚 $mysqli -> rollback(); echo '操作失败'; }
Preprocessing, first submit the SQL statement to the server and execute it Precompiled.
When the client executes a SQL statement, it only needs to upload the input parameters;
If multiple reads or storage are involved, the efficiency is higher than that of ordinary SQL execution operations.
Parameter in the VALUE of the SQL statement is replaced by a question mark.
$stmt = $mysqli -> prepare($sql)
$stmt -> bind_param('参数类型', 参数1, 参数2...)
Parameter type description:
string-s int-i double-d bool-b
Special reminder:
Write parameter types in order without spaces or commas. For example, parameter 1 is string and parameter 2 is bool, written like this: $stmt -> bind_param('si', parameter 1, parameter 2)
$stmt -> bind_result()
Special attention:
The variable of bind_result corresponds to the field name in the select statement, so be sure to pay attention to the quantity and order! ! !
$stmt -> execute()
$stmt->store_result()
Please see the link below for details
Detailed analysis
$stmt ->fetch()
query('set names utf8'); // SQL语句 $sql = "SELECT * FROM users WHERE id>? "; // 创建预编译对象 $stmt = $mysqli -> prepare($sql); // 参数绑定 $id = 1; $stmt -> bind_param('i',$id); // 绑定结果集 参数要和表的参数一致 $stmt -> bind_result($id,$name,$money,$age); // 执行SQL语句 $stmt -> execute(); // 执行SQL语句 $stmt->store_result(); // 接收结果集 while($stmt -> fetch()){ $data[] = [ 'id' => $id, 'name' => $name, 'money' => $money ]; } var_dump($data);
Related recommendations:
PHP operation database: select operation operation
The above is the detailed content of MySQLI method of operating MySQL database. For more information, please follow other related articles on the PHP Chinese website!