Related Articles

Related Articles

PHP | Inserting into MySQL database
  • Difficulty Level : Hard
  • Last Updated : 21 Mar, 2018

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
Recommended Articles
Page :