php 如何正确使用 PDO 对象进行参数化 SELECT 查询

How can I properly use a PDO object for a parameterized SELECT query

提问by Joe Phillips

I've tried following the PHP.net instructions for doing SELECTqueries but I am not sure the best way to go about doing this.

我已经尝试按照 PHP.net 说明进行SELECT查询，但我不确定执行此操作的最佳方法。

I would like to use a parameterized SELECTquery, if possible, to return the IDin a table where the namefield matches the parameter. This should return one IDbecause it will be unique.

SELECT如果可能，我想使用参数化查询ID在name字段与参数匹配的表中返回。这应该返回一个，ID因为它将是唯一的。

I would then like to use that IDfor an INSERTinto another table, so I will need to determine if it was successful or not.

然后我想将它ID用于INSERT另一个表，所以我需要确定它是否成功。

I also read that you can prepare the queries for reuse but I wasn't sure how this helps.

我还读到您可以准备查询以供重用，但我不确定这有什么帮助。

回答by troelskn

You select data like this:

你选择这样的数据：

$db = new PDO("...");
$statement = $db->prepare("select id from some_table where name = :name");
$statement->execute(array(':name' => "Jimbo"));
$row = $statement->fetch(); // Use fetchAll() if you want all results, or just iterate over the statement, since it implements Iterator

You insert in the same way:

你以同样的方式插入：

$statement = $db->prepare("insert into some_other_table (some_id) values (:some_id)");
$statement->execute(array(':some_id' => $row['id']));

I recommend that you configure PDO to throw exceptions upon error. You would then get a PDOExceptionif any of the queries fail - No need to check explicitly. To turn on exceptions, call this just after you've created the $dbobject:

我建议您将 PDO 配置为在出错时抛出异常。PDOException如果任何查询失败，您将得到一个- 无需明确检查。要打开异常，请在创建$db对象后立即调用：

$db = new PDO("...");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

回答by SmashCode

I've been working with PDO lately and the answer above is completely right, but I just wanted to document that the following works as well.

我最近一直在使用 PDO，上面的答案是完全正确的，但我只是想证明以下内容也有效。

$nametosearch = "Tobias";
$conn = new PDO("server", "username", "password");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $conn->prepare("SELECT `id` from `tablename` WHERE `name` = :name");
$sth->bindParam(':name', $nametosearch);
// Or sth->bindParam(':name', $_POST['namefromform']); depending on application
$sth->execute();

回答by Gilly

You can use the bindParamor bindValuemethods to help prepare your statement. It makes things more clear on first sight instead of doing $check->execute(array(':name' => $name));Especially if you are binding multiple values/variables.

您可以使用bindParam或bindValue方法来帮助准备您的陈述。它使事情一见钟情而不是做，$check->execute(array(':name' => $name));特别是如果您绑定多个值/变量。

Check the clear, easy to read example below:

检查下面清晰易读的示例：

$q = $db->prepare("SELECT id FROM table WHERE forename = :forename and surname = :surname LIMIT 1");
$q->bindValue(':forename', 'Joe');
$q->bindValue(':surname',  'Bloggs');
$q->execute();

if ($q->rowCount() > 0){
    $check = $q->fetch(PDO::FETCH_ASSOC);
    $row_id = $check['id'];
    // do something
}

If you are expecting multiple rowsremove the LIMIT 1and change the fetch method into fetchAll:

如果您期望多行，请删除LIMIT 1并将 fetch 方法更改为fetchAll：

$q = $db->prepare("SELECT id FROM table WHERE forename = :forename and surname = :surname");// removed limit 1
$q->bindValue(':forename', 'Joe');
$q->bindValue(':surname',  'Bloggs');
$q->execute();

if ($q->rowCount() > 0){
    $check = $q->fetchAll(PDO::FETCH_ASSOC);
    //$check will now hold an array of returned rows. 
    //let's say we need the second result, i.e. index of 1
    $row_id = $check[1]['id']; 
    // do something
}

回答by Domuta Marcel

A litle bit complete answer is here with all ready for use:

一点点完整的答案在这里都可以使用：

    $sql = "SELECT `username` FROM `users` WHERE `id` = :id";
    $q = $dbh->prepare($sql);
    $q->execute(array(':id' => "4"));
    $done= $q->fetch();

 echo $done[0];

Here $dbhis PDO db connecter, and based on idfrom table userswe've get the usernameusing fetch();

这$dbh是 PDO db 连接器，基于id表中users我们得到了username使用fetch();

I hope this help someone, Enjoy!

我希望这对某人有所帮助，享受！

回答by Sudhir

Method 1:USE PDO query method

方法一：USE PDO查询方式

$stmt = $db->query('SELECT id FROM Employee where name ="'.$name.'"');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Getting Row Count

获取行数

$stmt = $db->query('SELECT id FROM Employee where name ="'.$name.'"');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Method 2: Statements With Parameters

方法二：带参数的语句

$stmt = $db->prepare("SELECT id FROM Employee WHERE name=?");
$stmt->execute(array($name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Method 3:Bind parameters

方法三：绑定参数

$stmt = $db->prepare("SELECT id FROM Employee WHERE name=?");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

**bind with named parameters**
$stmt = $db->prepare("SELECT id FROM Employee WHERE name=:name");
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

or
$stmt = $db->prepare("SELECT id FROM Employee WHERE name=:name");
$stmt->execute(array(':name' => $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Want to know more look at this link

想知道更多看这个链接

回答by Shiv Singh

if you are using inline coding in single page and not using oops than go with this full example, it will sure help

如果您在单页中使用内联编码而不使用 oops 而不是使用这个完整示例，它肯定会有所帮助

//connect to the db
$dbh = new PDO('mysql:host=localhost;dbname=mydb', dbuser, dbpw); 

//build the query
$query="SELECT field1, field2
FROM ubertable
WHERE field1 > 6969";

//execute the query
$data = $dbh->query($query);
//convert result resource to array
$result = $data->fetchAll(PDO::FETCH_ASSOC);

//view the entire array (for testing)
print_r($result);

//display array elements
foreach($result as $output) {
echo output[field1] . " " . output[field1] . "<br />";
}