PHP | Inserting into MySQL database

Prerequisites : MySQL introduction, Creating MySQL database
INSERT INTO statement is used to insert new rows in a database table. Let’s see the syntax how to insert into table, considering database already exists.

SYNTAX :

INSERT INTO TABLE_NAME (column1, column2, column3, ... columnN) 
VALUES (value1, value2, value3, ...valueN);

Here, column1, column2, column3, …columnN are the names of the columns in the table into which you want to insert the data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

So to create a SQL query using the INSERT INTO statement with appropriate values, here’s an example, which will insert a new row to the newDB table by specifying values for the first_name, last_name and email fields.

  1. Creating table using MySQLi Object-oriented Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php
     $mysqli = new mysqli("localhost", "root", "", "newdb");
      
    if ($mysqli == = false) {
        die("ERROR: Could not connect. ".$mysqli->connect_error);
    }
      
    $sql = "INSERT INTO mytable (first_name, last_name, age)
                  VALUES('ram', 'singh', '25') ";
        if ($mysqli->query($sql) == = true)
    {
        echo "Records inserted successfully.";
    }
    else
    {
        echo "ERROR: Could not able to execute $sql. "
               .$mysqli->error;
    }
      
    // Close connection
    $mysqli->close();
    ? >

    chevron_right

    
    

    Output :

  2. Creating table using MySQLi Procedural Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php 
    $link = mysqli_connect("localhost", "root", "", "newdb");
      
    if ($link == = false) {
        die("ERROR: Could not connect. ".mysqli_connect_error());
    }
      
    $sql = "INSERT INTO mytable (first_name, last_name, age) 
              VALUES('ram', 'singh', '25') ";
        if (mysqli_query($link, $sql))
    {
        echo "Records inserted successfully.";
    }
    else
    {
        echo "ERROR: Could not able to execute $sql. "
            .mysqli_error($link);
    }
      
    mysqli_close($link);
    ? >

    chevron_right

    
    

    Output :

  3. Creating table using MySQLi PDO Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php
    try {
       $pdo = new PDO("mysql:host=localhost;dbname=newdb",
                         "root", "");
       $pdo->setAttribute(PDO::ATTR_ERRMODE,
                            PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
        die("ERROR: Could not connect. ".$e->getMessage());
    }
      
    try {
        $sql = "INSERT INTO mytable (first_name, last_name, age) 
               VALUES('ram', 'singh', '25') ";    
               $pdo->exec($sql);
        echo "Records inserted successfully.";
    }
    catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql. "
                .$e->getMessage());
    }
      
    // Close connection
    unset($pdo);
    ? >

    chevron_right

    
    

    Output :

The values of the table can be viewed in MySql database as –

Inserting Multiple Rows into a Table

One can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.

  1. Creating table using MySQLi Object-oriented Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php 
    $mysqli = new mysqli("localhost", "root", "", "newdb");
      
    if ($mysqli == = false) {
        die("ERROR: Could not connect. ".$mysqli->connect_error);
    }
      
    $sql = "INSERT INTO mytable (first_name, last_name, age) 
               VALUES('raj', 'sharma', '15'),
        ('kapil', 'verma', '42'),
        ('monty', 'singh', '29'),
        ('arjun', 'patel', '32') ";
        if ($mysqli->query($sql) == = true)
    {
        echo "Records inserted successfully.";
    }
    else
    {
        echo "ERROR: Could not able to execute $sql. "
            .$mysqli->error;
    }
      
    $mysqli->close();
    ? >

    chevron_right

    
    

    Output :

  2. Creating table using MySQLi Procedural Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php 
    $link = mysqli_connect("localhost", "root", "", "newdb");
      
    if ($link == = false) {
        die("ERROR: Could not connect. ".mysqli_connect_error());
    }
      
    $sql = "INSERT INTO mytable (first_name, last_name, age) 
              VALUES('raj', 'sharma', '15'),
        ('kapil', 'verma', '42'),
        ('monty', 'singh', '29'),
        ('arjun', 'patel', '32') ";
        if (mysqli_query($link, $sql))
    {
        echo "Records added successfully.";
    }
    else
    {
        echo "ERROR: Could not able to execute $sql. "
            .mysqli_error($link);
    }
      
    // Close connection
    mysqli_close($link);
    ? >

    chevron_right

    
    

    Output :

  3. Creating table using MySQLi PDO Procedure :
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php 
    try {
        $pdo = new PDO("mysql:host=localhost;dbname="newdb",
                          "root", "");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, 
                              PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
        die("ERROR: Could not connect. ".$e->getMessage());
    }
      
    try {
        $sql = "INSERT INTO mytable (first_name, last_name, age)
             VALUES('raj', 'sharma', '15'),
                   ('kapil', 'verma', '42'),
                   ('monty', 'singh', '29'),
                   ('arjun', 'patel', '32') ";   
         $pdo->exec($sql);
        echo "Records inserted successfully.";
    }
    catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql. "
                 .$e->getMessage());
    }
    unset($pdo);
    ? >

    chevron_right

    
    

Output :

The values of the table can be viewed in MySql database as –



My Personal Notes arrow_drop_up

I am a technology enthusiast who has a keen interest in programming I am pursuing Engineering in Computer Science from GEU, Dehradun I like to unwind by watching movies and English sitcomsI have a keen interest in music

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.