Warm tip: This article is reproduced from serverfault.com, please click

is it possible to create SQL table and insert data at the same time?

发布于 2020-11-28 02:10:28

when i removed the create table it is working, but is it possible to do this on code?

enter image description here

here is the code

    $sql = "CREATE TABLE student (
        student_id INT,
        name VARCHAR(20),
        major VARCHAR(20),
        PRIMARY KEY(student_id)
    )
    
    INSERT INTO student VALUE(1, 'Jack', 'biology')";
    
    if ($connect -> query($sql) === TRUE) {
        echo "New Table Created! <br><br>";
    }
    else {
        echo "Error : " . $sql . " <br><br>" . $connect -> error . "<br><br>";
    }
    
    echo "Connected Successfully!";

Here is the error when i didn't removed the create table

Error : CREATE TABLE student ( student_id INT, name VARCHAR(20), major VARCHAR(20), PRIMARY KEY(student_id) ); INSERT INTO student VALUE(2, 'kate', 'sociology')

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 student VALUE(2, 'kate', 'sociology')' at line 8

Questioner
User99
Viewed
0
JM7 2020-11-28 11:22:07

You have 2 typos in your code:

  • Missing ";" between the CREATE and INSERT statements.

  • INSERT statement uses VALUES, not VALUE.

     $sql = "CREATE TABLE student (
         student_id INT,
         name VARCHAR(20),
         major VARCHAR(20),
         PRIMARY KEY(student_id)
     );
    
     INSERT INTO student VALUES (1, 'Jack', 'biology')";
    

Aside from that, check the official docs for mysqli Multiple statements and mysqli_multi_query() since as mentioned in the first document:

The API functions mysqli_query() and mysqli_real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks.

Or you can also change your code to run each query individually.