How to write data to mysql. Adding data in PHP. Saving user input to the DB

In this article we will analyze, perhaps, one of the most important SQL queries. This queries to add and delete records from a database table. Because, VERY often add new records to the table, and do it in automatic mode, then this material is required to be studied.

To start SQL query to add a new record to a table:

INSERT INTO users (login, pass) values("TestUser", "123456")

When adding a record, the first command is " INSERT INTO", then the name of the table in which we insert the record. Next comes the names of the fields that we want to fill in parentheses. And then in parentheses after the word " values"we begin to list the values of the fields that we have selected. After executing this query, a new record will appear in our table.

Sometimes required update table entry, for this there is the following SQL query:

UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"

This request is more complex, since it has the construction " WHERE", but about it a little lower. First comes the command" UPDATE", then the table name, and after " SET" we describe the values of all fields that we want to change. It would be simple, but the question arises: " Which entry should be updated?". For this there is " WHERE". IN this case we update record, field " login"which matters" TestUser". Please note that if there are several such records, then everything will be updated! This is very important to understand, otherwise you risk losing your spreadsheet.

Let's talk a little more about WHERE". In addition simple checks for equality, there are also inequalities, as well as logical operations: AND And OR.

UPDATE users SET login = "TestUser2", pass="1234560" WHERE id< 15 AND login="TestUser"

The SQL query will update those records id which are less 15 AND field " login" has the meaning " TestUser". I hope you figured out the design" WHERE"because it's very important. Precisely" WHERE" is used when fetching records from tables, and this is the most frequently used task when working with databases.

And finally, simple SQL query to delete records from a table:

DELETE FROM users WHERE login="TestUser2"

After command " DELETE FROM" goes the name of the table in which you want to delete records. Next, we describe the "WHERE" construction. If the record meets the described conditions, it will be deleted. Again, pay attention, depending on the number of records that satisfy the condition after " WHERE", any number of them can be deleted.

In this article, we'll look at how to use PHP to insert rows into a MySQL database.

Step 1 - Creating a Table

First you need to create a table for the data. This is a simple procedure that can be performed with using phpMyAdmin in the hosting control panel.

After logging in to phpMyAdmin you will see an interface like this:
Let's create a table named Students in the u266072517_name database by clicking on the "Create Table" button. After that we will see new page, on which we set all the necessary table parameters:

This is the most easy setup, which can be used for table and get additional information about the structure of tables/databases.

Column options:

Name is the name of the column that appears at the top of the table.
Type is the type of the column. For example, we chose varchar because we will be entering string values.
Length/Values - used to specify the maximum length that an entry in this column can have.
Index - We used a "Primary" index for the "ID" field. When creating a table, it is recommended to use as primary key only one column. It is used to list the records in a table and is required when setting up the table. I also marked "A_I", which stands for "Auto Increment" - the option to automatically assign the number of records (1,2,3,4...).
Click the Save button and the table will be created.

Step 2. Writing PHP code to insert data into MySQL.

Option 1 - MySQLi Method

First you need to establish a connection to the database. After that, we use the SQL INSERT query. Full code example:

" . mysqli_error($conn); ) mysqli_close($conn); ?>

The first part of the code (line 3 - 18) is for connecting to the database.

Let's start with line #19:

$sql = "INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")";

She inserts data into MySQL database. INSERT INTO is a statement that adds data to the specified table. In our example, data is added to the Students table.

Next comes the enumeration of the columns into which values are inserted: name, lastname, email. The data will be added in the specified order. If we had written (email, lastname, name), the values would have been added in a different order.

The next part is the VALUES statement. Here we specify the values for the columns: name = Thom, lastname = Vial, email = [email protected].

We have run a query using PHP code. In code, SQL queries must be escaped with quotation marks. The next part of the code (line 20-22) checks if our request was successful:

if (mysqli_query($conn, $sql)) ( echo "New recordcreatedsuccessfully"; )

This code displays a message that the request was successful.

And the last part (line 22 - 24) displays a notification if the request was not successful:

