PHP MySQL Insert Data

In this unit, we will see how to insert data into database tables.

To insert data into the MySQL table, we have to create a MySQL table first. Please read the below article to create a MySQL table.

Read Also: PHP MySQL Create Table

We have to make a SQL query using the insert into a statement with appropriate values, after that we will execute this insert query by passing it to the PHP mysqli_query function to insert data in a table.

In the previous unit, we understood how to create databases and tables in MYSQL. In this unit, we will learn how to execute an SQL query to insert data into a table. The insert statement is useful to insert new rows in a database table.

There are different ways to insert records. It can be done either by providing the column names with values or by merely providing the table’s values but not specifying the column name.

Syntax

insert into table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

The second way doesn’t specify the column names where the data will be inserted only their values.

insert into table_name VALUES (value1, value2, value3,...)

Example

In this example, we insert a new row to the user table by specifying values for the columns first_name, last_name, email etc.

1. MySQL Procedural

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

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

$sql = "insert into user (firstname, lastname, email)
values ('Nick', 'Pan', 's@gmail.com')";

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

mysqli_close($connection);
?>

2. Object-Oriented

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

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

$sql = "insert into user (firstname, lastname, email) values ('Nick', 'Pan', 's@gmail.com')";

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

$connection->close();
?>

3. MySQL PDO

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

try {
  $connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "insert into user (firstname, lastname, email)
  values ('Nick', 'Pan', 's@gmail.com')";
  // use exec() because no results are returned
  $connection->exec($sql);
  echo "New record added successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$connection = null;
?>

Read More: PHP MySQL Multiple Insert

Conclusion

In this article, we learned how data could be entered into MySQL tables by executing the SQL insert statement using the mysqli_query PHP function for the Procedural method. Also, we have learned Object-oriented & PDO methods to insert data into SQL tables.

We hope you found this article helpful and in case, you want to learn about how to connect to MySQL using PHP and creating databases, do check out our previous blogs. Happy Learning! 🙂