\n\n\n\n\n<\/p>
Create a Table for the Data<\/h2>
The first step is to create a table for the data. If you have already created one, scroll down to the next section. If not, follow these steps:<\/p>
- \n
- Open phpMyAdmin<\/strong> on your hosting control panel.<\/li>\n<\/ol>
![\"The](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2022\/03\/hpanel-databases-phpmyadmin-sidebar.png\")
<\/a><\/figure><\/div>- \n
- Open u123456789_mydatabase<\/strong> and navigate to the Create table<\/strong> section.<\/li>\n<\/ol>
![\"The](\"\/tutorials\/wp-content\/uploads\/sites\/2\/2017\/01\/The-main-page-of-phpMyAdmin-with-an-empty-website-with-no-table-entries-1024x279.png\")
<\/a><\/figure><\/div>- \n
- Name the table Students<\/strong> and write 4 <\/strong>in the Number of columns <\/strong>field. Click the Go<\/strong> button near the bottom of the page.<\/li>\n\n\n\n
- A new page will appear. Enter the necessary information for the table.<\/li>\n<\/ol>
![\"phpMyAdmin](\"\/tutorials\/wp-content\/uploads\/sites\/2\/2017\/01\/phpMyAdmin-page-while-creating-a-new-table-1024x248.png\")
<\/a><\/figure><\/div>- \n
- Click Save<\/strong> to create a new table.<\/li>\n<\/ol>
Here are a few explanations of the columns used:<\/p>
- \n
- Name<\/strong> – The column name. It will appear at the top of the table.<\/li>\n\n\n\n
- Type<\/strong> – The data type. You can set various values, including int<\/strong>, varchar<\/strong>, and string<\/strong>. For example, select varchar<\/strong> to enter a string type name, which uses letters, not numbers.<\/li>\n\n\n\n
- Length\/Values<\/strong> – The maximum entry length for a particular column.<\/li>\n\n\n\n
- Index<\/strong> – To enumerate table entries, which is required when configuring table relationships. We recommend always having one ID column when creating a table. We used the Primary <\/strong>index for our ID<\/strong> field and marked A_I<\/strong>, which means Auto Increment<\/strong>. It automatically lists the entries (1,2,3,4…).<\/li>\n<\/ul>
For more information about table structure and its available settings, refer to phpMyAdmin<\/a>’s official documentation.<\/p>
How to Insert Into MySQL Database Table<\/h2>
There are two methods to INSERT<\/strong> data into MySQL database – the PHP MySQLi method and the PHP Data Object (PDO) method.<\/p>
Inserting Data Using MySQLi Method<\/h3>
First, establish a connection to a database. When connected, proceed with the INSERT <\/strong>MySQL query. Here is a full PHP code example with the basic connection and insert methods:<\/p>
<?php\n$servername = \"localhost\";\n$database = \"u123456789_mydatabase\";\n$username = \"u123456789_myuser\";\n$password = \"PasSw0rd123@\";\n\/\/ Create a connection\n$conn = mysqli_connect($servername, $username, $password, $database);\n\/\/ Check the connection\nif (!$conn) {\n die(\"Connection failed: \" . mysqli_connect_error());\n}\necho \"Connected successfully\";\n$sql = \"INSERT INTO Students (name, lastName, email) VALUES ('Tom', 'Jackson', 'tom@jackson.tld')\";\nif (mysqli_query($conn, $sql)) {\n echo \"New record created successfully\";\n} else {\n echo \"Error: \" . $sql . \"<br>\" . mysqli_error($conn);\n}\nmysqli_close($conn);\n?><\/pre>Important!<\/strong> If you are setting up a database for remote use, the hosting won’t be on the same server as the database. You must set up remote MySQL access<\/a> first and use its server address instead of localhost<\/strong>.<\/p><\/div>\n\n\n\n<\/p>
Lines 2-12 <\/strong>of the code are for the actual connection to the MySQL database<\/a>. The following <\/strong>line looks like this:<\/p>
$sql = \"INSERT INTO Students (name, lastName, email) VALUES ('Tom', 'Jackson', 'tom@jackson.tld')\";<\/pre>The INSERT INTO<\/strong> is a statement that adds data to the specified MySQL database table. In the example above, we are adding data to the table Students<\/strong>.<\/p>
Between the parenthesis, the table column names specify where we want to add the values (name, lastName, email)<\/strong>. The script will add the data in the specified order. If we write (email, lastName, name)<\/strong>, the script will add the values in the wrong order.<\/p>
The next part is the VALUES<\/strong> statement. Here, we specify values in the previously selected columns. Our example would be name = Tom, lastName = Jackson, email = tom@jackson.tld<\/strong>.<\/p>
Additionally, users must set SQL queries between quotes. In our example, everything written in quotes after $sql = <\/strong>is an SQL query.<\/p>
Meanwhile, lines 14-15 <\/strong>of the code check if the query works and displays a success message:<\/p>
if (mysqli_query($conn, $sql)) {\n echo \"New record created successfully\";<\/pre>The final part, lines 16-18<\/strong>, displays a different message in case the query fails. It shows an error SQL message instead:<\/p>
} else {\n\necho \"Error: \" . $sql . \"<br>\" . mysqli_error($conn);\n\n}<\/pre>Inserting Data Using the PHP Data Object (PDO) Method<\/h3>
To use this method, establish a database connection first by creating a new PDO object<\/strong>.<\/p>
Since the connection to the MySQL database is a PDO object, you must use various PDO methods to prepare and run queries.<\/p>
Methods of objects are called like this:<\/p>
$the_Object->the_Method();<\/pre>
PDO allows users to prepare, evaluate, and correct the SQL code before executing. It can prevent a malicious person from performing a simplified SQL injection attack by typing SQL code into a form.<\/p>
\/\/ User writes this in the username field of a login form<\/p>
john\"; DROP DATABASE user_table;\n\/\/ The final query becomes this\n\"SELECT * FROM user_table WHERE username = john\"; DROP DATABASE user_table;<\/pre>
As a syntactically correct SQL code, the semi-colon makes DROP DATABASE user_table<\/strong> a new SQL query and deletes the user table. Fortunately, prepared statements do not allow the “<\/strong> and ;<\/strong> characters to end queries. Thus, a malicious instruction to DROP DATABASE<\/strong> would not work.<\/p>
Important!<\/strong> You should always<\/strong> use prepared statements when sending or receiving data from the database with PDO. <\/p><\/div>\n\n\n\n<\/p>
To use prepared statements, you must write a new variable that calls the prepare()<\/strong> method of the database object. The result will look like this:<\/p>
<?php\n$servername = \"localhost\";\n$database = \"u123456789_mydatabase\";\n$username = \"u123456789_myuser\";\n$password = \"PasSw0rd123@\";\n$sql = \"mysql:host=$servername;dbname=$database;\";\n$dsn_Options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];\n\/\/ Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object\ntry {\n $my_Db_Connection = new PDO($sql, $username, $password, $dsn_Options);\n echo \"Connected successfully\";\n} catch (PDOException $error) {\n echo 'Connection error: ' . $error->getMessage();\n}\n\/\/ Set the variables for the person we want to add to the database\n$first_Name = \"Tom\";\n$last_Name = \"Jackson\";\n$email = \"tom@jackson.tld\";\n\/\/ Here we create a variable that calls the prepare() method of the database object\n\/\/ The SQL query you want to run is entered as the parameter, and placeholders are written like this :placeholder_name\n$my_Insert_Statement = $my_Db_Connection->prepare(\"INSERT INTO Students (name, lastName, email) VALUES (:first_name, :last_name, :email)\");\n\/\/ Now we tell the script which variable each placeholder actually refers to using the bindParam() method\n\/\/ First parameter is the placeholder in the statement above - the second parameter is a variable that it should refer to\n$my_Insert_Statement->bindParam(\":first_name\", $first_Name);\n$my_Insert_Statement->bindParam(\":last_name\", $last_Name);\n$my_Insert_Statement->bindParam(\":email\", $email);\n\/\/ Execute the query using the data we just defined\n\/\/ The execute() method returns TRUE if it is successful and FALSE if it is not, allowing you to write your own messages here\nif ($my_Insert_Statement->execute()) {\n echo \"New record created successfully\";\n} else {\n echo \"Unable to create record\";\n}\n\/\/ At this point, you can change the data of the variables and execute again to add more data to the database\n$first_Name = \"John\";\n$last_Name = \"Smith\";\n$email = \"john.smith@domain.tld\";\n \n\/\/ Execute again now that the variables have changed\nif ($my_Insert_Statement->execute()) {\n echo \"New record created successfully\";\n} else {\n echo \"Unable to create record\";\n}<\/pre>In lines 24-26<\/strong>, we use the bindParam()<\/strong> method of the database object. There is also the bindValue() <\/strong>method, which works differently:<\/p>
- \n
- bindParam() <\/strong>– evaluates data when the execute()<\/strong> method is reached. The first time the script reaches an execute()<\/strong> method, it sees that $first_Name<\/strong> corresponds to “Tom”, binds that value, and runs the query. When the script reaches the second execute()<\/strong> method, it sees that $first_Name<\/strong> now corresponds to “John”, binds that value, and reruns the query with the new values. Note that we defined the query once and reused it with different data at different points in the script.<\/li>\n\n\n\n
- bindValue() <\/strong>– evaluates the data as soon as bindValue()<\/strong> is reached. Since the value of $first_Name<\/strong> was set to “Tom” when the bindValue()<\/strong> was reached, it will be used every time an execute()<\/strong> method is called for $my_Insert_Statement<\/strong>.<\/li>\n<\/ul>
Notice that we reuse the $first_Name<\/strong> variable and assign it a new value the second time. If you check your database after running this script, you have both defined names, despite the $first_Name<\/strong> variable equalling “John” at the end of the script.<\/p>
The bindParam<\/strong> method accepts parameters by reference, not by value. Users only need to call bindParam<\/strong> once, and the script will insert updated values into the database.<\/p>
You can read more about passing parameters in the PHP manual<\/a>.<\/p>
Confirming the Success<\/h3>
If the query that we ran and INSERT<\/strong> into MySQL database was successful, we would see the following message:<\/p>
Connected Successfully<\/pre>
New record created successfully<\/pre>
Troubleshooting Common Errors<\/h3>
Sometimes, the new record will show an error with the SQL insert. Luckily, there are ways to fix these MySQL errors.<\/p>
MySQLi<\/strong><\/p>
If a MySQLi error message appears, we can attempt various fixes.<\/p>
For example, if we have one syntax error in our code, we will see the following error:<\/p>
Connect successfully\n\nError: INSERT INTO students {name, lastName, email} VALUES ('Tom', 'Jackson', 'tom@jackson.tld')\n\nYou 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 ('Tom', 'Jackson', 'tom@jackson.tld')' at line 1\"<\/pre>As you can see, the first part of the code is good and the script established the connection successfully. However, the SQL query failed.<\/p>
\"Error: INSERT INTO Students {name, lastName, email} VALUES ('Tom', 'Jackson', 'tom@jackson.tld') 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 ('Tom', 'Jackson', 'tom@jackson.tld')' at line 1\"<\/pre>This is because a syntax error caused the script to fail. The error was here:<\/p>
$sql = \"INSERT INTO Students {name, lastName, email} VALUES ('Tom', 'Jackson', 'tom@jackson.tld')\";<\/pre>We incorrectly used curly brackets instead of simple parentheses, causing the script to throw a syntax error.<\/p>
PDO<\/strong><\/p>
To receive error messages for troubleshooting, the user must set the error mode to display all exceptions<\/strong>. That’s what’s written in line 12 <\/strong>of the PDO connection. Since all exceptions are enabled, any specific issue will be displayed.<\/p>
Users should only use all exceptions when developing a script. It can expose the database and table names, which the user should hide from malicious parties.<\/p>
In the case above, where curly braces were used instead of parentheses, the error looks similar to this:<\/p>
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 ('Tom', 'Jackson', 'tom@jackson.tld')' at line 1\"<\/pre>Other Potential Issues:<\/strong><\/p>
- \n
- Specifying incorrect columns like non-existent columns or a spelling mistake.<\/li>\n\n\n\n
- One type of value being assigned to another type of column. For example, if we tried to assign the number 47<\/strong> to a Name<\/strong> 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”<\/strong>, it would work because our number would be a string to the column.<\/li>\n\n\n\n
- Accessing remote MySQL database without Remote MySQL set up.<\/li>\n<\/ul>
All those errors can be easily fixed by following the error message guidelines or checking the error log.<\/p>
![\"Hostinger](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/11\/Web-hosting_in-text-banner.png\")
<\/a><\/figure>Conclusion<\/h2>
PHP programming language allows users to easily add new MySQL database data with a few lines of code.<\/p>
In this tutorial, we have shown how to use PHP to <\/strong>insert data into your <\/strong>MySQL database using MySQLi, INSERT<\/strong> statement, and PDO. We have also showcased how to troubleshoot common connection errors.<\/p>
We hope that you found this tutorial useful. If you have any questions, let us know in the comments below.<\/p>
\nLearn More About PHP<\/h4>\n
How to Install phpBB on Your Website<\/a>
\nHow to (Safely) Make A PHP Redirect<\/a>
\n11 Best PHP Frameworks<\/a>
\nHow to Create phpinfo File<\/a>
\nHow to Modify the PHP Max Upload Size<\/a><\/p>\n <\/div>\n<\/p>\n","protected":false},"excerpt":{"rendered":"Managing data within a MySQL database is crucial for web applications and websites. While there are several methods available, one powerful approach is by using PHP to interact with the database. By leveraging the capabilities of MySQLi and PHP Data Objects (PDO) extensions, users can seamlessly insert data into MySQL databases with enhanced security and […]<\/p>\n
- Accessing remote MySQL database without Remote MySQL set up.<\/li>\n<\/ul>
- bindValue() <\/strong>– evaluates the data as soon as bindValue()<\/strong> is reached. Since the value of $first_Name<\/strong> was set to “Tom” when the bindValue()<\/strong> was reached, it will be used every time an execute()<\/strong> method is called for $my_Insert_Statement<\/strong>.<\/li>\n<\/ul>
- Type<\/strong> – The data type. You can set various values, including int<\/strong>, varchar<\/strong>, and string<\/strong>. For example, select varchar<\/strong> to enter a string type name, which uses letters, not numbers.<\/li>\n\n\n\n
- Name<\/strong> – The column name. It will appear at the top of the table.<\/li>\n\n\n\n
- A new page will appear. Enter the necessary information for the table.<\/li>\n<\/ol>
- Name the table Students<\/strong> and write 4 <\/strong>in the Number of columns <\/strong>field. Click the Go<\/strong> button near the bottom of the page.<\/li>\n\n\n\n
- Open u123456789_mydatabase<\/strong> and navigate to the Create table<\/strong> section.<\/li>\n<\/ol>