PHP

How to Use PHP to Insert Data Into MySQL Database

How to Use PHP to Insert Data Into MySQL Database

Introduction

In this tutorial, you will learn how to start managing a database from your PHP scripts. You will learn how to use PHP to INSERT into MySQL database. Before proceeding, you should check out our other tutorial which covers the most important step of working with PHP and databases – connecting PHP to a database.

What you’ll need

Before you begin this guide you’ll need the following:

  • Access to your hosting control panel

Step 1 — Creating a table

First of all, we have to create a table for your data. It is a very simple procedure that you can do with phpMyAdmin, which is located your hosting control panel. We already covered the process of creating MySQL database in the previous tutorial, so it will be skipped here.

Hostinger Control Panel phpMyAdmin Icon

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

phpmyadmin_main page

We will create a table named Students for our database u266072517_name. You can create a new table by clicking Create Table button. After that, you will see this new page where you can enter all the needed information for your table:

phpmyadmin_setup

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

For now, here are a few simple 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 – You can set a column type here. For example, we selected varchar because we need to enter a string type of name here (which has letters, not numbers).
  • Length/Values – Used to specify the maximum length your entry in this column can have.
  • Index – We used “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. I 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.

Step 2 — Writing PHP code to INSERT data into MySQL database

Option 1 – MySQLi Method

First of all, you should establish a connection to a database based on our previous tutorial. After that is done, we can proceed with the SQL query INSERT. Here is a full example code with the basic connection and insertion methods:

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

Let’s start with line number 19:

This is the most important line of our code, it does everything we want to learn in this tutorial – inserts data into MySQL database. The INSERT INTO is a statement which adds data into the specified database table. In our example, we are adding data to the table Students.

Going further, between the brackets, we have table columns specified to which 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 a different order.

So next part is VALUES statement. Here we specify our values to the previously specified columns. That way, each column represents a specific value. For example, in our case it would be like this: name = Thom, lastname = Vial, email = thom.v@some.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 quotes and going after $sql = is an SQL query.

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

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:

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

Option 2 – 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 – the previous tutorial will show you how. 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:

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 in to a field on a form. For example:

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:

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 “Thom”, 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 “Thom” 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 “Thom Vial” twice in the database and John Smith will be ignored.

Step 3 — 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:

insert into mysql successful

Troubleshooting common errors

MySQLi

In any other case, an error message will be displayed instead. For example, let’s make one syntax error in our code and we get this:

fail message

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

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

As you can see, we used curly brackets instead of the simple ones. This is not correct and 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 received 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 is trying to maliciously access your data. In the case above where curly braces were used instead of brackets, the error looks similar to this:

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 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 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.

1 Comment

Click here to post a comment

  • 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

Join thousands of subscribers worldwide

and get awesome deals & the newest tutorials to your email

Please wait...

Thank you for sign up!