How to create a table and insert data into it?

Question

What I'm trying to do is in the $sql this is where I'm going to code the SQL commands

    $connect = new mysqli($servername, $username, $password, $database);
    
    if ($connect -> connect_error) {
        die("Unable to Connect : " . connect_error);
    }
    
    $sql = /*"CREATE TABLE student (
        student_id INT,
        name VARCHAR(20),
        major VARCHAR(20),
        PRIMARY KEY(student_id)
    ); */
    
    "INSERT INTO student VALUE(3, 'joseph', 'education');";
    
    if ($connect -> query($sql) === TRUE) {
        echo "New Table Created! <br><br>";
    }
    else {
        echo "Error : " . $sql . " <br><br>" . $connect -> error . "<br><br>";
    }
    
    echo "Connected Successfully!";

This is the output when I removed the create table. The inserted data is successful

New Table Created!

Connected Successfully!

This the output when I did not 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(3, 'joseph', 'education');

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(3, 'joseph', 'education')' at line 8

Connected Successfully!

What function do I need to use to put in the $sql the SQL commands like this? Is it even possible? Is this how SQL works?

$sql = "CREATE TABLE student (
        student_id INT,
        name VARCHAR(20),
        major VARCHAR(20),
        PRIMARY KEY(student_id)
    );
    
    INSERT INTO student VALUE(3, 'joseph', 'education');"

You need two separate calls to query() execute two separate queries. — ADyson
– ADyson, Commented Nov 28, 2020 at 18:37
Because normally in a web application you create the database in advance and the application just reads and writes to existing tables. There's rarely a reason for an application to create tables. Sometimes when we see people trying to do that in their code it's a symptom of a flawed database design. Sounds like maybe you haven't got as far as that though — ADyson
– ADyson, Commented Nov 28, 2020 at 18:44
im jus t starting in php on how to connect and insert data in mysql so im confuse about this things — User99
– User99, Commented Nov 28, 2020 at 18:48

Dharman · Accepted Answer · 2020-11-28 18:50:13Z

3

You need to do it in two steps. First, prepare a statement with the CREATE TABLE and then prepare the second statement with INSERT.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connect = new mysqli($servername, $username, $password, $database);
$connect->set_charset('utf8mb4'); // always set the charset

$sql = "CREATE TABLE student (
    student_id INT,
    name VARCHAR(20),
    major VARCHAR(20),
    PRIMARY KEY(student_id)
)";
$stmt = $connect->prepare($sql);
$stmt->execute();

$stmt = $connect->prepare("INSERT INTO student VALUE(3, 'joseph', 'education')");
$stmt->execute();

answered Nov 28, 2020 at 18:50

Dharman♦

34k27 gold badges106 silver badges157 bronze badges

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

Collectives™ on Stack Overflow

How to create a table and insert data into it?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related