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.

Because the id column is defined as AUTO_INCREMENT, we don't have to specify a specific numeric value for it, and we can pass a 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 an SQL query. It takes as parameters a connection object and a string to be escaped.

Thus, we actually apply 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.

Comments moved from the blog

SERGEI
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, can you this way to implement not through DataGridView, but through 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 is (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. Thanks

SERGEI
09/25/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 that 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.
Thanks

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 Cyrillic, unless, of course, the server does not receive gibberish 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 tutorial, we'll 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 a database in the same way that you use SQL to create a database 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 given 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 .

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's easy to lose track. Therefore, it is better to write the SQL statement slightly differently:

strSQL = "INSERT INTO people("; strSQL = strSQL . "FirstName"; strSQL = strSQL . "Last name" 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 by dividing the sentence into small parts and then combining them into a variable $strSQL.

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

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

// Connect 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 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()); // Closing the connection mysql_close(); ?>

DB updated!

Saving user input to the DB

You probably already figured out that you can create a form for this, as in Lesson 11, and the values ​​​​from the form can be inserted into the SQL statement. Let's say you have a 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 form content. In this particular case, the SQL statement might be:

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 you try to update the database. When working with a database, no errors are absolutely unacceptable. 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 errors.

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 commonly used data types are listed below:

SQL statements with quotes or backslashes

If you try to paste text containing single quote characters ("), double quotes(") or a backslash (\), the record will not be inserted into the database. The solution is to substitute backslashes before the characters that should be mnemonized when inserted into database queries.

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 function in my language sql 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 texts of comments, 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 function current date and 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.

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 record 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 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 query is more complex, since it has the " 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 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 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 removed.