PHP MySQL Insert

How to insert a Data into a Table in a MySQL Database

Now that you know how to build a database and tables in MySQL, you can move on to the next step. You can learn how to run a SQL query to insert records into a table in this tutorial.

In a database table, the INSERT INTO statement is used to insert new rows.

Let's create a SQL query with acceptable values using the INSERT INTO argument, and then execute it by passing it to the PHP mysqli query() function to insert data into the table. Here's an example in which you assign values for the first name, last name, and email fields to add a new row to the persons table.

<?php
  /* Attempt MySQL server connection.
  Assuming you are running MySQL server with default
  setting (user 'root' with no password) */
  $mysqli = new mysqli("localhost", "root", "", "demo");

  // Check connection
  if($mysqli === false){
  die("ERROR: Could not connect. " . $mysqli->connect_error);
  }

  // Attempt insert query execution
  $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
  if($mysqli->query($sql) === true){
    echo "Records inserted successfully.";
  }
  else{
    echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
  }

  // Close connection
  $mysqli->close();
?>

The id field was labelled with the AUTO INCREMENT flag in the previous chapter, if you recall. If this field is left blank, this modifier instructs MySQL to assign a value to it by incrementing the previous value by one.

Creating a Table of Several Rows

You may also use a single insert question to insert several rows into a table at the same time. Include several lists of column values in the INSERT INTO statement to accomplish this, with column values for each row enclosed in parentheses and separated by a comma.

Let's add a few more rows to the people table, as follows:

<?php
  /* Attempt MySQL server connection.
  Assuming you are running MySQL server with
  default setting (user 'root' with no password) */
  $mysqli = new mysqli("localhost", "root", "", "demo");

  // Check connection
  if($mysqli === false){
  die("ERROR: Could not connect. " . $mysqli->connect_error);
  }

  // Attempt insert query execution
  $sql = "INSERT INTO persons (first_name, last_name, email) VALUES
  ('John', 'Rambo', 'johnrambo@mail.com'),
  ('Clark', 'Kent', 'clarkkent@mail.com'),
  ('John', 'Carter', 'johncarter@mail.com'),
  ('Harry', 'Potter', 'harrypotter@mail.com')";
  if($mysqli->query($sql) === true){
   echo "Records inserted successfully.";
  }
  else{
   echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
  }

  // Close connection
  $mysqli->close();
?>

Now go to http://localhost/phpmyadmin/ and look at the data in the persons table in the sample database in phpMyAdmin. The value for the id column is automatically allocated by incrementing the previous id value by 1.