Web Hosting How to Upload Large Data Into Mysql Database Php

Mar 10, 2022

Domantas G.

8min Read

How to Use PHP to Insert Data Into MySQL Database

In this tutorial, yous will learn how to INSERT data into your MySQL database from PHP scripts. In that location are two methods that yous can use, MySQLi and PDO. Before you begin this PHP MySQL insert guide you lot'll demand access to your hosting control console.

Download eBook: Speed Up Your Website. 8 Practical Tips That Work

If you are unsure how to set up your MySQL connectedness 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 accept to create a table for your data. If y'all have already created one, whorl downwards to the side by side section. Creating a table is a unproblematic process that you lot tin can do with phpMyAdmin, which is located in your hosting command panel.

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

We will create a table named Students for our database u104357129_name. You tin create a new table past clicking the Create Table button almost the lesser of the page. After that, you will be directed to a new page where you can enter all the information needed for your table:

Since this is a simple setup, for more than information regarding the structure of a tabular array/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. Yous tin set up int, varchar, string, and many more than. For example, we selected varchar because nosotros 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.
• Alphabetize – Nosotros used the "Primary" index for our "ID" field. When creating a table, it is recommended to accept one ID column. It is used to enumerate table entries and required when configuring table relationships. We also marked "A_I", which ways Auto Increase. This will automatically enumerate the entries (1,ii,iii,4…).

Click Save and your tabular array volition exist created.

PHP Code to INSERT Data Into MySQL Database

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

MySQLi Method

Beginning, you'll need to institute a connection to a database. Later on 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, $countersign, $database);  // Check connexion  if (!$conn) {       dice("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 "Fault: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn);  ?>

The first part of the code (lines three – xviii) is meant for the actual connection to the database. We volition not go through this office again, nevertheless, if you wish to know what each line of code ways, check out our how to connect to a database tutorial.

Then, let'due south start with line number 19:

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

This is the near of import 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 information to the table Students.

Going farther, between the parenthesis, we take the table column names specified to where we desire to add the values: (name, lastname, email). Data will exist added in the specified order. If we wrote (email, lastname, name), values would be added in the wrong social club.

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

Another affair worth mentioning is that nosotros 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 later $sql = is an SQL query.

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

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

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

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

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

Information technology will provide the states an mistake SQL message in case something is wrong.

PHP Information Object (PDO) Method

Equally with the previous example, nosotros need a connection to the database first which is done by creating a new PDO object – this tutorial volition show you how if you are unsure. As the connection to the MySQL database is a PDO object, you must apply various PDO methods (any role that is role of any object) to fix and run queries. Methods of objects are called like this:

$the_Object->the_Method();        

PDO allows you to prepare SQL code earlier it is executed. The SQL query is evaluated and corrected before being run. A simplified SQL injection assail could be washed merely by typing SQL lawmaking into a field on a course. 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 right SQL code, the semi-colon makes DROP DATABASE user_table a new SQL query, and your user table is deleted. Prepared statements practise non allow the " and ; characters to cease the original query and the malicious pedagogy DROP DATABASE volition never exist executed.

You should e'er use prepared statements when sending or receiving information from the database with PDO.

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

On to the right code:

<?php $servername = "mysql.hostinger.com"; $database = "u266072517_name";  $username = "u266072517_user"; $countersign = "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 together to the database $first_Name = "Test"; $last_Name = "Testing"; $e-mail = "Testing@testing.com";  // Here we create a variable that calls the gear up() method of the database object // The SQL query you lot want to run is entered as the parameter, and placeholders are written like this :placeholder_name $my_Insert_Statement = $my_Db_Connection->fix("INSERT INTO Students (name, lastname, e-mail) VALUES (:first_name, :last_name, :email)");  // Now nosotros tell the script which variable each placeholder really refers to using the bindParam() method // Starting time parameter is the placeholder in the statement above - the second parameter is a variable that information technology should refer to $my_Insert_Statement->bindParam(':first_name', $first_Name); $my_Insert_Statement->bindParam(':last_name', $last_Name); $my_Insert_Statement->bindParam(':email', $electronic mail);  // Execute the query using the data we simply defined // The execute() method returns TRUE if information technology is successful and Simulated if information technology is not, allowing you lot to write your own messages here if ($my_Insert_Statement->execute()) {   echo "New record created successfully"; } else {   repeat "Unable to create record"; }  // At this point you tin can change the data of the variables and execute again to add more than information to the database $first_Name = "John"; $last_Name = "Smith"; $electronic mail = "john.smith@electronic mail.com"; $my_Insert_Statement->execute();  // Execute again now that the variables accept changed if ($my_Insert_Statement->execute()) {   repeat "New tape created successfully"; } else {   echo "Unable to create record"; }

