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.



Answers

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

By : Sudhir


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!



I wanted to comment on Gillian Lo Wongs answer but I don't have enough reputation to do so. Using rowCount() to count the rows returned by a SELECT statement is not portable and may not behave as you seem to be expecting it to. Check the php manual here PDOStatement::rowCount Just got bitten by this myself



This video can help you solving your question :)
By: admin