0

I tried using PDO with two insert query and i get this error. I already check each of the query directly in mysql db and its working perfectly. i'm not sure what is wrong with my code

<?php 

define('MYSQL_USER', 'root');
define('MYSQL_PASSWORD', '');
define('MYSQL_HOST', 'localhost');
define('MYSQL_DATABASE', 'mydb');

$pdoOptions = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

$pdo = new PDO(
    "mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DATABASE, //DSN
    MYSQL_USER, //Username
    MYSQL_PASSWORD, //Password
    $pdoOptions //Options
);

$sql = "INSERT INTO customer (cust_name) VALUES (:cust_name); ";
$sql .= "INSERT INTO reference (ref_name) VALUES (:ref_name) ";

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

$stmt->bindParam(':cust_name' , "test");
$stmt->bindParam(':ref_name' , "help");

if ($stmt) {    
    echo "Success";
} else {  
    print_r($stmt->errorInfo());    
}
?>

this is the error that i get

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO reference (ref_name) VALUES (?)' at line 1'

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
busukayam
  • 1
  • 2
  • PDO doesn't support multiple queries. – Funk Forty Niner Feb 06 '18 at 13:40
  • **Never** get your web app to login to the database as root. Root can do whatever it likes. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. – ADyson Feb 06 '18 at 13:43
  • P.S. To solve your problem you simply need to execute it as two separate statements. A prepared statement is just that - a statement. Not two statements. P.S. Wrap in a transaction if you require integrity (i.e. both must succeed, or else neither must succeed) – ADyson Feb 06 '18 at 13:44
  • Relevant post https://stackoverflow.com/q/6346674/1415724 and https://stackoverflow.com/questions/40359474/executing-multiple-queries-using-pdo – Funk Forty Niner Feb 06 '18 at 13:45
  • I had the same problem. Setting `PDO::ATTR_EMULATE_PREPARES` to `true` fixed this issue for me. You can also delete it then, since `true` is the default. – Scriptman Feb 06 '18 at 13:48
  • thanks guys. @ADyson are correct. i just execute in two queries and it work! Many thanks .btw the password is just for my localhost db. not any production db. anyway thanks for remind – busukayam Feb 06 '18 at 14:22
  • I just finished edit my code. Thanks Funk Forty Niner and Scriptman – busukayam Feb 06 '18 at 14:46

0 Answers0