Syntax error driving newcomer to SQL crazy

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by erniehan, May 13, 2012.

  1. erniehan

    erniehan New Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    3
    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>";*/
     
  2. descalzo

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    Might be the problem.

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

    Skizzerz Contributors Staff Member Contributors

    Messages:
    2,929
    Likes Received:
    117
    Trophy Points:
    63
    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.
     

Share This Page