Coding php insert data into database

After creating a MySQL database and table, we can start inserting the data [i.e. records] in them. In this tutorial, we are going to learn how to insert data in MySQL database using PHP in XAMPP stack.

Prerequisites

Make sure you have setup XAMPP stack in your system. The following guide explains how to setup XAMPP stack in Linux.

• How To Install XAMPP In Linux

Alternatively, you can use the LAMP or LEMP stacks which provide both PHP and MySQL. If you're on Linux, refer to the following guides to install LAMP/LEMP stacks.

• Install Apache, MySQL, PHP [LAMP Stack] On Ubuntu 20.04 LTS
• Install Nginx, MySQL, PHP [LEMP Stack] On Ubuntu 20.04 LTS
• Install Apache, MariaDB, PHP [LAMP Stack] In CentOS 8
• Install Apache, MariaDB, PHP [LAMP] stack on Arch Linux
• Install Nginx, MariaDB, PHP [LEMP] stack on Arch Linux

Setting up XAMPP is much easier than LAMP and LEMP stacks. So, we will will be using XAMPP stack throughout this guide.

After setting up the XAMPP stack, you need to create a MySQL database and table inside the database. Refer to the following guide to know how to create MySQL database and table in XAMPP stack.

• Create MySQL Database And Table Using PHP In XAMPP

For demonstration purpose, I am going to create a table named "sales" in a database called "my_company" in my XAMPP stack.

Insert Data In MySQL Database Using PHP

We can insert a single record or multiple records at once in a MySQL table. First, we will see how to insert single record.

Inserting Single Record In MySQL Table Using PHP

We will use a SQL Query statement in our PHP code to insert a record in the table. We can insert a single record/row into a table by using the INSERT command.

Query Syntax:

INSERT INTO table_name[column1,column2,………….] VALUES [value1,value2,………….]  

[Or]

INSERT INTO table_name VALUES [value1,value2,………….]

Where, table_name represents the name of the table and columns represents the column name and values are inserted into the table.

Steps

1. Specify the servername, MySQL username, password and database name in the PHP code.

Here, the servername is localhost, username is root and password is empty. And the database name is my_company, we are creating a table called "sales" inside this database.

2. Create a connection using the above details.

By using my_sqli_connect[] function, we will establish a connection. It will take three parameters. First will be the servername, second is the username and the last is password. It will also take a database name which is optional here, because we are just creating the connection.

Code:

$connection = mysqli_connect[$server_name, $user_name, $password,$database_name]

3. Check the Connection.

We can check the connection using the mysqli_connect_error[] function specified in an if condition. This function will display an error, if the connection is failed.

4. Specify the SQL Query to insert a single record into the table.

In this step, we specify the SQL query to insert values into the database. Let the database name is "my_company" and we are storing it in a variable named query. The table name is "sales" that has three columns.

Code:

$query = "INSERT INTO table_name [id,name,count] VALUES [1,'Cooking-Gas',40]";

5. Checking the creation.

If we want to check the values inserted or not, we can use the mysqli_query[] function.

Code:

mysqli_query[$connection, $query]

It will take two parameters.

• The $connection parameter specifies the connection details.
• The $query parameter is the sql query that we use to insert a record/row in the table

If this condition is True, then a row will be inserted. Otherwise it will return an error. We can check the error by using the mysqli_error[$connection] function.

6. Close the connection.

This is the last step where we have to close the connection by using the mysqli_close[] function.

Code:

mysqli_close[$connection];

Now let us write a sample PHP code based on the above steps.

PHP Code To Insert A Single Record In MySQL Table

Create a new file named insert.php under the /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.