How to use PHP to insert rows into a MySQL database. This lesson will be especially useful for beginners: How to record from php to mysql

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 done using phpMyAdmin in your hosting control panel.

After logging into phpMyAdmin you will see this interface:
Let’s create a table named Students in the u266072517_name database by clicking on the “Create Table” button. After this, we will see a new page on which we set all the necessary table parameters:

This is the simplest setting that can be used on a table and get more information about the table/database structure.

Column options:

Name is the column name that appears at the top of the table.
Type — column type. 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 the "Primary" index for the "ID" field. When creating a table, it is recommended to use only one column as the primary key. It is used to list the records in the 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: Write PHP code to insert data into MySQL.

Option 1 - MySQLi method

First you need to establish a connection to the database. After this 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 intended to connect to the database.

Let's start with line number 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 a specified table. In our example, data is added to the Students table.

Next is a list of columns into which the values are inserted: name, lastname, email. The data will be added in the order specified. 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 ran the request using PHP code. In program code, SQL queries must be escaped with quotes. The next piece of code (line 20-22) checks if our request was successful:

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

This code displays a message indicating 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 (PDO) Method

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 as follows:

$the_Object->the_Method();

PDO allows you to "prepare" SQL code before it is executed. The SQL query is evaluated and "corrected" before running. For example, a simple SQL injection attack can be carried out 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 execute.

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 database object's prepare() method // The SQL query you want to run is entered as a parameter, and the 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)"); // We now 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 of printing your own if message ($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". Then 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 runs the query again with new values. It is important to remember that we have defined a query once and reuse it with different data at different points in the script.
bindValue() - This method evaluates the data once bindValue() is reached. Since $first_Name was set to "Thom", when bindValue() is reached, it will be used every time the execute() method is called on $my_Insert_Statement.
Notice that we are reusing the $first_Name variable and assigning it a new value 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 it runs.

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 - Confirm Success and Resolve Problems

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'll get the following:

The first part of the code is ok, the connection was established successfully, 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 manual that corresponds to your MySQL server version for the rights syntax to use near "(name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")" at line 1"

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

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

We used curly braces instead of regular ones. This is incorrect and the script generated a syntax error.

PDO

Line 7 of the PDO connection sets the error mode to "display all exceptions". If set to a different value and the request failed, we would not receive any error messages.

This setting should only be used when developing a script. When activated, database and table names may be displayed that are best 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"

Conclusion

In this article, we have shared how to use PHP to insert data into a MySQL database using MySQLi and PDO. And also how to eliminate 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 the friendly project team

From the author: oh, you can’t take the words out of the song! But you can delete them, update them, or insert others. The main thing is that the words are entered into the database. Today we will tell you how data is recorded in MySQL and how to do it correctly so that the song sounds!

Adding entries using phpMyAdmin

The phpMyAdmin shell for administering the MySQL DBMS implements “lightweight” functionality for adding new records to database tables. Because of its simplicity, it is ideal for both green "dummies" and "lazy" professionals.

To enter new information into the table, you must log into the program with administrator rights. Then select the desired database and table from the lists on the left. Then in the top menu go through the “Insert” item.

After this, to make a record in the MySQL database, fill in the “Value” field for all columns in the next window and click “OK” at the bottom.

In the above screenshot you can see that the “Animals” table being modified consists of two columns (fields): id and name. The second section specifies the type of each column. We only need to enter a value for the name field, since the id column is the primary key and was set to auto-increment when the table was created. This means that the value of the id field will be generated automatically by MySQL by adding 1 to the previous integer value.

To find out which field of data records in MySQL is the primary key, in phpMyAdmin go to the menu (with the table selected on the left in the list) to the “Structure” tab of the top menu. Here is a description of all table fields, their type and additional characteristics.

Inserting data using SQL queries

But phpMyAdmin is just a shell, and real administrators “talk” to the MySQL server using Structured Query Language. That is, they “talk” to him in SQL language. Since we strive to become real professionals, we will dive a little into the study of SQL commands within the framework of the topic under consideration. Here is a query, entering which in the “SQL” field, you will create the same database:

CREATE TABLE Animal (id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id));

CREATE TABLE Animal (id MEDIUMINT NOT NULL AUTO_INCREMENT ,

name CHAR (30) NOT NULL, PRIMARY KEY (id));

