How to Use PHP to Insert Data Into MySQL Database
access_time
hourglass_empty
person_outline

How to Use PHP to Insert Data Into MySQL Database

In this tutorial, you will learn how to INSERT data into your MySQL database from PHP scripts. There are two methods that you can use, MySQLi and PDO. Before you begin this PHP MySQL insert guide you’ll need access to your hosting control panel.

Get a massive discount for PHP Hosting today! Up to 90% off for a limited time!

Get Started Here

If you are unsure how to set up your MySQL connection you can check out this PHPMySQL tutorial which covers the most important steps of working with PHP and databases.

Creating a Table (Optional)

First, we have to create a table for your data. If you have already created one, scroll down to the next section. Creating a table is a simple process that you can do with phpMyAdmin, which is located in your hosting control panel.phpMyAdmin option in your hosting database

After logging into your phpMyAdmin page, you should see something similar to this:

phpmyadmin_main page

We will create a table named Students for our database u104357129_name. You can create a new table by clicking the Create Table button near the bottom of the page. After that, you will be directed to a new page where you can enter all the information needed for your table:MySQL table named Students created in MyPHPAdmin area

Since this is a simple setup, for more information regarding the structure of a table/database and what kind of settings you can use with the columns, refer to the official documentation of phpMyAdmin.

For now, here are a few explanations of the columns that we used:

  • Name – This is the name of your column. It will be displayed at the top of your table.
  • Type – This is your data type. You can set int, varchar, string, and many more. For example, we selected varchar because we need to enter a string type name (which uses letters, not numbers).
  • Length/Values – This is used to specify the maximum length your entry in this column can have.
  • Index – We used the “Primary” index for our “ID” field. When creating a table, it is recommended to have one ID column. It is used to enumerate table entries and required when configuring table relationships. We also marked “A_I”, which means Auto Increment. This will automatically enumerate the entries (1,2,3,4…).

Click Save and your table will be created.

PHP Code to INSERT Data Into MySQL Database

There are two methods you can use to INSERT data into your MySQL database. The PHP MySQLi method and PHP Data Object or PDO method.

MySQLi Method

First, you’ll need to establish a connection to a database. After that is done, we can proceed with the MySQL query INSERT. Here is a full PHP code example with the basic connection and insert methods:

<?php
$servername = "mysql.hostinger.co.uk";
$database = "u266072517_name";
$username = "u266072517_user";
$password = "buystuffpwd";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection

if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}
 
echo "Connected successfully";
 
$sql = "INSERT INTO Students (name, lastname, email) VALUES ('Test', 'Testing', 'Testing@tesing.com')";
if (mysqli_query($conn, $sql)) {
      echo "New record created successfully";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);

?>

The first part of the code (lines 3 – 18) is meant for the actual connection to the database. We will not go through this part again, however, if you wish to know what each line of code means, check out our how to connect to a database tutorial.

So, let’s start with line number 19:

$sql = "INSERT INTO Students (name, lastname, email) VALUES ('Test', 'Testing', 'Testing@tesing.com')";

This is the most important line of PHP code as it inserts data into the MySQL database. The INSERT INTO is a statement which adds data into the specified database table. In this example, we are adding data to the table Students.

Going further, between the parenthesis, we have the table column names specified to where we want to add the values: (name, lastname, email). Data will be added in the specified order. If we wrote (email, lastname, name), values would be added in the wrong order.

The next part is the VALUES statement. Here we specify our values to insert into the previously specified columns. That way, each column represents a specific value. For example, in our case, it would be like this: name = Test, lastname = Testing, email = Testing@testing.com.

Another thing worth mentioning is that we just ran an SQL query using PHP code. SQL queries must be set between the quotes. In our example, everything between the quotes and written after $sql = is an SQL query.

The next part of the code (20 – 22 lines) checks if our query was successful:

if (mysqli_query($conn, $sql)) {
     echo "New record created successfully";
}

It simply displays a success message if the query which we ran was successful.

And the final part (22 – 24 lines) displays a different message in case our query wasn’t successful:

