PHP script for writing data to mysql. How to write to MySQL database using PHP code
In this article we will analyze, perhaps, one of the most important SQL queries. it 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 Add SQL Query new entry to the 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 start listing the values of those 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 are updating the record, the 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 the 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.
Last update: 1.11.2015
To add data, the "INSERT" expression is used:
$query ="INSERT INTO goods VALUES(NULL, " samsung galaxy III","Samsumg")";
The "INSERT" statement inserts one row into the table. After keyword INTO specifies the name of the table, and after VALUES the set of values for all columns is specified in parentheses. Since we have three columns in the table, we specify three values.
Since in the previous topic, when creating a table, we specified the following column order: id, name, company, in this case, NULL is passed for the id column, "Samsung Galaxy III" for name, and "Samsumg" for company.
Since the id column is defined as AUTO_INCREMENT, we do not need to specify a specific numeric value for it, and we can pass null value, and MySQL will assign the next available value to the column.
Now let's look at adding data using an example. Let's create a file create.php with the following content:
Data added"; ) // close connection mysqli_close($link); ) ?>
Add new model
Here, the code for interacting with the database is combined with the functionality of the forms: using the form, we enter data to be added to the database.
Security and MySQL
Here we have used the mysqli_real_escape_string() function. It serves to escape characters in a string, which is then used in SQL query. It takes as parameters a connection object and a string to be escaped.
Thus, we actually use character escape twice: first for the sql expression using the mysqli_real_escape_string() function, and then for html using the htmlentities() function. This will allow us to protect ourselves from two types of attacks at once: XSS attacks and SQL injections.
In this article, we'll look at how to use PHP to insert strings into a database. MySQL data.
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 that only one column be used as the primary key. It is used to list the records in a table and is required when setting up the table. I also noted "A_I", which means "Auto Increment" - the parameter for automatically assigning record numbers (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]")";
It inserts data into the 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 expressions, 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 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 reco rdcreatedsuccessfully"; ) 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"
Other possible problems:
- Columns are incorrectly specified (non-existent columns or a spelling error in their names).
- One type of value is assigned to a column of another type. For example, if you try to insert the number 47 into the Name column, you will get an error. This column must use a string value. But if we specified a number in quotes (for example, "47") it would work, 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 they have been added to the database. Below is an example of a table where data has been added.
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
All modules of a site or web application where it is required to enter and record some data (for example, name, age, address, etc.) use a simple mysql function INSERT INTO `name_base` (name,value1,value2) VALUES ('Vasya ','1','2');
All variables are entered into the database according to the values that we set in the first brackets. It is important to consider the encoding of the handler script, database, and configuration file. It is advisable to use the most common encoding UTF-8.
Keep in mind that there are two ways to write to the database.
First way if we do not initially set the cell names of the database tables. Then we must list all variables for each cell, namely how many cells are in the database table, so many variables must be listed in brackets after the VALUE value.
For example:
The database table has four cells. This means that after the VALUE (..) element, all four variables must be listed in parentheses. And one more thing: if the variable does not exist, let's say this is an optional parameter. Then we just write an empty value in quotes '',
"INSERT INTO `name_base` VALUES (NULL, `".$name."`,``,`2`)"; // the third empty value is written in quotes
But this request has some minor drawbacks. If you add one cell or two cells to a database table, then this query will return an error. Because in this method, enumeration of all cells in the query is mandatory.
Second way if after query INSERT INTO `name_base` (...) list all cells after database name. An example has already been discussed above. If you forgot, write again:
"INSERT INTO `name_base`(`name`,`value`,`value2`) VALUES (NULL, `".$name."`,``,`2`)";
Here we have listed all cells (name,value1,value2) . And if you add an additional two more cells to the database table, then the query syntax will not have to be changed. But unless we need to add at once in one request those very additional variables that we need for those very new created cells.
Such an error crashes very often, after a small change on the site. Let's say the administrator added an additional cell to the database, let's say status. But the script handler did not have time to change the module, or simply forgot. But some sites have a very complex structure, and it can take a lot of time and effort to find an error. Therefore, it is desirable to use the second method of writing to the database. Although this kind of mistake is more often made by novice web programmers.
php write to mysql database. Practical examples
So, now we got to the heart of the matter when working with database queries. We will do everything on practical examples. Let's create a simple script for recording comments that site visitors will leave.
First, let's create a table msg in the database with four cells. In the first cell we write the id of the comment. The number of characters in the cell is up to ten characters with the auto-increment parameter. This automatic setting will change each time you add a comment to +1.
The next cell name is the username. Number of characters - up to two hundred - three hundred characters of your choice, parameter char. Then the coment cell - we will enter the comment text itself into this cell. If you want to record large comment texts, then you can set the text parameter - then you can simply enter huge texts, more than five hundred thousand characters, or set the tinytext parameter, then a little less characters will fit, but it will work a little faster.
But in our case, we will keep in mind that visitors will not write huge texts. And therefore, we will limit ourselves and will fix two thousand characters with the varchar parameter, to write string values.
In the last cell we will write the date the comment text entry was made. We will write in numerical format in seconds, using the current date and time function time(); For simplicity, we will set the function to the variable $time=time(); And create a cell in the database. Let's name the same name time with the int parameter (for numeric values). Let's write down the number of characters - eleven is better (with a small margin for the future :-).
The database dump is as follows:
`msg` table structure -- CREATE TABLE IF NOT EXISTS `msg` (`id` int(10) NOT NULL AUTO_INCREMENT, `name` char(250) NOT NULL, `coment` varchar(2000) NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Everything, a table for comments has been created. Now we write a form in order to write a comment and the handler script itself. The HTML code for the comment form is as follows.
The lesson will be based on feedback form required on almost any site.
Step one: Creating a database in MySQL
We open phpMyAdmin(included in the basic package) Denver`a), and create a database called " test_base", choose encoding" cp1251_general_ci".
Step two: Create a table in MySQL by using SQL query
Of course, you can also create a table using standard means phpMyAdmin, but the structure of the created table will be clearly visible.
Create a table called " test_table" and with six fields with names:
"name" - usernames will be stored here;
"email" - addresses will be stored here email boxes users;
"theme" - the subject of the message will be stored here;
"message" - messages will be stored here;
"data" - the date the message was sent will be stored here;
"id" - identification number of the record (line), key field.SQL query:
create table test_table(
id int(11) not null auto_increment,
name varchar(255) not null,
email varchar(255) not null,
theme varchar(255) not null,
message text not null,
data date not null,
primary key (id)
);
Step three: Form creation
index.html:
MySQL persist form
Example of a form with saving data in MySQL