Syntax error driving newcomer to SQL crazy

[erniehan]

I have entered the first code set below into PhpMyAdmin and it works. Using PHP and running it gives me a syntax error. HELP! This is my first exposure to SQL. I am a retired assembly language and C++ programmer doing this for fun! If I run each SQL statement separately like in second code segment, it works great. What simple little thing have I missed? :biggrin:

[FONT=&quot][/FONT]FIRST CODE SET

$sqlquery = <<< HERE

DROP TABLE IF EXISTS adminpw;
CREATE TABLE adminpw
(pwd INT AUTO_INCREMENT PRIMARY KEY,
adminid CHAR(20),
adpw CHAR(20)
);
INSERT adminpw VALUES (null, 'admin', 'admin');
INSERT adminpw VALUES (null, 'dog', 'tiger');
INSERT adminpw VALUES (null, 'horse', 'cat');

HERE;

SECOND CODE SET:


/* Delete the Admin Password Table and Rebuild it */
$sqlquery = "DROP TABLE adminpw;";
$result = mysql_query($sqlquery, $conn) or die(mysql_error());


$sqlquery = "CREATE TABLE adminpw
(pwd INT(10) AUTO_INCREMENT PRIMARY KEY,
adminid CHAR(20),
adpw CHAR(20)
);";
$result = mysql_query($sqlquery, $conn) or die(mysql_error());

$sqlquery = "INSERT adminpw VALUES (null, 'rabbit', 'hare');";
$result = mysql_query($sqlquery, $conn) or die(mysql_error());
/*print "<p>a $result</p>";*/

$sqlquery = "INSERT adminpw VALUES (null, 'mouse', 'cat');";
$result = mysql_query($sqlquery, $conn) or die(mysql_error());
/*print "<p>b $result</p>";*/


$sqlquery = "INSERT adminpw VALUES (null, 'bird', 'dog');";
$result = mysql_query($sqlquery, $conn) or die(mysql_error());
/*print "<p>c $result</p>";*/

 

[descalzo]

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Might be the problem.

Also, you should use PDO instead of the mysql_ interface.

 

[Skizzerz]

That is indeed the problem. None of PHP's MySQL libraries support running multiple queries at once. You will need to break each query up into separate strings and run them separately. Additionally, as descalzo said, the PDO interface to MySQL is more recommended than the old (and deprecated) mysql_* functions (mysqli_* functions are another alternative to PDO). PDO provides an object-oriented interface to running database queries and adding inherent security to your MySQL calls. See here for a tutorial that details a bit more of what I'v e mentioned as well as providing some practical code.