MySQL Insert Data

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

To insert data in 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 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.

As we have understood in the previous unit that how to create database 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 of inserting records, either by providing the columns name with values or by merely providing the table’s values but doesnโ€™t specify 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', '[email protected]')";

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', '[email protected]')";

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', '[email protected]')";
  // 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;
?>

Conclusion

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

I hope you find this article helpful ๐Ÿ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
MySQL Create Table

In this unit, we will learn how to create a MySQL table. To create a table with the given name,...

Close