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