Associative arrays and binding parameters

Question

I've gotten a little confused with the PDO::prepare functions.

I have something like this

array('user_email'=>'[email protected]','user_pass'=>'password')

and i'd like to translate it into something like this

INSERT INTO user_info (user_email, user_pass) VALUES ([email protected], password)

using parameterized queries with PDO (or mysqli, I'm open to suggestions). Another idea -

array('uid'=>'10', 'first_name'=>'robert', 'last_name'=>'jones')
array("email", "number")

into

SELECT email, number FROM t1 WHERE uid=10 AND first_name=robert AND last_name=jones

I know the answer lies somewhere with PDO::prepare and call_user_func_array, but I've gotten really confused on how the latter function works, and would appreciate an explanation.

Thanks a ton!

Answer 1

I'm confused, and maybe you are too. Here is a simple example:

$sth = $dbh->prepare('SELECT * FROM table WHERE id = ? AND date = ?');
$sth->execute(array(150, '2009-04-04'));
$data = $sth->fetchAll();

Or:

$sth = $dbh->prepare("INSERT table VALUES(:foo, :bar)");
$sth->bindParam(":foo", $foo);
$sth->bindParam(":bar", $bar);

Or:

$sth = $dbh->prepare("INSERT INTO user_info (user_email, user_pass) VALUES (:email, :pass)");
$sth->execute(array(':email' => '[email protected]', ':pass' => '1234'));

Hope this helps!

Answer 2

PDOStatement::execute() works with parameters markers, so you have to construct query before calling PDO::prepare().

Answer 3

You don't have to use call_user_func_array(). PDOStatement::execute() takes associative arrays by default.

$stmt = $pdo->prepare("SELECT fld FROM tbl WHERE fld=:parameter1 AND fld2=:parameter2");
$stmt->execute(array(":parameter1" => "value1", ":parameter2" => "value2"));
...

http://se.php.net/manual/en/pdo.prepare.php