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.
PDO::query() prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.
First, connect to a MySQL database. Check it out the connecting to MySQL database using PDO tutorial for detail information. Then, construct a SELECT statement and execute it by using the query() method of the PDO object. The query() method of the PDO object returns a PDOStatement object, or false on failure.
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();
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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With