else {
     echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

It will provide us an error SQL message in case something is wrong.

PHP Data Object (PDO) Method

As with the previous example, we need a connection to the database first which is done by creating a new PDO object – this tutorial will show you how if you are unsure. As the connection to the MySQL database is a PDO object, you must use various PDO methods (any function that is part of any object) to prepare and run queries. Methods of objects are called like this:

$the_Object->the_Method();

PDO allows you to prepare SQL code before it is executed. The SQL query is evaluated and corrected before being run. A simplified SQL injection attack could be done just by typing SQL code into a field on a form. For example:

// User writes this in the username field of a login form
john"; DROP DATABASE user_table;

// The final query becomes this
"SELECT * FROM user_table WHERE username = john"; DROP DATABASE user_table;

As there is syntactically correct SQL code, the semi-colon makes DROP DATABASE user_table a new SQL query, and your user table is deleted. Prepared statements do not allow the and ; characters to end the original query and the malicious instruction DROP DATABASE will never be executed.

You should always use prepared statements when sending or receiving data from the database with PDO.

To use prepared statements, you must write a new variable that calls the prepare() method of the database object.

On to the correct code:

<?php
$servername = "mysql.hostinger.com";
$database = "u266072517_name"; 
$username = "u266072517_user";
$password = "buystuffpwd";
$sql = "mysql:host=$servername;dbname=$database;";
$dsn_Options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

// Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object
try { 
  $my_Db_Connection = new PDO($sql, $username, $password, $dsn_Options);
  echo "Connected successfully";
} catch (PDOException $error) {
  echo 'Connection error: ' . $error->getMessage();
}

// Set the variables for the person we want to add to the database
$first_Name = "Test";
$last_Name = "Testing";
$email = "Testing@testing.com";

// Here we create a variable that calls the prepare() method of the database object
// The SQL query you want to run is entered as the parameter, and placeholders are written like this :placeholder_name
$my_Insert_Statement = $my_Db_Connection->prepare("INSERT INTO Students (name, lastname, email) VALUES (:first_name, :last_name, :email)");

// Now we tell the script which variable each placeholder actually refers to using the bindParam() method
// First parameter is the placeholder in the statement above - the second parameter is a variable that it should refer to
$my_Insert_Statement->bindParam(:first_name, $first_Name);
$my_Insert_Statement->bindParam(:last_name, $last_Name);
$my_Insert_Statement->bindParam(:email, $email);

// Execute the query using the data we just defined
// The execute() method returns TRUE if it is successful and FALSE if it is not, allowing you to write your own messages here
if ($my_Insert_Statement->execute()) {
  echo "New record created successfully";
} else {
  echo "Unable to create record";
}

// At this point you can change the data of the variables and execute again to add more data to the database
$first_Name = "John";
$last_Name = "Smith";
$email = "john.smith@email.com";
$my_Insert_Statement->execute();

// Execute again now that the variables have changed
if ($my_Insert_Statement->execute()) {
  echo "New record created successfully";
} else {
  echo "Unable to create record";
}

On lines 28, 29, and 30, we use the bindParam() method of the database object. There is also the bindValue() method which is very different.

  • bindParam() – This method evaluates data when the execute() method is reached. The first time the script reaches an execute() method it sees that $first_Name corresponds to “Test”, binds that value and runs the query. When the script reaches the second execute() method, it sees that $first_Name now corresponds to “John”, binds that value and runs the query again with the new values. The important thing to remember is that we defined the query once and reused it with different data at different points in the script.
  • bindValue() – This method evaluates the data as soon as bindValue() is reached. As the value of $first_Name was set to “Test” when the bindValue() was reached, it will be used every time an execute() method is called for $my_Insert_Statement.

Notice that we reuse the $first_Name variable and give it a new value the second time. If you check your database after running this script, you have both of the defined names, despite the $first_Name variable equalling “John” at the end of the script. Remember that PHP evaluates an entire script before actually running it.

If you update the script to replace bindParam with bindValue, you will insert into MySQL “Test Testing” twice in the database and John Smith will be ignored.

Confirming the Success and Solving Common Issues

If the query that we ran and insert into MySQL database was successful, we will see the following message like:

Connect Successfully
New record created successfully

Troubleshooting Common Errors

However, there are times when the new record will have an error with the SQL insert. But not to worry, there a few ways you can go about fixing these MySQL errors.

MySQLi

If a MySQLi error message is displayed we can do the following methods to fix it. For example, let’s make one syntax error in our code, if we do, we will see something similar to this:

Connect successfully
Error: INSERT INTO students {name, lastname, email} VALUES ('Test', 'Testing', 'Testing@testing.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{name, lastname, email} VALUES ('Test', 'Testing', 'Test@testingcom')' at line 1"

As you can see, the first part of the code is good, the connection was established successfully, but our SQL query ran into a wall.

"Error: INSERT INTO Students {name, lastname, email} VALUES ('Thom', 'Vial', 'thom.v@some.com') You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{name, lastname, email} VALUES ('Thom', 'Vial', 'thom.v@some.com')' at line 1"

There is a syntax error which, unfortunately, caused our script to fail. The error was here:

$sql = "INSERT INTO Students {name, lastname, email} VALUES ('Thom', 'Vial', 'thom.v@some.com')";

We used curly brackets instead of the simple parenthesis. Since this is not correct it caused our script to throw a syntax error.

PDO

On line 7 of the PDO connection, the error mode is set to display all exceptions. If this was left out of the script and the query failed, you would not receive any error messages. With exceptions enabled, the specific problem is shown.

This should generally only be used when developing a script as it can expose the database and table names, which you may prefer to hide from anyone who might be trying to maliciously access your data. In the case above where curly braces were used instead of parenthesis, the error looks similar to this:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; <code>check the manual that corresponds to your MySQL server version for the right syntax to use near '{name, lastname, email} VALUES ('Thom', 'Vial', 'thom.v@some.com')' at line 1"</code>

Other possible problems you might run into:

  • Incorrect columns specified (non-existent columns or a spelling mistake).
  • One type of value being assigned to another type of column. For example, if we tried to assign a number 47 into a Name column, we would get an error because it is supposed to be a string value. But if we assigned a number between quotes, for example, “47”, that would work because our number would be assigned as a string to the column.
  • Trying to enter data into a table which does not exist or making a spelling mistake of the table.

All of those errors can be fixed easily by following the error message guidelines or checking the error log.

After a successful data entry, we should see it added to our database. Here is an example of the table to which we added our data when viewed from phpMyAdmin.phpMyAdmin Data inserted into MySQL using PHP

Conclusion

In this tutorial, you have learned how to use PHP to INSERT data into your MySQL database using MySQLi and PDO. You have also learned how to troubleshoot common connection errors. Knowing how to use PHP to add data into MySQL database is useful whether you are learning how to code or building your website.

The Author

Author

Tautvydas V. / @tautvydas

Tautvydas started his career as a technical support agent and now walks the path of full-stack development. He strives to produce top-notch features, improvements, and outstanding user experience with every line of code. In his free time, Tautvydas likes to travel and play old school video games.

Author

Robert_78 Reply

September 23 2017

Hi, first and foremost, congratulations for your excellent tutorial. The only remark I feel like making is that in your tutorial it seems that it's necessary to use phpmyadmin to use PHP to Insert Data Into MySQL Database. Probably my question is naive or a bit silly and for that I do apologize; I've been studying these subjects on my own with a great passion by the way. So, please help me understand because I looked into many other tutorials on the Web without being able to find out the solution to my problem I had been looking for. Let me give you an example. I have created a form (let's suppose with a textarea to fill out with the a comment); upon filling in the whole form I wish to click on the button "submit". Let's also suppose that 1) I use php instead of Perl, Phyton and so forth. 2) I WANT to use the Maria DB SQL DATABASE MANAGEMENT SYSTEM. I should stress that, as far as I know but if I go wrong don't hesitate to correct me, MySQL is the world's most popular open-source RDBMS. It is extremely fast and is used by some of the most frequently visited Web sites on the Internet, including Google, Facebook, Twitter, Yahoo, You Tube, and Wikipedia. Recently, HOWEVER, some of these companies HAVE MOVED TO MariaDB. Fedora/RHEL HAS REPLACED MySQL in its repositories with MariaDB, and Wikipedia has also converted. Ubuntu provides both version. 3) the HTML 5, CSS 3 and all possible other files have been placed in /var/www/html on my Debian 8 remote Server (where I have set up Apache and built a Website, which for now I prefer not to write in the textbox below). 4) Now, and THIS IS THE CRUCIAL POINT I WANT TO MAKE, I wish to write a PHP script that allows me to store all the form data I submitted INTO a table of a database of MariaDB. And I want to do that without using phpmyadmin, which of course would simplify my task, I'm fully aware of that. BUT, to didactic purposes I'll be willing to tell you if you want, I don't wish any graphic interface at all. Now, a system engineer told me that what I'm gonna do is realizable and to go to Google to look for video tutorials, articles etc.... I wrote off and I hope to have made myself clear. In a few words I can't help wondering WHY in all the dozens and dozens of tutorials -that by the way are more or less copied out despite the Copyright laws- what is badly explained is how to get my target by using XAMPP, Windows, MySQL AND NOT MariaDB and php scripts that I tested but don't work - if you want I can email you them. I do believe you could create an extremely original tutorial by explaining what I wrote / pointed out. I thank you very much for your patience and I do hope to hear from you at your earliest convenience. With kind regards, Robert

    Author

    J3

    Replied on December 19 2017

    wtf! What a waste of perfectly good letters Joking, right?