On lines 28, 29, and thirty, we employ the bindParam() method of the database object. There is also the bindValue()method which is very unlike.

• 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 "Exam", 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 affair to remember is that nosotros defined the query one time and reused it with dissimilar data at different points in the script.
• bindValue() –This method evaluates the information as presently as bindValue() is reached. As the value of $first_Name was set to "Test" when the bindValue() was reached, it volition exist used every time an execute() method is called for $my_Insert_Statement.

Notice that nosotros reuse the $first_Name variable and requite it a new value the second time. If you bank check your database after running this script, you have both of the divers names, despite the $first_Name variable equalling "John" at the cease of the script. Recollect that PHP evaluates an unabridged script earlier actually running information technology.

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

Confirming the Success and Solving Mutual Issues

If the query that we ran and insert into MySQL database was successful, we will run into the post-obit message similar:

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 lot can go about fixing these MySQL errors.

MySQLi

If a MySQLi error message is displayed we can practise the following methods to fix it. For instance, let's make 1 syntax error in our lawmaking, if we practise, we will come across something similar to this:

Connect successfully Error: INSERT INTO students {name, lastname, e-mail} VALUES ('Test', 'Testing', 'Testing@testing.com') You lot take 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, electronic mail} VALUES ('Test', 'Testing', 'Test@testingcom')' at line 1"

As y'all can meet, the first part of the lawmaking is good, the connection was established successfully, merely our SQL query ran into a wall.

"Error: INSERT INTO Students {proper name, lastname, email} VALUES ('Thom', 'Vial', 'thom.five@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 utilize near '{proper noun, lastname, email} VALUES ('Thom', 'Vial', 'thom.v@some.com')' at line one"        

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

$sql = "INSERT INTO Students {proper noun, lastname, electronic mail} 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 mistake.

PDO

On line vii of the PDO connection, the error way is set up to display all exceptions. If this was left out of the script and the query failed, you would not receive any error letters. With exceptions enabled, the specific trouble is shown.

This should generally simply be used when developing a script as it can expose the database and tabular array 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 mistake: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an mistake in your SQL syntax; <code>check the manual that corresponds to your MySQL server version for the right syntax to use near '{name, lastname, electronic mail} VALUES ('Thom', 'Vial', 'thom.v@some.com')' at line one"</code>        

Other possible bug you might run into:

• Incorrect columns specified (not-existent columns or a spelling error).
• Ane blazon of value being assigned to another type of column. For example, if we tried to assign a number 47 into a Proper noun cavalcade, we would get an error considering information technology is supposed to exist a cord value. But if we assigned a number between quotes, for example, "47", that would work considering our number would be assigned as a cord to the column.
• Trying to enter data into a table which does non be or making a spelling error of the tabular array.

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

After a successful data entry, we should meet it added to our database. Hither is an case of the tabular array to which we added our data when viewed from phpMyAdmin.

Determination

In this tutorial, yous have learned how to utilize PHP to INSERT information into your MySQL database using MySQLi and PDO. You have likewise learned how to troubleshoot common connection errors. Knowing how to use PHP to add together data into MySQL database is useful whether y'all are learning how to code or edifice your website.

Domantas leads the content and SEO teams forward with fresh ideas and out of the box approaches. Armed with extensive SEO and marketing knowledge, he aims to spread the word of Hostinger to every corner of the world. During his gratis time, Domantas likes to strop his web development skills and travel to exotic places.

tranweng1960.blogspot.com

Source: https://www.hostinger.com/tutorials/how-to-use-php-to-insert-data-into-mysql-database

Share this post