Why does creating a table and then inserting multiple values return an error?

Question

So I'm trying to create a table and then insert multiple values into it, like so:

        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "someDbName";
        $conn = mysqli_connect($servername, $username, $password, $dbname);
                if (!$conn) {
                    die("Connection failed: " . mysqli_connect_error());
                }
                    $sql = "CREATE TABLE IF NOT EXISTS someTableName(
                    someID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    someVar1 VARCHAR(30) NOT NULL,
                    someVar2 INT NOT NULL);
                    INSERT INTO someTableName (someVar1 , someVar2 ) VALUES ('someString1', someInteger1),
                    ('someString2',someInteger2);";

Where the someInteger bits are, of course, integers. And then:

            $sql = mysqli_real_escape_string($conn, $sql);    
            if (mysqli_multi_query($conn, $sql)) {
                dtconsole("Tables populated successfully");
            } else {
                dtconsole("Error creating table: " . mysqli_error($conn));
            }

With the dtconsole function there just to output to console to help me debug.

function dtconsole($data){
        $output=$data;
        if(is_array($output)){
            $output=implode(',',$output);
        }
        echo '<script>console.log("'.$output.'");</script>';
    }

Every time I try to run this, it returns the following error:

Error creating table: 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 ' someID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ' at line 1

And I can't for the life of me see what I'm doing wrong.

Yep, tested it on localhost/phpmyadmin and it worked just fine. — K. Medea
– K. Medea, Commented Oct 24, 2018 at 8:41

Nick · Accepted Answer · 2018-10-24 08:43:15Z

1

Your problem is that you are calling mysqli_real_escape_string on your entire query, instead of just the values you are inserting. As a result it is converting the CR-LF in your $sql string into \r\n, which the MySQL parser cannot interpret. You should be doing something like this:

$someString1 = mysqli_real_escape_string($conn, 'someString1'); 
$someString2 = mysqli_real_escape_string($conn, 'someString2');
$sql = "CREATE TABLE IF NOT EXISTS someTableName(
        someID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        someVar1 VARCHAR(30) NOT NULL,
        someVar2 INT NOT NULL);
        INSERT INTO someTableName (someVar1 , someVar2 ) VALUES ($someString1, someInteger1),
        ($someString2,someInteger2);";
if (mysqli_multi_query($conn, $sql)) { 
    ...

answered Oct 24, 2018 at 8:43

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

K. Medea Over a year ago

Sounds good, yeah - I'll use this lots down the line. Kind of a weird thing, though - the error persists even when I remove the mysqli_real_escape_string() bit and just hardcode in some values. Any idea why that should be?

K. Medea Over a year ago

Nevermind, there were some weird bits of code mixed in. Thanks for the lovely answer, mate! Consider me humbled and thankful.

Nick Over a year ago

@K.Medea thanks. Consider this a short-term solution though - longer term you should really look into prepared statements to prevent SQL injection.

Collectives™ on Stack Overflow

Why does creating a table and then inserting multiple values return an error?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related