PHP MySQL Insert Data

The INSERT INTO statement is used to add new records to a MySQL table.

Now that you’ve understood how to create database and tables in MySQL. In this tutorial you will learn how to execute SQL query to insert records into a table.

First you need to create database and then table. After that you can add data into tables.

Data can be entered into MySQL tables by executing SQL INSERT statement through PHP function mysqli_query. 

Syntax rules

• The SQL query must be quoted in PHP.
• String values inside the SQL query must be quoted.
• Numeric values and the word NULL must not be quoted

INSERT INTO statement syntax

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

In the previous chapter we created an empty table named “Students” with five columns: “id”, “firstname”, “lastname”, “email” and “reg_date”. Now, let us fill the table with data.

Example – MySQLi Procedural

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO Students (firstname, lastname, email)
VALUES ('Peter', 'Doe', 'peter@example.com')";

if (mysqli_query($conn, $sql)) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

If you remember from the preceding chapter, the id field was marked with the AUTO_INCREMENT flag. This modifier tells the MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1.

Inserting Multiple Rows into a Table

You can also insert multiple rows into a table with a single insert query at once.

To insert multiple rows into a table, 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.

Example

$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Anil', 'Kent', 'anilkent@mail.com'),
('Garv', 'Jadav', 'garvjadav@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('Umesh', 'Bavaliya', 'umeshbavaliya@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO Students (firstname, lastname, email)
VALUES ('Peter', 'Doe', 'peter@example.com')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Example – PDO

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "INSERT INTO Students (firstname, lastname, email)
  VALUES ('Peter', 'Doe', 'peter@example.com')";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "New record created successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>