如何正确地将 PDO 对象用于参数化 SELECT 查询

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 PDOException if any of the queries fail - No need to check explicitly. To turn on exceptions, call this just after you've created the $db object:

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

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.

$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();

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 $dbh is PDO db connecter, and based on id from table users we've get the username using fetch();

I hope this help someone, Enjoy!

You can use the bindParam or bindValue methods 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.

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 rows remove the LIMIT 1 and change the fetch method into 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
}

Method 1:USE PDO query method

$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

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

//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 />";
}