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

Question

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

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

It is not inserting data when the create table is not remove it saysnsyntax error — User99, Nov 28 '20 at 02:46
Please update your question with the code that you are actually running with problem. Also write the exactly error message. — Felippe Duarte, Nov 28 '20 at 02:48
You have a typo in your insert statement. It is VALUES not VALUE. See https://www.mysqltutorial.org/mysql-insert-statement.aspx — mikeroq, Nov 28 '20 at 03:00
Or you run a query for each statement (create, then insert), or do some trick like what people do in this answer: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Felippe Duarte, Nov 28 '20 at 03:00
I am not familiar with Maria DB or your SQL version. Please look on how to provide a minimal reproducible example. But usually the `create table` command requires the `select` statement after the name of the table and before listing the variables. — LuizZ, Nov 28 '20 at 10:18
[Don't paste images of code](https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors) — Jonathan Hall, Nov 28 '20 at 17:03
i'm asking if i can do the code in the picture in ```$sql``` — User99, Nov 28 '20 at 17:06
Does this answer your question? [How to create a table and insert data into it?](https://stackoverflow.com/questions/65053190/how-to-create-a-table-and-insert-data-into-it) — Dharman, Nov 28 '20 at 18:56

score 2 · Accepted Answer · answered Nov 28 '20 at 03:22

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.

I jist found out about multi_query($sql) i'm gonna try that later thanks to all who answer my question! — User99, Nov 28 '20 at 03:43
Please do not encourage the use of `mysqli_multi_query()`. Clearly, there is no reason to use it in this scenario. — Dharman, Nov 28 '20 at 19:33

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

1 Answers1