How to use prerare in php

The usage of prerare in PHP is "PDO::prepare", which means preparing the statement to be executed and returning the statement object. Its usage syntax is such as "public PDO::prepare(string $statement, array $driver_options = array())".

Operating system for this tutorial: Windows 10 system, PHP version 8.1.3, Dell G3 computer.

Usage of php prepare

PDO::prepare

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PHP 8 ,PECL pdo >= 0.1.0)

PDO::prepare — Prepare the statement to be executed and return the statement object

Description

public PDO::prepare(string $statement, array $driver_options = array()): PDOStatement为 PDOStatement::execute() 方法准备待执行的 SQL 语句。 语句模板可以包含零个或多个参数占位标记，格式是命名（:name）或问号（?）的形式，当它执行时将用真实数据取代。 在同一个语句模板里，命名形式和问号形式不能同时使用；只能选择其中一种参数形式。 请用参数形式绑定用户输入的数据，不要直接字符串拼接到查询里。

Call PDOStatement::execute (), the parameter placeholder mark for each value must have a unique name. Unless simulation mode is enabled, parameters with the same name cannot be used in the same statement.

Note:

Parameter placeholders can only display complete data literally. It cannot be part of a literal, a keyword, an identifier, or any other arbitrary scope. For example: You cannot bind multiple values ​​​​to a single parameter and then use IN() to query in an SQL statement.

If you use different parameters and call the same SQL statement multiple times through PDO::prepare() and PDOStatement::execute(), the performance of the application will be improved - the driver can allow the client/server to cache the query and Meta information. At the same time, calling PDO::prepare() and PDOStatement::execute() can also prevent SQL injection attacks without manually adding quotes and escaping parameters.

If the built-in driver does not support parameters, PDO will simulate the function of parameters; if the driver only supports one of the styles (named parameters and question mark parameters), it will automatically rewrite to the other style.

注意: The parser used for emulated prepared statements and for rewriting named or question mark style parameters supports the non standard backslash escapes for single- and double quotes. That means that terminating quotes immediately preceeded by a backslash are not recognized as such, which may result in wrong detection of parameters causing the prepared statement to fail when it is executed. A work-around is to not use emulated prepares for such SQL queries, and to avoid rewriting of parameters by using a parameter style which is natively supported by the driver.

Parameters

statement

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

driver_options

The array contains one or more key=>value key-value pairs to set properties for the returned PDOStatement object. Common usage is: setting PDO::ATTR_CURSOR to PDO::CURSOR_SCROLL will get a scrollable cursor. Some drivers have driver-level options that are set during prepare.

Return value

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

Note:

The prepare statement in simulation mode will not interact with the database server, so PDO::prepare() will not check the statement.

Example

Example#1 SQL statement template in named parameter form

<?php
/* 传入数组的值，并执行准备好的语句 */
$sql = &#39;SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour&#39;;
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(&#39;:calories&#39; => 150, &#39;:colour&#39; => &#39;red&#39;));
$red = $sth->fetchAll();
$sth->execute(array(&#39;:calories&#39; => 175, &#39;:colour&#39; => &#39;yellow&#39;));
$yellow = $sth->fetchAll();
?>

Example#2 SQL statement template in question mark form

<?php
/* 传入数组的值，并执行准备好的语句 */
$sth = $dbh->prepare(&#39;SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?&#39;);
$sth->execute(array(150, &#39;red&#39;));
$red = $sth->fetchAll();
$sth->execute(array(175, &#39;yellow&#39;));
$yellow = $sth->fetchAll();
?>

The above is the detailed content of How to use prerare in php. For more information, please follow other related articles on the PHP Chinese website!