Using a passed variable in a query using PDO

Question

I am trying to take a variable passed from another page and use it in a PDO query. The variable is the date the record was added and I'm trying to return all the newer records. Do I use $_POST for this in PDO?

<?php
require_once('globals.php');

$date_added = $_POST['date_added'];

$sth = $dbh->prepare("SELECT * FROM games WHERE date_added > $date_added");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);


?>

Does it work? If not: what's the error you are seeing? One issue here is that you are taking user data directly in the query - use prepared statements or $dbh->quote() — johannes, Oct 26 '11 at 14:11
Here's the error: Fatal error: Call to a member function prepare() on a non-object. — Don S, Oct 26 '11 at 14:19
@DonS You never had a successful database connection to begin with. Did you actually call `new PDO()` somewhere, like in `globals.php`? — Michael Berkowski, Oct 26 '11 at 14:21
@DonS Even if it isn't a public form you should be binding `$date_added` in the prepared statement. — Michael Berkowski, Oct 26 '11 at 14:22
@johannes what about non-user data? is it OK to put in in the query as is? — Your Common Sense, Oct 26 '11 at 14:26
@Michael, no I did not call new PDO(). I'll try that. How do I bind the variable? — Don S, Oct 26 '11 at 14:26

Michael Berkowski · Accepted Answer · 2011-10-26T14:38:54.800

You need to actually establish a connection to the database by creating a new PDO object into $dbh. The code below assumes a database user and password as $dbusername, $dbpassword and database named $nameofdb.

$date_added is replaced in the prepare() call with a parameter :dateadded, then passed via an array to the execute() call.

Please read the documentation on both PDO::__construct() and PDO::execute()

<?php
require_once('globals.php');

// Connect to MySQL via PDO
try {
    $dbh = new PDO("mysql:dbname=$nameofdb;host=localhost", $dbusername, $dbpassword);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$date_added = $_POST['date_added'];

// Replace `$date_added` with a parameter `:dateadded`
$sth = $dbh->prepare("SELECT * FROM games WHERE date_added > :dateadded");
// bind $date_added and pass it into the execute() call inside an array
$sth->execute(array('dateadded'=>$date_added));

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);

?>

Thanks Michael! That works perfectly. Thanks for the PDO links as well. — Don S, Oct 26 '11 at 15:09
If I wanted to json the result instead of printing it would the code be: '$json=json_encode($result);' instead of: 'print_r($result);' For that last line? — Don S, Oct 26 '11 at 15:13

Using a passed variable in a query using PDO

1 Answers1

Linked