PHP MySQL Insert

This guide is about PHP MySQL Insert Data, how to use PHP in order to insert data into a MySQL database with examples, including specifying the name and columns of the table, and specifying the values to be inserted into them.

There are also examples in the article that illustrate how SQL queries can be executed in PHP using the mysqli and PDO extensions.

In addition to this, We will also guide you the use of prepared statements in order to avoid SQL injection attacks in the future.

Table Of Content-

PHP MySQL Insert:
- PHP MySQL Insert Data:
- Now Insert Data Through MySQLi and PDO:

PHP MySQL Insert Data

In our previous article, we discussed how to establish a database connection in PHP. Now, we will jump right into the process of inserting data into a MySQL database using PHP.

Once we have established a database connection, we can construct the SQL INSERT INTO statement to insert data into the database.

The INSERT INTO statement specifies the table name and the values to be inserted into the table.

Here’s an example SQL INSERT INTO statement:

INSERT INTO mytable (name, age, email) VALUES ('Colin', 36, '[email protected]')

In above example, we are inserting data into a table named mytable with three columns: name, age, and email.

We’re inserting the values ‘Colin‘, 36, and ‘[email protected]’ into those columns, respectively.

To construct the SQL query in PHP, we can use variables to represent the values to be inserted, like this:

$name = "Colin";
$age = 36;
$email = "[email protected]";

$sql = "INSERT INTO mytable (name, age, email) VALUES ('$name', $age, '$email')";

Note: We surround the string values ($name and $email) with single quotes in the SQL query, but we do not surround the integer value ($age) with quotes.

Execute the Query

Once we have constructed the SQL query, we can execute it using the mysqli_query() function.

The mysqli_query() function takes two parameters:

The database connection variable ($conn in our example).
The SQL query variable ($sql in our example).

Here’s an example to execute the query:

if (mysqli_query($conn, $sql)) {
echo "Data inserted successfully";
} else {
echo "Error inserting data: " . mysqli_error($conn);
}

In above code, we have used an if statement to check if the query was successful.

If the query was successful, It will portray “Data inserted successfully”.

If the query failed, output will be “Error inserting data” along with the specific error message returned by mysqli_error().

Note: Once we have finished inserting data into the database, it’s good practice to close the database connection using the mysqli_close() function.

Now Insert Data Through MySQLi and PDO

As soon as we have created a database and a table, we can begin adding data to them so that they can be used in our application.

There are some syntax rules that need to be followed:
There must be a quotation mark around the SQL query in PHP.
There must be quotation marks around the string values inside the SQL query.
There should not be any quotation marks around numeric values.
There is no need to quote NULL values.

A MySQL table can add new records by inserting them into it with the INSERT INTO statement, which works as follows:

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

In our previous chapter we have also created a blank table named “Users“, which will have seven columns: “id”, “firstname”, “lastname”, “email”, “age”, “gender” and “reg_date”.

It’s now time to start filling in the data in same table.

Remember: MySQL will automatically add the value to columns that are AUTO_INCREMENT (like “id”) or TIMESTAMP with the update of current_timesamp (like “reg_date”).

Here are some examples of how to add a new record to the “Users” table using the following code.

Below is an example using MySQLi’s Object-Oriented method:

Example (MySQLi Object-Oriented):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db";// Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //Inserting data into Users $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES ('Matt', 'LeBlanc', '[email protected]', 55, 'Male')"; if ($conn->query($sql) === TRUE) { echo "New record added successfully"; } else { echo "Error: " . $sql . " " . $conn->error; } $conn->close(); ?>

Below is an example using MySQLi’s Procedural method:

Example (MySQLi Procedural):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Checking connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES ('Matt', 'LeBlanc', '[email protected]', 55, 'Male')"; if (mysqli_query($conn, $sql)) { echo "New record added successfully"; } else { echo "Error: " . $sql . " " . mysqli_error($conn); } mysqli_close($conn); ?>

Below is an example using MySQL PDO(PHP Data Objects) method:

Example (PDO):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Specify the exception error mode in the PDO $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES ('Matt', 'LeBlanc', '[email protected]', 55, 'Male')"; // Due to the fact that no results are returned, use exec() instead $conn->exec($sql); echo "New record added successfully"; } catch (PDOException $e) { echo $sql . " " . $e->getMessage(); } $conn = null; ?>

Example Explanation

In above example we have demonstrates the use of PDO (PHP Data Objects) to connect to a MySQL database and insert a new record into the “Users” table. Here’s a step-by-step explanation of what the code does:

The code starts by defining the variables $servername, $username, $password, and $dbname, which are used to specify database connection details.
A try-catch block is used to handle any exceptions that may occur during the database connection and query execution. Inside the try block, the PDO constructor is used to create a new database connection. The connection string is created by combining the servername, dbname, username, and password variables.
The PDO::setAttribute() method is called on the $conn object to set the error mode to ERRMODE_EXCEPTION, which means that PDO will throw an exception if an error occurs.
A SQL INSERT statement is defined in the $sql variable. The statement inserts a new record into the “Users” table with the values ‘Matt’ for firstname, ‘LeBlanc’ for lastname, ‘[email protected]’ for email, 55 for age, and ‘Male’ for gender.
The $conn->exec() method is called to execute the INSERT statement. Because the statement does not return any results, the exec() method is used instead of query().
If the INSERT statement is executed successfully, the message “New record added successfully” is displayed.
If an exception is caught, the catch block is executed. The catch block displays an error message that includes the SQL query and the error message returned by PDO.
Finally, the $conn variable is set to null to close the database connection.

We value your feedback.