Author

Jerry Jubilant Reply

October 10 2018

You are so great, the problem I have been facing for long has been resolved thanks to your guide... May God bless you!

Author

Aini Husna Reply

November 05 2018

I'm sorry.. but I've been following your suggestion but still cannot make it.

    Author

    Gediminas B.

    Replied on November 06 2018

    Hello, Aini. I'm sad to hear that you're having trouble :( Where exactly do you get stuck? Do you get any specific error messages while inserting data? Checking the error log could give you some good hints. Let me know how it goes ;)

Author

Lucas Reply

March 18 2019

Thanks for tutorial, can you also create a tutorial on how to query data or view data

Author

Sean Reply

April 13 2019

I tried using the PDO example but it fails. I used a clean new .php file and table. I used a copy and paste with no modifications other than closing the at the end. Browser error: Parse error: syntax error, unexpected ':' in /var/www/html/hostinger.php on line 28 Line code: $my_Insert_Statement->bindParam(:first_name, $first_Name); Thanks.

    Author

    Sean

    Replied on April 13 2019

    I'm developing this on my local machine, however I do have a paid account at Hostinger. My previous post was filtered at the end, it should say the only modification I made was closing the php section. The internet says the error likely corresponds to a missing or extra parenthesis. I'm not sure where that would be though.

Author

richard Reply

April 17 2020

you have solved my problem.Thank you sir .you are too good,god bless you.

Leave a reply

Comment*

Name*

Email*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Become a part of Hostinger now!

More in PHP
Close