CODEWITHSUNDEEP
phpmysqlpdosyntax-errorprepared-statement
asdf1234
asdf1234

Reputation: 127

php prepared statement SQLSTATE[42000]: Syntax error or access violation: 1064

I am trying to change to prepared statements but keep getting the following error:

Warning: PDO::query(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':fname AND

Here is the code im using:

$street = 'astreet';
$lname = 'alname';
$fname = 'afname';
$list = '1,2,3,4,5';

   $var = 'admin';  

   $db = new PDO("mysql:dbname=customers;host=localhost",$var,$var);

   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$sql = "SELECT * FROM `table` WHERE `id` in ($list) AND `first_name` LIKE :fname AND `last_name` LIKE :lname AND `street_name` LIKE :street";

   $stmt = $db->prepare($sql);

   $stmt->execute(array(':street' => $street));
   $stmt->execute(array(':fname' => $fname));
   $stmt->execute(array(':lname' => $lname));

   $result = $db->query($sql);



foreach ($result as $row) {

        echo $row['post_code'];

              }

if I run the query as normal - not a prepared staement, it works fine - its only when I start adding the :variables to the query I get the error

Is there any code missing/incorrect?

Thanks

Upvotes: 1

Views: 2741

Answers (2)

tswaehn
tswaehn

Reputation: 387

As far as I know you have to escape strings in SQL. So I would replace

$sql = "SELECT * FROM `table` WHERE `id` in ($list) AND `first_name` LIKE :fname AND `last_name` LIKE :lname AND `street_name` LIKE :street";

by

$sql = "SELECT * FROM `table` WHERE `id` in ($list) AND `first_name` LIKE ':fname' AND `last_name` LIKE ':lname' AND `street_name` LIKE ':street' ";

Upvotes: 0

andrewsi
andrewsi

Reputation: 10732

$stmt = $db->prepare($sql);

$stmt->execute(array(':street' => $street));
$stmt->execute(array(':fname' => $fname));
$stmt->execute(array(':lname' => $lname));

You're running the same query three times, with a different parameter each time. You need to run the query as so:

$stmt = $db->prepare($sql);

$stmt->execute(array(':street' => $street, ':fname' => $fname, ':lname' => $lname));

And pass all three parameters in one go.

Edited to add:

You can also try binding the parameters explicitly:

$stmt = $db->prepare($sql);

$stmt->bindParam(':street', $street);
$stmt->bindParam(':fname', $fname);
$stmt->bindParam(':lname', $lname);

$stmt->execute();

Or adding them as an array:

$sql = "SELECT * FROM `table` WHERE `id` in ($list) AND `first_name` LIKE ? AND `last_name` LIKE ? AND `street_name` LIKE ?";
$stmt = $db->prepare($sql);

$stmt->execute(array($fname, $lname, $street));

Upvotes: 1

Related Questions