How To Insert Data In MySQL Database Using PHP In XAMPP

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

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.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to insert a row into sales table
$query = "INSERT INTO sales (id,name,count) VALUES (1,'Cooking-Gas',40)";
if (mysqli_query($connection, $query)) {
  echo "record Inserted Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

In the above code, "my_company" is the database name and "sales" in the table name.

Open your web browser and navigate to http://localhost/insert.php. You will see an output like this in your browser window.

You can also verify if the record/row is inserted from phpMyAdmin as well. To do so, open phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL from the browser.

Go to my_company -> sales and click Browse to see the inserted record.

As you can see, we have successfully inserted a single record in the table called "sales" in the my_company database.

Inserting Multiple Records In MySQL Table Using PHP

We can insert multiple Records/Rows 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 MySQL table and columns represents the column name and values are inserted into the table.

Steps

1. Specify servername, MySQL username, password and database name.

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

2. Create a connection using the above details.

By using mysqli_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 the 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 mysqli_connect_error() function specified in an if condition. This function will represent an error, if the connection is failed.

4. Specify the SQL Query to insert multiple records into the table.

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

Code:

$query = "INSERT INTO table_name (id,name,count) VALUES (5,'Shampoo',10);";
$query .= "INSERT INTO table_name (id,name,count) VALUES (2,'Milk',20);";
$query .= "INSERT INTO table_name (id,name,count) VALUES (3,'Books',14);";
$query .= "INSERT INTO table_name (id,name,count) VALUES (4,'Chocos',45);";
$query .= "INSERT INTO table_name (id,name,count) VALUES (6,'Eggs',12)";

Heads Up: Please mind the dot (.) in second and the subsequent SQL queries. You need to put a dot (.) in front of the equal sign starting from second query.

5. Checking the creation.

If we want to check if the values inserted or not, we can use mysqli_query() function.

Code:

mysqli_query($connection, $query)

As you can see, It takes two parameters.

• The $connection parameter specifies the connection details.
• The $query parameter is the sql query that we use to insert multiple rows in the table

If this condition is True, then the rows 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 to insert 5 records based on the above steps.

PHP Code To Insert Multiple Records In MySQL Table

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

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to insert 5 rows into sales table
$query = "INSERT INTO sales (id,name,count) VALUES (5,'Shampoo',10);";
$query .= "INSERT INTO sales (id,name,count) VALUES (2,'Milk',20);";
$query .= "INSERT INTO sales (id,name,count) VALUES (3,'Books',14);";
$query .= "INSERT INTO sales (id,name,count) VALUES (4,'Chocos',45);";
$query .= "INSERT INTO sales (id,name,count) VALUES (6,'Eggs',12)";

if (mysqli_multi_query($connection, $query)) {
  echo "records Inserted Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/insert_multiple.php. You will see an output like this in your browser window.

Let us verify if the records are actually inserted or not from the phpMyAdmin dashboard.

Open phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL from the browser. Go to my_company-> sales and click on Browse button to see the inserted records.

Conclusion

In this tutorial, we discussed how to insert data in MySQL database using PHP code in XAMPP stack. We provided two sample PHP codes to demonstrate how to insert single and multiple records in a MySQL table.

Change the code as per your requirement and test them on your system. If you have any question, let us know via the comment section below.

Read Next:

• How To Select Data From MySQL Database Using PHP in XAMPP Stack