After creating the table and writing the data into the MySQL database (via the “Insert” tab), the program will inform you that a row with an identifier value of 1 has been added to animals. And a little lower in the editor window the query code will be displayed, which the shell generated for us and sent to the database server .

Request code:

INSERT INTO `my_db1`.`animal` (`id`, `name`) VALUES (NULL, "Cat");

INSERT INTO ` my_db1 ` . ` animal ` ( ` id ` , ` name ` ) VALUES ( NULL , "Cat" ) ;

Let's study it in more detail. In SQL, the INSERT statement is used to insert a new row into a table. It tells the server that the specified values (VALUES (NULL, ‘Cat’)) need to be inserted into the database table (my_db1 . animal) in the id and name fields.

Please note that we do not specify a numeric value for the id column, but NULL, since we have “turned on” autofill (autoincrement) for this field.

How to insert a record using PHP

Everything we have considered is only a “prelude” to the main action, in which “His Highness” the server-side programming language PHP comes onto the stage. It was thanks to him that MySQL as a DBMS became so widespread on the Internet.
Most of the World Wide Web is built on a combination of these two Internet technologies. Wherever you look, you will find them everywhere: in modern CMS, “home-written” engines and on the server.

It's no surprise that PHP provides so many built-in functions for writing data to MySQL. But we will focus on the most important of them. Here is the code that adds a new "animal" to the animals table. If you try hard enough, you can collect a whole menagerie this way :)

$con_str=mysql_connect("localhost", "root", "", "db1"); if(mysql_connect("localhost","root"))( echo "Hello!!"; ) mysql_select_db("db1",$con_str); $query_str="INSERT INTO `db1`.`animal` (`id`, `name`) VALUES (NULL, "dog")"; mysql_query($query_str); mysql_close();

$con_str = mysql_connect ("localhost" , "root" , "" , "db1" ) ;

