How can I use the PDO object correctly for a parameterized SELECT query

Question

I tried following the PHP.net instructions to execute SELECT queries, but I'm not sure if this is the best way to do this.

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

I would like to use this ID for INSERT in another table, so I will need to determine if this was successful or not.

I also read that you can prepare queries for reuse, but I was not sure how this helps.

Answer 1

You select the following data:

 $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 the same:

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

I recommend setting up PDO to throw exceptions on error. You will then receive a PDOException if any of the requests fail. No need to explicitly specify. To enable exceptions, call this immediately after creating the $db object:

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

Answer 2

I have been working with PDO recently, and the answer above is completely correct, but I just wanted to write 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(); 

Answer 3

You can use the bindParam or bindValue methods to help prepare the report. This makes things clearer at first glance instead of doing $check->execute(array(':name' => $name)); Especially if you bind multiple values ​​/ variables.

Check out the 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 expect multiple lines to remove LIMIT 1 and change the selection method to 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 say we need the second result, ie index of 1 $row_id = $check[1]['id']; // do something } 

Answer 4

The answer to the full litle bit is ready for everyone here:

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

I hope this helps someone. Enjoy!

Answer 5

Method 1: USE PDO Request Method

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

Getting the number of rows

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

Method 2: Findings with Parameters

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

Method 3: Binding Options

 $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 about this link

Answer 6

if you use inline coding on one page and don't use oops, than follow this full example, it will definitely 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 />"; } 

Answer 7

exactly after the fit line add below code

 echo $statement->queryString;