Php_pdo preprocessing statement explanation

Many mature databases support the PreparedStatements concept. You can use multiple methods to implement preprocessing. Next, I will introduce the php_pdo preprocessing statement in detail in this article. I will introduce the example code in detail. if you need it, you can refer to it for reference, let's take a look. This article mainly introduces the php_pdo preprocessing statement. let's take a look at the details below.

I. preprocessing statements can bring two benefits:

1. the query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database will analyze, compile, and optimize
Plan to execute the query. For complex queries, this process takes a long time. if you need to repeat the same query multiple times with different parameters, this process will increase
Greatly speed down applications. By using preprocessing statements, you can avoid repeated analysis, compilation, and optimization cycles. In short, preprocessing statements consume less resources because
And runs faster.

2. parameters provided to the pre-processing statement do not need to be enclosed in quotation marks, and the driver will automatically process them. If the application only uses preprocessing statements, you can ensure that
SQL injection occurs. (However, if the other part of the query is constructed by unescaped input, there is still a risk of SQL injection ).

II. preprocessing example:

 GetMessage ();} // 2. pre-processed SQL statement $ SQL = "insert into stu (id, name, sex, age) values (?,?,?,?) "; $ Stmt = $ pdo-> prepare ($ SQL); // 3. right? BindValue (1, null); $ stmt-> bindValue (2, 'test55 '); $ stmt-> bindValue (3, 'w'); $ stmt-> bindValue (4, 22); * // bindValue method 2/* $ stmt-> bindParam (1, $ id); $ stmt-> bindParam (2, $ name); $ stmt-> bindParam (3, $ sex); $ stmt-> bindParam (4, $ age ); $ id = null; $ name = "test66"; $ sex = "m"; $ age = 33; * // The third binding method // $ stmt-> execute (array (null, 'test777', '22', 55); // 4. run $ stmt-> execute (array (null, 'test77', '22', 55); echo $ stmt-> rowCount ();

 GetMessage ();} // 2. pre-processed SQL statement $ SQL = "insert into stu (id, name, sex, age) values (: id,: name,: sex,: age )"; $ stmt = $ pdo-> prepare ($ SQL); // 3. right? Parameter binding // (first binding method)/* $ stmt-> bindValue ("id", null); $ stmt-> bindValue ("name ", 'ceshi1'); $ stmt-> bindValue ("sex", 'w'); $ stmt-> bindValue ("age", 22 ); * // bindParam ("id", $ id); $ stmt-> bindParam ("name", $ name ); $ stmt-> bindParam ("sex", $ sex); $ stmt-> bindParam ("age", $ age); $ id = null; $ name = "ceshi2 "; $ sex = "m"; $ age = 33; * // The third binding method // $ stmt-> execute (array (null, 'test777', '22 ', 55); // 4. run $ stmt-> execute (array ("id" => null, "name" => "ceshi3", "sex" => "w ", "age" => 66); echo $ stmt-> rowCount ();

 GetMessage ();} // 2. pre-processed SQL statement $ SQL = "select id, name, sex, age from stu"; $ stmt = $ pdo-> prepare ($ SQL); // 3. run $ stmt-> execute (); $ stmt-> bindColumn (1, $ id); $ stmt-> bindColumn (2, $ name ); $ stmt-> bindColumn ("sex", $ sex); $ stmt-> bindColumn ("age", $ age ); while ($ row = $ stmt-> fetch (PDO: FETCH_COLUMN) {echo "{$ id }:{$ name }:{$ sex }:{ $ age}
";}/* Foreach ($ stmt as $ row) {echo $ row ['id']." -------- ". $ row ['name']."
";}*/

Best practice:

// 1. connect to the database try {$ pdo = new PDO ("mysql: host = localhost; dbname = jikexueyuan", "root", "");} catch (PDOException $ e) {die ("database connection failed ". $ e-> getMessage ();} // 2. pre-processed SQL statement $ SQL = 'SELECT catid, catname, catdir from cy_category where parentid =: parentid'; $ stmt = $ pdo-> prepare ($ SQL ); $ params = array ('parentid' => $ subcatid); $ stmt-> execute ($ params); // $ row = $ stm-> fetchAll (PDO :: FETCH_ASSOC); while ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC) {var_dump ($ row); echo"
";}

Preprocessing batch operation instances:

 Prepare ("insert into registry (name, value) VALUES (: name,: value)"); $ stmt-> bindParam (': name', $ name ); $ stmt-> bindParam (': value', $ value); // Insert a row $ name = 'one'; $ value = 1; $ stmt-> execute (); // Insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute (); // use a pre-processing statement to insert data repeatedly. // The following example replaces it with name and value? To execute an insert query. $ Stmt = $ dbh-> prepare ("insert into registry (name, value) VALUES (?, ?) "); $ Stmt-> bindParam (1, $ name); $ stmt-> bindParam (2, $ value); // Insert a row $ name = 'one '; $ value = 1; $ stmt-> execute (); // Insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute (); // use a pre-processing statement to obtain data. // The following example shows how to obtain data based on the format provided by the key value. User input is automatically enclosed in quotation marks, so there is no risk of SQL injection attacks. $ Stmt = $ dbh-> prepare ("SELECT * from registry where name =? "); If ($ stmt-> execute (array ($ _ GET ['name']) {while ($ row = $ stmt-> fetch ()) {print_r ($ row) ;}}?>

Summary

The above is all the content of this article. I hope the content of this article will be helpful for everyone to learn or use php. if you have any questions, please leave a message.

For more details about php_pdo pre-processing statements, refer to PHP Chinese network!