if (mysql_connect("localhost" , "root" ) ) (

echo "Hello!!" ;

mysql_select_db("db1", $con_str);

$query_str= "INSERT INTO `db1`.`animal` (`id`, `name`) VALUES (NULL, "dog")";

mysql_query($query_str);

mysql_close();

This code needs to be pasted into a PHP file and placed on the server side.

Using the mysql_connect() function we connect to the MySQL database server. The function takes as arguments the host, the DBMS user name, the password, and the name of the database to which you want to connect. We have a blank password because we are using a server installed on the local (client) machine.

To demonstrate all the described examples of writing to a MySQL database using PHP, we used the “gentleman’s kit” from Denver. It includes a local Apache server, a MySQL server, phpMyAdmin and several other useful tools for creating and testing program code.

Then, in the logical if block, we checked for a connection to the database server. After that, in the mysql_select_db() function we designated the database to which we will connect. Using the mysql_query() function, we launched the SQL query recorded in the $query_str variable. And at the end the established connection was closed (mysql_close() function). Now, if we look at our menagerie (the animal table), we will find a new “pet” there.

To write it into MySQL, PHP “kindly” provided the entire necessary set of functions. The main thing that beginners get burned by when using SQL in program code is incorrect writing of queries, violation of syntax and alternation of escaping characters (quotes).

To avoid the appearance of extra “gray” hair on your head, it is better to check the correct spelling of the request using phpMyAdmin. To do this, place the SQL code in the program editor and run it. If something is wrong, the application will start swearing, display a red message and indicate the location of the error.

As you can see, with the help of MySQL you can “collect” your own menagerie and correctly change the words of any “song”. And PHP is ideal for writing to the MySQL database, so we advise you to make a “close” friendship with this “great” programming language!

In this article we will look at perhaps some of the most important SQL queries. This queries to add and remove records from a database table. Because VERY often you have to add new records to the table, and do it automatically, 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 an entry, the command " INSERT INTO", then the name of the table into which we are inserting the record. Next comes in parentheses the names of the fields that we want to fill in. 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 entry 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 query is more complex, since it has the construction " WHERE", but more about it below. First comes the command " UPDATE", then the table name, and after " 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 be updated?". This is why there is " WHERE". In this case, we update 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 equality checks, 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 of which there are fewer 15 AND field " login" has the meaning " TestUser"I hope you have 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, simple 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 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.

All modules of a site or web application where it is necessary to enter and record some data (for example, name, age, address, etc.) use a simple function in the mysql language INSERT INTO `name_base` (name,value1,value2) VALUES ('Vasya ','1','2');

All variables are entered into the database according to the values 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.

Please note that you can write to the database in two ways.

First way if we do not initially specify the cell names of the database tables. Then we have to list all the variables for each cell, namely how many cells are in the database table, so many variables should be listed in parentheses after the VALUE value.

For example:
There are four cells in a database table. 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 it 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, this query will return an error. Because in this method, listing all cells in the query is mandatory.

Second way if after an INSERT INTO `name_base` (...) query, list all the cells after the database name. An example has already been discussed above. If you forgot, let's write it again:

"INSERT INTO `name_base`(`name`,`value`,`value2`) VALUES (NULL, `".$name."`,``,`2`)";

Here we have listed all the cells (name,value1,value2) . And if you add an additional two cells to the database table, then the query syntax will not have to be changed. But unless we need to immediately add in one request those very additional variables that we need for those very new created cells.

This error occurs 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 processor did not have time to change the module, or simply forgot. But some sites have a very complex structure, and finding an error can take a lot of time and effort. Therefore, it is advisable to use the second method of writing to the database. Although this kind of mistake is more often made by novice web programmers.

Php entry to mysql database. Practical examples

So, now we get to the heart of the matter when working with database queries. We will do everything using practical examples. Let's create a simple script to record 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 a cell is up to ten characters with the auto-increment parameter. This automatic setting will change every time a comment is added to +1.

The next cell is the name of the user. Number of characters - up to two hundred - three hundred characters of your choice, parameter char. Then the comment cell - in this cell we will enter the text of the comment itself. If you want to record large comment texts, then you can set the text parameter - then you can enter simply huge texts, more than five hundred thousand characters, or set the tinytext parameter, then a little less characters will fit in, 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 record two thousand characters with the varchar parameter to record string values.

In the last cell we will write the date the comment text was recorded. We will write in numerical format in seconds, using the function of the current date and time time(); For simplicity, we will set the function to the variable $time=time(); And let's create a cell in the database. Let's call it the same name as 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:

Table structure `msg` -- 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 ;

That's it, a table for comments has been created. Now we write a form to write a comment and the script handler itself. The HTML code for the comment form is as follows.

In this tutorial we'll look at how to insert data into a database directly from your PHP scripts.

Inserting data using SQL

You use SQL to insert data into a database in the same way you use SQL to create databases and tables. The SQL query syntax is:

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 use 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 it can be easy to lose track. 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 constructed by dividing the sentence into small parts and then combining them into a variable $strSQL.

In practice there is no difference in using one or the other method, but when working with large tables the ability to "keep track" becomes extremely important, so choose the most appropriate method.

Let's try the following code to insert Gus Goose into the database:

Inserting data into the database // Connection to the database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error());// Select database mysql_select_db("mydatabase") or die(mysql_error()); //Building the SQL statement $strSQL = "INSERT INTO people("; $strSQL = $strSQL . "FirstName, "; $strSQL = $strSQL . "LastName, "; $strSQL = $strSQL . "Phone," $strSQL = $strSQL . "BirthDate"; $strSQL = $strSQL . "VALUES("; $strSQL = $strSQL . ""Gus", "; $strSQL = $strSQL . ""Goose", "; $strSQL = $strSQL . ""99887766", "; $strSQL = $strSQL . ""1964-04-20""); // SQL statement is executed mysql_query($strSQL) or die (mysql_error()); //Close connection mysql_close(); ?>

DB updated!

Saving user input to the database

You've probably already figured out that you can create a form to do this, as in Lesson 11, and the values from the form can be inserted into an SQL statement. Let's say you have a simple form:

This form is submitted to the file insert.php, where you, as shown in Lesson 11, can receive user input by requesting the contents of the form. In this particular case, the SQL statement could be like this:

strSQL = "INSERT INTO people(FirstName) values("" . $_POST["FirstName"] . "")"

Similarly, you can request data from cookies, query string sessions, etc.

The most common beginner mistakes

At first, you'll probably get a bunch of error messages when you try to update the database. When working with the database, no errors are allowed at all. An incorrect comma may mean that the database is not 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 other. 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	Size
CHR	Text or a combination of text and numbers. Can also be used for numbers not used in calculations (eg telephone numbers).	Up to 255 characters - or 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 hh:mm:ss format	8 bytes

SQL statements with quotes or backslashes

If you try to insert text that contains single quote ("), double quote ("), or backslash (\) characters, the record will not be inserted into the database. The solution would be to substitute backslashes in front of characters that must be mnemonized when inserted into database queries.