Analysis of prepare() method in PHP

Every time a query statement is sent to the MySQL service, the syntax of the query must be parsed to ensure that the structure is correct and can be executed. This is a necessary step in the process, but it does come with some overhead. It is necessary to do it once, but if you execute the same query repeatedly and only change the column values ​​when inserting multiple rows in batches, the prepared statement will cache the syntax and execution process of the query on the server, and only transfer the data between the server and the client. changing column values ​​to eliminate this additional overhead.

First let’s take a look at the syntax of PDO::prepare:

public PDO::prepare ( string $statement   , array $driver_options = array()   ) : PDOStatement

• $statement: must be a valid SQL statement for the target database server template.

• $driver_options: The array contains one or more key=>value key-value pairs, set attributes for the returned PDOStatement object

• Return Value If the database server completes preparing the statement, the PDOStatement object is returned. If the database server cannot prepare the statement, PDO::prepare() returns false or throws PDOException (depending on the error handler).

Code example:

1. Connect to the database

<?php
$servername="localhost";
$username="root";
$password="root123456";
$dbname="my_database";
$pdo=new PDO("mysql:host=$servername;dbname=$dbname",$username,$password);
echo "连接成功"."<br>";
$pdo->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER);

输出：连接成功

2. Use question mark parameters to prepare SQL statements

$sql ="select * from fate where `id`=?";
$statement=$pdo->query("select * from fate where `id`=3 ");
var_dump($statement->fetch());

echo "<br>";
$stmt2 = $pdo->prepare($sql);
$stmt2 ->execute(array(3));
var_dump($stmt2->fetch());

输出：array(6) {["ID"]=>string(1) "3"0]=>string(1) "3"["NAME"]=>string(7) "luncher"[1]=>string(7) "luncher"["AGE"]=>string(2) "56"[2]=>string(2) "56"}
array(6) {["ID"]=>string(1) "3"[0]=>string(1) "3"["NAME"]=>string(7) "luncher"[1]=>string(7) "luncher"["AGE"]=>string(2) "56"[2]=>string(2) "56"}

3.Use named parameters to prepare SQL statements

$query = "insert into tp_user (id,name,gender)values(:id,:name ,:gender)";
//准备语句
$stmt1 = $pdo->prepare($query);
// 执行查询
$stmt1->execute(array('id'=> 1,':name' => '张三', ':gender' => '男'));
//再次执行
$stmt1->execute(array('id'=> 2,':name' => '李四', ':gender' => '女'));
echo "插入成功！","<br>";

输出;插入成功！

Recommended: 《2021 PHP interview questions summary (collection)》《php video tutorial》

The above is the detailed content of Analysis of prepare() method in PHP. For more information, please follow other related articles on the PHP Chinese website!