In this article, we will look at how to use PHP to insert rows into a MySQL database.
Step 1 - creating the table
First, you need to create a table for the data. This is a simple procedure that can be done using phpMyAdmin in your hosting control panel.
After logging in you 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 - the type of the column. For example, we chose varchar because we will be entering string values.
- Length / Values - used to indicate maximum length that the entry in this column can have.
- Index - we used the "Primary" index for the "ID" field. It is recommended that you use only one column as the primary key when creating a table. It is used to list records in a table and is required when setting up a table. I also marked "A_I", which stands for "Auto Increment" - the parameter for automatically assigning 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. Complete code example:
". mysqli_error ($ conn);) mysqli_close ($ conn);?>
The first part of the code (lines 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]")";
It inserts data into a MySQL database. INSERT INTO is a statement that adds data to the specified table. In our example, the data is added to the Students table.
Next comes the enumeration of the columns into which the values are inserted: name, lastname, email. The data will be added in the order shown. If we had written (email, lastname, name), the values would have been added in a different order.
The next part is the VALUES operator. Here we specify values for the columns: name = Thom, lastname = Vial, email = [email protected]
We ran a request using PHP code. In code, SQL queries must be quoted. The next part of the code (lines 20-22) checks if our request was successful:
if (mysqli_query ($ conn, $ sql)) (echo "New recordcreatedsuccessfully";)
This code displays a message about the successful completion of the request.
And the last part (22 - 24 lines) displays a notification if the request was not successful:
else (echo "Error:". $ sql. "
". mysqli_error ($ conn);)
Option 2 - PHP Data Object (PDO) Method
First, we need to connect to the database by creating a new PDO object. When working with it, we will use different methods PDO. 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 accomplished by simply injecting SQL 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 complete the original query, so 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 which variable refers to each placeholder 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 successful and FALSE if not, giving you the option to print your own message if ($ my_Insert_Statement-> execute ()) (echo "New reco rdcreatedsuccessfully "; ) else (echo "Unable to createrecord";) // At this point you can change the variable data and run a query to add other 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". Then it binds this value and runs the request. When the script reaches the second execute () method, it sees that $ first_Name now matches "John". Then it binds this value and starts the query again with new values. It's 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. Since $ first_Name was set to "Thom", when bindValue () is reached, it will be used every time execute () is called on $ my_Insert_Statement.
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 indicated 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" into the database twice and John Smith will be ignored.
Step 3 - Confirmation of Success and Problem Solving
If the request to insert rows into the database was successful, we will see the following message:
Eliminating common mistakes
MySQLi
Otherwise, 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 have used curly braces instead of the regular ones. This is incorrect and the script generated a syntax error.
PDO
On line 7 of the PDO connection, the error mode is set to "display all exceptions". If a different value was given and the request failed, we would not receive any error messages.
This setting should only be used when developing a script. When activated, the names of the database and tables may be displayed, which are better hidden for security reasons. In the case described above, when curly braces were used instead of regular braces, 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 "
Other possible problems:
- Columns are incorrect (non-existent columns or spelling error in their names).
- One type of value is assigned to a column of a different type. For example, if you try to insert the number 47 into the Name column, you will get an error. You must use a string value in this column. But if we had specified a number in quotes (for example, "47") it would have worked, because it is a string.
- An attempt was made to enter data into a table that does not exist. As well as a spelling error in the table name.
After successfully entering the data, we will see that it has been added to the database. Below is an example of a table to which data has been added.
In this post I want to tell you 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: the username and his email:
This form can be used to register a new user, to send news, to collect statistics, and for anything ... In general, a user fills in his data into this form: name and mail, clicks on a button and then the data goes into a 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 occured, please try again.";)
What's going on in this script? Let's figure it out now!
The data entered into the form is transferred by the POST method to the php script (which is written above), and using the global array $ _POST, the data is formed into the variables $ name and $ email:
$ name = $ _POST ["name"]; $ email = $ _POST ["email"];
After the variables are ready to be entered into the database, we compose a request. 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 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 you wish, 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 the field Your town, we will name the variable: $ city. And so in the data entry form, after:
Your E-mail:
add:
Your town:
In a php script, after:
$ email = $ _POST ["email"];
add:
$ city = $ _POST ["city"];
And of course, we add in the request too, like this:
$ result = mysqli_query ("INSERT INTO user (name, email, city) VALUES (" $ name "," $ email "," $ city ")");
Here's what you should end up with:
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 "The data was saved successfully!";) else (echo "An error occured, please try again.";)
As you can see, nothing complicated! If necessary, you can add another field, and more, and more ...
In this article, we will analyze perhaps some of the most important SQL queries... it queries to add and remove records from a database table... Since, VERY often it is necessary add new records to the table, and to do it in automatic mode, then this material is required for study.
To start SQL query to add a new record to the table:
INSERT INTO users (login, pass) values ("TestUser", "123456")
When adding an entry, the first command is " INSERT INTO", then the name of the table into 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 those fields that we have selected. After executing this query, a new record will appear in our table.
Sometimes it takes update a record in a table, for this there is the following SQL query:
UPDATE users SET login = "TestUser2", pass = "1234560" WHERE login = "TestUser"
This query is more complex, since it has the construction " WHERE", but more about it below. First comes the command" UPDATE"followed by the table name, followed by" SET"we describe the values of all the fields that we want to change. It would be simple, but the question arises:" Which record should you update?". For this there is" WHERE". In this case, we are updating the record, the field" login"which matters" TestUser". Please note that if there are several such records, then absolutely everything will be updated! This is very important to understand, otherwise you risk losing your table.
Let's talk a little more about " WHERE". In addition to simple tests 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 is very important. Exactly." WHERE"used when fetching records from tables, and this is the most frequently used task when working with databases.
And finally, a simple one SQL query to delete records from a table:
DELETE FROM users WHERE login = "TestUser2"
After the command " DELETE FROM"is the name of the table in which you want to delete records. Next, we describe the" WHERE "construction. If the record matches the described conditions, it will be deleted. WHERE", any number of them can be deleted.
In this lesson, we will learn how to enter the data entered by the user into the form into the MySql database. You will learn how to connect to the MySql database from the code of a web page, as well as process and insert data into the database.
In the last lesson, I told you how to install Denver on your computer, how to create your own database, a user for it, how to create a table in the database, and we filled it with one record.
In this lesson, we will create an html document for entering user information, as well as a php file that handles this information, which will process the data, connect to the MySql database and insert new records there.
Moving from words to deeds, let's get started.
Add custom data to MySql database from web page
First step: create html form for data entry
From the very beginning, we need to prepare for working with php, MySql and testing the written code on the local computer. For this:
1. Launch Denwer on your computer.
2. Open the virtual disk created by Denver (for example, I have this disk “Z”). Open it, then: home - localhost - www - there create a new folder and give it a name (I will give the name “db1”).
3. If you are working in Adobe Dreamweaver, then go to the site manager, there click on the "New" button, select the path to the newly created folder as the path, in the field above you can give a name to the site, then click Save - Done. After all this, your folder will appear in the right pane of Adobe Dreamweaver.
1. Now we will create a regular html file (let's call it info_form.html and save it in the db1 folder we just created) and write the form code there for entering information. Our users table in the new_db database has 4 fields that the user could fill in (the id field will be filled in automatically). Based on this, you can create code like this:
Here in the "action" parameter of our form there is a file form.php... It will be a handler file that we will create next.
Let's also create a style.css file to style our form. It is already included in the code, so all that remains is to create it, place it in the same folder as the html file and write the following code in it:
Body (width: 400px; margin: 0 auto; background: # F8F4B6;) label (display: block; float: left; width: 150px; padding: 0 10px; margin: 18px 0 0; text-align: right;) # submit (float: right; margin: 5px 50px 10px 0;)
The form now looks like this:
However, if you enter data and try to submit the form, you will see an error, since no form.php file in the scripts directory exists.
Creating a handler file is our next task.
2. Let's go inside the folder db1 create a folder with the name "Scripts"... In it we will save all our scripts for interacting with the MySql database.
After the folder is created, create a new php file and save it in the scripts folder under the name form.php.
But before you start directly with the information-handler file, you must first connect to our database. I suggest creating a separate file for this, and here's why:
Most likely in your project you will have to connect to the MySql database not only from one file. As a rule, various project files interact with the database.
It is very impractical to write the same database connection code in each such file. And if there are 50 such files, for example, and one day you will change the password for the user and you will have to search for all these 50 files and make corrections in each one.
It is much more convenient to use a separate file for connection, which will be connected to each of the files where there should be a connection to the MySql database. In this case, you will only need to make corrections in one file.
So let's create a new php file, let's name it connect.php and put it in the scripts folder. In it we will write down the code for connecting to our MySql database, as well as the code that will report errors if they occur. Please note that you will need to enter your username, your password and your database name as parameters. The host must be localhost.
Error connecting to database! ". mysql_error ()."
"); mysql_select_db (" new_db ") // parameter in brackets (" name of the base to connect to ") or die ("Database selection error! ". mysql_error ()."
"); ?>Now let's go to the form.php file and connect the connect.php file to it using the following code:
If you try to submit the form now, you will see a blank web page after submitting. This is normal, since we just connected to the base and do not display anything else yet.
If you see an error message, then check the correctness of all parameters (username, password, hostname: localhos, database name), and also be sure that your Denver is enabled.
3. We have successfully connected to the MySql database and now we need accept the data entered into the form into our file handler... The attributes "name", which are present in the html document, will help us with this.
To obtain data, we will use a special php variable that feeds us all the information from the web form. This variable is named “$ _REQUEST”.
After connecting the connect.php file, add the following code in the form.php file:
$ first_name = $ _REQUEST ["first_name"]; $ last_name = $ _REQUEST ["last_name"]; $ email = $ _REQUEST ["email"]; $ facebook = $ _REQUEST ["facebook"];
4. We received the data in the file and put them into variables. Now you need send this data to our MySql database table... To do this, you need to write the following code below:
$ insert_sql = "INSERT INTO users (first_name, last_name, email, facebook)". "VALUES (" ($ first_name) "," ($ last_name) "," ($ email) "," ($ facebook) ");"; mysql_query ($ insert_sql);
Here, we first form a query, saying that we need to insert the appropriate variables into the corresponding fields of the “users” table. We put this query into the variable “$ insert_sql”. And then, using the special function mysql_query, we execute this query.
If now you fill out and submit the form, and then look at the “users” table of your database, you will see a new record that appeared there after the submission.
5. We have inserted a new record from the web page into the MySql database table. Now I want to touch a little on the format in which our data is inserted.
First, you need to take care of the situation when a person filling out the form may put unnecessary spaces at the beginning of the information entry. They need to be cut.
Secondly, we have a Facebook field. If in the future we want to display this information as a link, then we must take care that the data in this field is stored in the correct format (ie "http://www.facebook.com/ facebook id"). But the user will not always enter this data as we need it. He can enter: “www.facebook.com/ facebook id”, “facebook.com/ facebook id” or simply “/ facebook id”.
These situations should be avoided. To do this, let's tweak our code. We cut off possible extra spaces using the function trim, and the function will check the correctness of the entered url for facebook preg_match... Thus, the entire code of the form.php file will look like this:
About function preg_match:
This is a function for searching with regular expressions.
The first parameter in parentheses is the regular expression, the second is the string to search for.
The function returns only the first match: 0 - if there are no matches, 1 - if there is a match.
Now try to deliberately fill in the form, making unnecessary spaces at the beginning of filling in any of the fields and entering the address for facebook without http: // or even entering only the facebook ID. Send a request, and then go to the database and you will see that, despite the not entirely correct filling, the data has exactly the form that we need.
On this I will end this lesson. You can download all the files of this lesson in the source (just do not forget to change the parameters to your own in the connect.php file).
And in the next lesson, we will learn how to retrieve information from the MySql database, select required entries and display them on the screen. In order not to miss the next lesson, subscribe to blog updates using the subscription form that you see below.
Leave your comments and share with your friends using the social media buttons.
Good luck and see you in the next lesson!
In this tutorial, we will look at how to insert data into the database directly from your PHP scripts.
Inserting data using SQL
You use SQL to insert data into the database in the same way you use SQL to create databases and tables. The syntax for the SQL query is as follows:
INSERT INTO TableName (column1, column 2, ...) VALUES (value1, value 2, ...)
As you can see, you can update multiple columns in a single SQL statement by specifying them in a comma separated list. But of course, you can also specify only one column and one value. Columns not mentioned in this SQL statement will remain empty.
Example: Inserting a new person into a table
In this example, we are using the database from Lesson 18. Let's say we want to insert a person into the database. It could be Gus goose with phone number 99887766 and date of birth 1964-04-20 .
The SQL statement might look like this:
$ strSQL = "INSERT INTO people (FirstName, LastName, Phone, BirthDate) VALUES (" Gus "," Goose "," 99887766 "," 1964-04-20 ")";
As you can see, SQL statements can be quite long and you can easily get lost. Therefore, it is better to write the SQL statement slightly differently:
strSQL = "INSERT INTO people ("; strSQL = strSQL. "FirstName,"; strSQL = strSQL. "LastName," strSQL = strSQL. "Phone,"; strSQL = strSQL. "birth)"; strSQL = strSQL. "VALUES ("; strSQL = strSQL. "" Gus ","; strSQL = strSQL. "" Goose ","; strSQL = strSQL. "" 99887766 ","; strSQL = strSQL. "" 1964-04-20 ")"; mysql_query ($ strSQL) or die (mysql_error ());
Here the SQL statement is built with dividing the statement into small parts and then concatenating them into a variable. $ strSQL.
In practice, there is no difference in applying one method or the other, but when working with large tables, it becomes extremely important to be able to "keep track", so choose the most appropriate method.
Let's try the following code to insert Gus Goose into the database:
The database has been updated!
Saving user input to the database
You probably already realized that you can create a form for this, as in Lesson 11, and values from the form can be inserted into an SQL statement. Suppose you have simple form:
This form is submitted to a file insert.php where, as shown in Lesson 11, you can get user input by requesting the content of the form. In this particular case, the SQL statement can be like this:
strSQL = "INSERT INTO people (FirstName) values (" ". $ _POST [" FirstName "]." ")"
Similarly, you can request data for cookies, sessions, query strings, etc.
Most common beginner mistakes
At first, you will probably get a bunch of error messages when trying to update the database. When working with a database, no errors are completely allowed. An incorrectly placed comma may mean that the database is not being updated, and you will receive an error message. Below we describe the most common mistakes.
Invalid data type
It is important that the data and data type of the column match. Each column can contain data of a certain type. The following screenshot shows the data types of the "people" table from our example.
An error is thrown if, for example, you try to insert text or a number into a data field. Therefore, set the data type as precisely as possible.
The most common data types are listed below:
| Meaning | Data Type | The size |
|---|---|---|
| CHR |
Text or a combination of text and numbers. It can also be used for numbers not used in calculations (eg telephone numbers). | Up to 255 characters - either the length specified in "Length" |
| TEXT |
Large blocks of text or a combination of text and numbers. | Up to 65,535 characters |
| INT |
Numerical data for mathematical calculations. | 4 bytes |
| DATE |
Dates in YYY-MM-DD format | 3 bytes |
| TIME |
Time in hh: mm: ss format | 3 bytes |
| DATETIME |
Date and time in YYY-MM-DD format hh: mm: ss | 8 bytes |
SQL statements with quotes or backslashes
If you try to insert text containing single quote ("), double quote ("), or backslash (\) characters, the record will not be inserted into the database. The solution is to substitute backslashes in front of characters that should be mnemonized when inserted into database queries.
