@(PDO(PHP data object/PHP數(shù)據(jù)對(duì)象))[PDO|預(yù)處理語(yǔ)句|參數(shù)化查詢]

The database library called PHP Data Objects or PDO for short can use drivers for many different database types, and supports a very important feature known as prepared statements, sometimes also known as parametrized queries.

PDO::prepare

Paste_Image.png

在執(zhí)行之前，對(duì)一條語(yǔ)句進(jìn)行預(yù)處理，并返回一個(gè)語(yǔ)句對(duì)象。

預(yù)處理一條 SQL 語(yǔ)句，以便 PDOStatement::execute() 方法執(zhí)行。該 SQL 語(yǔ)句可以包含 0 或更多個(gè)命名參數(shù)(:name)或問(wèn)號(hào)參數(shù)(?)，這些參數(shù)的真實(shí)值在語(yǔ)句執(zhí)行的時(shí)候會(huì)被替換掉。使用這些參數(shù)綁定所有的用戶輸入的數(shù)據(jù)，不要在查詢中直接包含用戶輸入的數(shù)據(jù)。

返回值：

如果數(shù)據(jù)庫(kù)服務(wù)器成功地預(yù)處理了該語(yǔ)句，PDO::prepare() 將會(huì)返回一個(gè) PDOStatement 對(duì)象；否則，返回 false 或 拋出 PDOException(依 error handling 而定)。

模擬的預(yù)處理語(yǔ)句并沒有與數(shù)據(jù)庫(kù)服務(wù)器進(jìn)行通信，所以PDO::prepare()并沒有檢查該語(yǔ)句。

PDOStatement::bindParam

Paste_Image.png

原來(lái) PDO 官方手冊(cè)的簡(jiǎn)要描述的描述順序有點(diǎn)怪怪的，并且后面的詳細(xì)描述也不一致。所以這里把簡(jiǎn)要描述跟詳細(xì)描述中的描述順序統(tǒng)一一下。

Binds the specified variable name to a parameter.

綁定 指定的變量名(只能是 $name 的形式)到 一個(gè)參數(shù)(:name 或 ?參數(shù) ，可以是 :name 或 從1 開始的索引 的形式)。

綁定 一個(gè) PHP 變量 到 預(yù)處理語(yǔ)句中對(duì)應(yīng)的命名占位符或問(wèn)號(hào)占位符。

與 PDOStatement::bindValue() 不同的是：PDOStatement::bindParam() 中的變量是作為引用而綁定的，并且只有在調(diào)用 PDOStatement::execute() 的時(shí)候才會(huì)讀取這個(gè)變量的值。

Note we used bindValue and not bindParam. Trying to bind a parameter by reference will generate a Fatal Error and this cannot be caught by PDOException either.

但如果需要循環(huán)執(zhí)行預(yù)處理語(yǔ)句，最好使用bindParam，具體原因見對(duì)應(yīng)的章節(jié)：Executing prepared statements in a loop。

返回值：

成功則返回 true，失敗則返回 false

例如：

/* Execute a prepared statement by binding PHP variables */

$calories = 150;

$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories

FROM fruit

WHERE calories < :calories AND colour = :colour');

$sth->bindParam(':calories', $calories, PDO::PARAM_INT);

$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);

$sth->execute();

/* Execute a prepared statement by binding PHP variables */

$calories = 150;

$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories

FROM fruit

WHERE calories < ? AND colour = ?');

$sth->bindParam(1, $calories, PDO::PARAM_INT);

$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);

$sth->execute();

PDOStatement::bindValue

Paste_Image.png

Binds a value to a parameter.

綁定 一個(gè)值(可以是 $name 或 'Jack' 的形式) 到 一個(gè)參數(shù)(:name 或 ?參數(shù) ，可以是 :name 或 從1 開始的索引 的形式)。

綁定 一個(gè)值 到 預(yù)處理語(yǔ)句中對(duì)應(yīng)的命名占位符或問(wèn)號(hào)占位符。

返回值：

成功則返回 true，失敗則返回 false

例如：

$stm->bindValue(':name',$name);

$stm->bindValue(':name','Jack');

PDOStatement::execute

Paste_Image.png

Executes a prepared statement.

執(zhí)行一條 經(jīng)過(guò)預(yù)處理的語(yǔ)句。

如果預(yù)處理語(yǔ)句中包含占位符，則必須執(zhí)行以下兩點(diǎn)之一：

調(diào)用PDOStatement::bindParam() 或 PDOStatement::bindValue() 把變量或值綁定到占位符上。

或 傳入一個(gè)數(shù)組

1. 參數(shù)：

$input_parameters：一個(gè)數(shù)組。數(shù)組的元素?cái)?shù)量 應(yīng)該與 需要執(zhí)行的 SQL 語(yǔ)句中占位符數(shù)量 相等。

所有的值作為 PDO::PARAM_STR 處理。

不能綁定多個(gè)值到一個(gè)單獨(dú)的參數(shù)；比如，不能綁定兩個(gè)值到 IN()子句中一個(gè)單獨(dú)的命名占位符。

綁定值的數(shù)量不能超過(guò)指定的數(shù)量。如果在 $input_parameters 的鍵名數(shù)量 比 PDO::prepare() 中的 SQL 語(yǔ)句中指定的參數(shù)的數(shù)量還要多，則該語(yǔ)句將會(huì)失敗并發(fā)出一個(gè)錯(cuò)誤。

$input_parameters 中的鍵名 必須和 SQL 中聲明的 相匹配。在 PHP 5.2.0 之前，這是被忽略的。

2. 返回值：

成功則返回 true，失敗則返回 false

例如：

/* Execute a prepared statement by passing an array of insert values */

$calories = 150;

$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories

FROM fruit

WHERE calories < :calories AND colour = :colour');

$sth->execute(array(':calories' => $calories, ':colour' => $colour));

/* Execute a prepared statement by passing an array of insert values */

$calories = 150;

$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories

FROM fruit

WHERE calories < ? AND colour = ?');

$sth->execute(array($calories, $colour));

Preparing Statements using SQL functions

You may ask how do you use SQL functions with prepared statements. I've seen people try to bind functions into placeholders like so:

//THIS WILL NOT WORK!

$time = 'NOW()';

$name = 'BOB';

$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(?, ?)");

$stmt->execute(array($time, $name));

This does not work, you need to put the function in the query as normal:

$name = 'BOB';

$stmt = $db->prepare("INSERT INTO table(`time`, `name`) VALUES(NOW(), ?)");

$stmt->execute(array($name));

You can bind arguments into SQL functions however:

$name = 'BOB';

$password = 'badpass';

$stmt = $db->prepare("INSERT INTO table(`hexvalue`, `password`) VALUES(HEX(?), PASSWORD(?))");

$stmt->execute(array($name, $password));

Also note that this does NOT work for LIKE statements:

//THIS DOES NOT WORK

$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE %?%");

$stmt->bindParam(1, $search, PDO::PARAM_STR);

$stmt->execute();

So do this instead:

$stmt = $db->prepare("SELECT field FROM table WHERE field LIKE ?");

$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);

$stmt->execute();

Note we used bindValue and not bindParam. Trying to bind a parameter by reference will generate a Fatal Error and this cannot be caught by PDOException either.

但如果需要循環(huán)執(zhí)行預(yù)處理語(yǔ)句，最好使用bindParam，具體原因見對(duì)應(yīng)的章節(jié)：Executing prepared statements in a loop。

Executing prepared statements in a loop

Prepared statements excel in being called multiple times in a row with different values.

Because the sql statement gets compiled first, it can be called multiple times in a row with different arguments, and you'll get a big speed increase vs calling mysql_query over and over again!

Typically this is done by binding parameters with bindParam. bindParam is much like bindValue except instead of binding the value of a variable, it binds the variable itself, so that if the variable changes, it will be read at the time of execute.

$values = array('bob', 'alice', 'lisa', 'john');

$name = '';

$stmt = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");

$stmt->bindParam(':name', $name, PDO::PARAM_STR);

foreach($values as $name) {

$stmt->execute();

}

Transactions

Here's an example of using transactions in PDO: (note that calling beginTransaction() turns off auto commit automatically):

try {

$db->beginTransaction();

$db->exec("SOME QUERY");

$stmt = $db->prepare("SOME OTHER QUERY?");

$stmt->execute(array($value));

$stmt = $db->prepare("YET ANOTHER QUERY??");

$stmt->execute(array($value2, $value3));

$db->commit();

} catch(PDOException $ex) {

//Something went wrong rollback!

$db->rollBack();

echo $ex->getMessage();

}

總結(jié)

以上是生活随笔為你收集整理的php pdo 查询语句,PDO：预处理语句（参数化查询）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。