else ( echo "Error: " . $sql . "
" .mysqli_error($conn); )

Option 2 - PHP Data Object Method (PDO)

First we need to connect to the database by creating a new PDO object. When working with it, we will use various PDO methods. Object methods 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. For example, the simplest SQL injection attack can be performed by simply entering SQL code into a form field. For example:

Since this is syntactically correct SQL, the semicolon makes DROP DATABASE user_table a new SQL query and the user table is dropped. Prepared expressions (bound variables) do not allow semicolons and quotes to terminate the original query. Therefore, the DROP DATABASE command will never be executed.

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

Correct code:

getMessage(); ) // Set variables for the person we want to add to the database $first_Name = "Thom"; $last_Name = "Vial"; $email = " [email protected]"; // Create a variable that calls the prepare() method of the database object // The SQL query you want to execute is entered as a 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 what variable each placeholder refers to in order to use the bindParam() method // The first parameter is the placeholder in the statement above , the second is the variable 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 succeeded and FALSE if it didn't, leaving you to print your own message if ($my_Insert_Statement->execute()) ( echo "New recordcreatedsuccessfully"; ) else ( echo "Unable to createrecord"; ) // At this point, you can change the variable data and run a query to add more data to the database data to the database $first_Name = "John"; $last_Name = "Smith"; $email = " [email protected]"; $my_Insert_Statement->execute(); // Execute again when the variable is changed if ($my_Insert_Statement->execute()) ( echo "New recordcreatedsuccessfully"; ) else ( echo "Unable to createrecord";

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

bindParam() - This method evaluates the data when the execute() method is reached. The first time the script reaches the execute() method, it sees that $first_Name matches "Thom". It then binds that value and runs the query. When the script reaches the second execute() method, it sees that $first_Name now matches "John". Then it binds this value and runs the query again with new values. It is important to remember that we once defined a query and reuse it with different data at different points in the script.
bindValue() - This method evaluates the data as soon as bindValue() is reached. Because $first_Name was set to "Thom", when bindValue() is reached, it will be used every time the execute() method on $my_Insert_Statement is called.
Note that we are reusing the $first_Name variable and assigning a new value to it a second time. After running the script, both names will be listed in the database, despite the fact that the $first_Name variable at the end of the script has the value "John". Remember that PHP checks the entire script before running it.

If you update the script to replace bindParam with bindValue, you will insert "Thom Vial" twice into the database and John Smith will be ignored.

Step 3 - Confirm Success and Resolve Issues

If the request to insert rows into the database was successful, we will see the following message:

Troubleshooting Common Errors

MySQLi

In any other case, an error message will be displayed. For example, let's make one syntax error in the code, and we get the following:

The first part of the code is fine, the connection was successfully established, but the SQL query failed.

"Error: INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]") You have an error in your SQL syntax; check the manualthatcorresponds to your MySQL server version for the rightsyntax to use near "(name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")" at line 1"

There was a syntax error that caused the script to fail. The error was here:

$sql = "INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")";

We used curly braces instead of normal braces. This is incorrect and the script gave a syntax error.

PDO

On line 7 of the PDO connection, the error mode is set to "display all exceptions". If another value was set and the request would fail, we would not receive any error messages.

This setting should only be used when developing a script. When enabled, database and table names may be displayed, which are best kept hidden for security reasons. In the case described above, when curly brackets were used instead of regular brackets, the error message looks like this:

Fatal error: Uncaughtexception "PDOException" with message "SQLSTATE: Syntax error or accessviolation: 1064 You have an error in your SQL syntax; check the manualthatcorresponds to your MySQL server version for the rightsyntax to use near "(name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")" at line 1"

Conclusion

In this article, we have covered how to use PHP to insert data into a MySQL database using MySQLi and PDO. And also how to fix common mistakes. This knowledge will be useful when learning programming and when developing your own website.

This publication is a translation of the article " How to Use PHP to Insert Data Into MySQL Database» prepared by a friendly project team

In this post I want to tell how to transfer to the database, the entered data in the form. And so we create a simple form where we will have two fields: a username and his email:

This form can be used to register a new user, to send out news, to collect statistics, and for anything ... In general, the user fills in this form with his data: name and mail, clicks on the button and then the data goes into the php script:

$name = $_POST["name"]; $email = $_POST["email"]; $result = mysqli_query("INSERT INTO user (name, email) VALUES ("$name", "$email")"); if ($result) ( echo "Data saved successfully!"; ) else ( echo "An error occurred, please try again."; )

What happens in this script? Now let's figure it out!
The data entered into the form is transferred to the php script (which is written above) using the POST method, and using the $_POST global array, the data is formed into the $name and $email variables:

$name = $_POST["name"]; $email = $_POST["email"];

After the variables are ready to be entered into the database, we compose a query. But first, your scripts must already be connected to the database, how to connect to the database, I wrote in this thread:. The request itself looks like this:

$result = mysqli_query("INSERT INTO user (name, email) VALUES ("$name", "$email")");

In this code, we have indicated that the following variables will be added to the name and email cells that are in the user table: $name and $email .
Further, if everything went well, we will receive a message from the condition:

Data saved successfully!

If there were any problems and the data was not entered, we will receive an error message:

An error has occurred, please try again.

That's all!

*** *** *** *** ***

If desired, you can add more fields for entering information, for example, we need to add a field for entering the user's city. We already have a ready-made script (written above), now we just add a field Your city, let's name the variable: $city . And so in the data entry form, after:

Your E-mail:

add:

Your city:

In php script, after:

$email = $_POST["email"];

add:

$city = $_POST["city"];

And of course, we also add it to the request, like this:

$result = mysqli_query("INSERT INTO user (name, email, city) VALUES ("$name", "$email", "$city")");

Here's what the end result should be:
Input form:

Script:

$name = $_POST["name"]; $email = $_POST["email"]; $city = $_POST["city"]; $result = mysqli_query("INSERT INTO user (name, email, city) VALUES ("$name", "$email", "$city")"); if ($result == true) ( echo "Data saved successfully!"; ) else ( echo "An error occurred, please try again."; )

As you can see, nothing complicated! If necessary, you can add another field, and another, and another ...

Comments moved from the blog

SERGEY
09/14/2016 at 01:25
Good afternoon!
Interested in this question: what is the easiest way to organize the storage of data and program settings without using a database? Don't want to be tied to MySQL or Access..

ADMIN
09/14/2016 at 22:14
Hello!

Properties.Settings
App.Config
XML file
serialization
Try picking one of these from the list.

NIKOLAI
09/16/2016 at 02:28
Hello, how can I delete the highlighted row in dataGridVIew from dataGridVIew and phpMyAdmin.

PhpMyAdmin? This is just a shell for working with the database, can you explain?

NIKOLAI
09/18/2016 at 02:24
it is necessary that the selected row be deleted from the DataGridView and from the database.

ADMIN
09/19/2016 at 07:00
How to delete a row in a Mysql database - added an article.

NIKOLAI
09/20/2016 at 09:20
Thanks a lot.

DIMA
09/20/2016 at 10:24
Hello, is it possible to implement this method not through the DataGridView, but through the ComboBox? If so, how? Thank you.

ADMIN
09/22/2016 at 03:21
Hello. Example:

GENNADY
09/22/2016 at 18:25
why should I add such text to the database System.Windows.Forms.TextBox, Text: ge

By the way, this (ge) at the end it is written by the gene, even though the text is specified in the table settings. The word of the gene should have fit further, I display this table in my program and it turns out that it displays all this unnecessary text for me

ADMIN
09/24/2016 at 04:17
Most likely the SQL query is written incorrectly, for example:

In textBox1 we enter the name: Gene.

Sql query: "Insert into table name values(textBox1, ..)"; Result: System.Windows.Forms.TextBox

And you need to pass: "Insert into table name values(textBox1.Text, ..)";
Result: Gena

GENNADY
09/24/2016 at 18:41
That is how it is. Thank you

SERGEY
25.09.2016 at 11:51
Hello. And how to implement adding to the database through textBox?

ADMIN
09/26/2016 at 20:53
Everything is the same in principle. For example, let's take the most recent example, it needs:

//create parameters and add them to the collection cmd.Parameters.AddWithValue("@Name", textBox1.Text); cmd.Parameters.AddWithValue("@LastName", textBox2.Text);

now the parameters: Name and LastName receive the values entered in the textboxes and pass them to the database

LINARA
09/27/2016 at 17:45
Hello, how is the highlighted row in dataGridVIew and phpMyAdmin?

ADMIN
09/29/2016 at 02:06
I don't know how you can select a row in phpMyAdmin. And in the dataGridView, for example, this can be done using the SelectionChanged event.

PSH
09/30/2016 at 03:48
2Linara:
If you really want to edit rows, take a tool a la HediSQL, tweak and modify rows.

2admin
Good day! Thank you for the materials - everything is very coolly stated)
Question: I add data with the following request (it is a test one):

String sql = "INSERT INTO users (`FIO`, `Tour`, `Count`, `Cost`, `Date`, `Passport`, `Birth`) VALUES ("Kolyan", "Moscow", "1+1 ", 1100, "2011-11-11", "1111 1111", "11/9/1900");";

The data is entered all ok, but in the database (mysql) instead of Cyrillic they turn out to be “????”.

Visual studio says System.String is a unicode sequence.

Also tried:

ALTER DATABASE `test` COLLATE "koi8r_general_ci"; ALTER TABLE `users` COLLATE="koi8r_general_ci"; ALTER DATABASE `test` COLLATE "utf8_unicode_ci"; ALTER TABLE `users` COLLATE="utf8_unicode_ci";

But it doesn't help..
What can be wrong? Different VS and DB encodings? Or what?
Could send what to read / change.
Thank you

ADMIN
01.10.2016 at 09:49
Hello.

In the DB (and in the table) the utf_general_ci collation

Is there such a comparison? Perhaps utf8_general_ci?

Usually they create a Mysql database by choosing the utf8_general_ci comparison, so there are no problems with the Cyrillic alphabet, unless, of course, the server does not receive bugs from the client.

COLLATION is used for comparison, but in this case, the encoding (charset) is important. Therefore, first you need to make sure that it is set correctly on the server, for example, in utf8, and not latin1.

When connecting via the .net connector (by default), latin1 is used, so sometimes you need to explicitly specify the utf8 encoding in the connection string:

MySqlConnection mycon; mycon = new MySqlConnection("server=127.0.0.1;uid=vasya;pwd=123;database=test;Charset=utf8;"); //MySqlConnectionStringBuilder: mysqlCSB.CharacterSet = "utf8";

PSH
01.10.2016 at 11:34
You are right, described yourself, utf8_general_ci!
Yes it helped, ;Charset=utf8;
Thank you very much!

SERGIUS
02.10.2016 at 11:02
Thanks for the working example. Question
I have created a text field in which I would like to enter the IP address of the database, but I do not know how to substitute this data here

String conStr = " [email protected];user=test;" +
"database=test;password=test;";
Please tell me how to insert data from text fields in windows form into this design….

ADMIN
03.10.2016 at 11:50
"[email protected];user=...
In general, it is better to use properties instead of such a string, as in this article, or the String.Format() method

OLGA2203
05/15/2017 at 20:14

String Connect = “Server=127.0.0.1;Port=3306;Database=base;Data Source=localhost;user=root;”; MySqlConnection con = new MySqlConnection(Connect); con.Open(); //Establish a connection to the database. MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = @”INSERT INTO tovar(ID,Category,Name,TradeMark,Price,Photo,Size,Color,Material,Count) VALUES (@pr, @Category, @Name, @TradeMark, @Price, @Photo, @Size, @Color, @Material, @Count)”; cmd.Parameters.AddWithValue("@pr",counter); cmd.Parameters.AddWithValue(“@Category”, comboBox1.SelectedItem.ToString()); cmd.Parameters.AddWithValue(“@Name”, textBox1.Text); cmd.Parameters.AddWithValue(“@TradeMark”, textBox2.Text); cmd.Parameters.AddWithValue(“@Price”, Convert.ToInt32(textBox4.Text)); cmd.Parameters.AddWithValue(“@Photo”, textBox3.Text); cmd.Parameters.AddWithValue(“@Size”, textBox6.Text); cmd.Parameters.AddWithValue(“@Color”, textBox5.Text); cmd.Parameters.AddWithValue(“@Material”, textBox8.Text); cmd.Parameters.AddWithValue(“@Count”, Convert.ToInt32(textBox7.Text)); cmd.Connection = con; cmd.ExecuteNonQuery(); MessageBox.Show(“Adding successful”, “Adding successful”, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

The error “Column 'ID' cannot be null” is thrown, I remove the addition to the ID column - the same is written about the next column, etc.
If I enter any constant values in brackets in VALUES, the line is added to the base.
Tell me, please, what is the problem? I need to write to the database exactly the data and values entered through the form

In this guide, you will learn how to start managing a database from your PHP script. you will learn adding a record to a MySQL table, using php code. Before you get started, check out our other tutorials that cover the basic steps for working with PHP and databases - connecting from PHP to a MySQL database.

Before starting, check for the following:

Access to your hosting control panel

Step 1 - Creating a Table

First of all, we need to create a table for your data. This is a very simple procedure that you can do in phpMyAdmin from your hosting control panel. We have already covered the process of creating a MySQL database in a previous tutorial, so we will skip that point here.

After entering the phpMyAdmin page, you will see a similar picture:

Let's create a table called Students for our database u266072517_name. You can create a new table by clicking the button Create Table. After that, you will see a new page where you can enter all the required data for your table:

This is the easiest way to create a table, for more information about the table/database structure and what settings can be used for each field, please refer to the official phpMyAdmin documentation.

Here are a few simple explanations of the fields we will be using:

Name is the name of your field. It will be displayed at the very top of your table.
type– here you can set the field type. For example, we choose varchar, because here we need to enter a string with a name (which has letters, not numbers).
Length/Values– is used to set the maximum length of your record in this field.
Index– we use the “Primary” index for our “ID” field. When a table is created, it is recommended to have one ID field. It is used to index records in a table when relationships between tables are set up. It can also be noted here “A_I”, which means Auto Increment. This setting will automatically increment the index (1,2,3,4…).

Click Save and your table will be created.

Step 2 — Generating the PHP Code and Adding a Record to the MySQL Table

Option 1 - MySQLi method

First of all, you need to establish a connection to the database, according to our previous guide. After that, we can continue with the SQL query to add a record to the MySQL table − INSERT. Here's a complete code example with a connection and an insert method:

" . mysqli_error($conn); ) mysqli_close($conn); ?>

Thus, the first part of the code (lines 3 – 18 ) are part of establishing a connection to the database. We won't go through this part again, if you want to know what each line means, refer to our previous tutorial on how to connect to a database.

Let's start with the line 19 :

$sql = "INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")";

This is the most important line of code, it does everything we describe in this tutorial - adding a MySQL table entry to the database. INSERT INTO is an expression that adds a record to the specified MySQL database table. In our example, we add data to the table Students.

Moving on, in brackets, we define the fields of the table, the values to which we will add: (name, lastname, email). The data will be added in a specific order. If we write (email, lastname, name), the values will be added in a different order.

The next part of the value VALUES. Here we set our values to the previously specified fields. Thus, each field will get its own value. For example, in our case it would be something like: name=Thom, lastname=Vial, email= [email protected] .

It is important to note that here we form SQL query using PHP code. SQL queries must be enclosed in quotes. In our example, everything between the quotes and after $sql = is an SQL query.

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

If (mysqli_query($conn, $sql)) ( echo "New record created successfully"; )

A success message is displayed if the query was run correctly.

And the final part 22 – 24 lines) show another message, in case our request fails:

Else ( echo "Error: " . $sql . "
" .mysqli_error($conn); )

This code displays an error message to us in case something went wrong.

Option 2 - PHP Data Object Method (P HP Data O bject)

As in the previous example, we first need to make a database connection, which is done when creating a new PDO object - the previous tutorial explains how to do this. Since the MySQL database connection is a PDO object, we must use various PDO ‘methods’ (kind of functions that are part of a particular object) to prepare and run the query. Object methods are called like this:

$the_Object->the_Method();

PDO allows you to 'prepare' SQL code before executing it. The SQL query is evaluated and adjusted before being run. Thus, a simple SQL injection attack can be performed by filling in SQL code in a form field. For example:

// User writes this in the username field of a login form thom"; DROP DATABASE user_table; // The final query becomes this "SELECT * FROM user_table WHERE username = thom"; DROP DATABASE user_table;

Since the SQL code is syntactically correct, the semicolon makes DROP DATABASE user_table new SQL query and your user table is dropped. Prepared expressions don't allow characters “ And ; to complete the original request, and the instruction DROP DATABASE will never be executed.

Always use prepared queries when you send or receive data from a database with PDO.

To use prepared expressions, you need to create a new variable that will call the method prepare() on the database object.

The correct code looks like:

$servername = "mysql.hostinger.com"; $database = "u266072517_name"; $username = "u266072517_user"; $password = "buystuffpwd"; $sql = "mysql:host=$servername;dbname=$database;"; $dsn_Options = ; // 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 = "Thom"; $last_Name = "Vial"; $email = " [email protected]"; // 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 = " [email protected]"; $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 method bindParam() database object. There is also a method bindValue(), different from the previous one.

bindParam() - this method counts the data when the method execute() reached. The first time the script reaches the method execute() he sees that $first_name refers to "Thom", binds that value, and executes the query. When the script gets the second time to the method execute() he looks that $first_name now refers to "John", binds that value, and runs the query again with the new value. It is important to understand that we create a request once and then substitute different data in different places in the script.
bindValue() - this method evaluates the data as soon as the queue reaches it. Since the value $first_name was given as "Thom", at the moment we reached the method bindValue(), it will be used when calling the method execute() For $my_Insert_Statement.

Note that we are reusing the variable $first_name and give it a new value a second time. If you check your database after running this script, both of the given names will be there, despite the value of the variable $first_name will be equal to "John" at the end of this script. Remember that PHP evaluates the contents of a script before running it.

If you change your script by replacing bindParam on bindValue, you will add "Thom Vial" to the MySQL database twice and John Smith will be ignored.

Step 3 - Checking for success and resolving common issues

If the query we ran against the MySQL database was successful, we will see a message like this:

Solving Common Mistakes

MySQLi

In any other case, an error message will be shown instead of the above message. For example, let's make one syntax error in our code and we get this:

As we can see, the first part of the code is ok, the connection was successfully established, but our SQL query failed while executing.

"Error: INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]") 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", " [email protected]")" at line 1"

There is a syntax error that causes our script to fail. The error was here:

$sql = "INSERT INTO Students (name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")";

As you can see, we are using curly braces instead of round braces. This is incorrect and results in a syntax error in our script.

PDO

In line 7 of the PDO connection, the error handling mode is set to 'display all exceptions'. If you take this out of the script and the request fails, you won't get any error message. With exceptions enabled, the specific problems encountered will be displayed. In general, this is best used when developing a script, as it can reveal database and table names that you would like to hide from someone who might gain unauthorized access to your data. In the case above, when curly braces were used instead of round braces, the error looks like below:

Fatal error: Uncaught exception "PDOException" with message "SQLSTATE: Syntax error or access violation: 1064 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", " [email protected]")" at line 1"

Other problems you may encounter:

Incorrectly specified fields (non-existent fields or spelling errors).
Value type mismatch with field type. For example, when we want to assign the value of a number 47 field Name, we'll get an error because the value is supposed to be a string. But, if you specify a number in quotes, for example, “47” , there will be no error, because our number will be written as a string in this field.
An attempt was made to enter data into a table that does not exist, or a spelling error in the table name.

All of these errors can be fixed by following the troubleshooting guides or by checking the error log.

After successfully adding the data, we should see it in our database. Here is an example of a table where we have added our data, as viewed in phpMyAdmin.

Conclusion

In this guide, you learned how to use PHP code to add record to MySQL table using MySQLi And PDO. Also considered cases of common errors and their solutions. Knowing how to use PHP code to add to a MySQL database will come in handy whether you're learning to code or are already building your own website.

Thematic materials: