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


Question

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

I would like to use a parameterized SELECT query, if possible, to return the ID in a table where the name field matches the parameter. This should return one ID because it will be unique.

I would then like to use that ID for an INSERT into another table, so I will need to determine if it was successful or not.

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

1
85
3/8/2016 4:31:21 AM

Accepted Answer

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);
158
4/20/2009 9:15:45 AM

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon