> >

Php adding an entry to the database. The lesson will be especially useful for beginners

Comments moved from the blog

SERGEY
09/14/2016 at 01:25
Good afternoon!
I am interested in the following question: what is the easiest way to organize the storage of data and program settings without using a database? I 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 to choose one of these from the list.

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

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

NICHOLAY
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 the database Mysql data- added an article.

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

DIMA
09.20.2016 at 10:24
Hello, is it possible this method Implemented 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 enter such text into the database System.Windows.Forms.TextBox, Text: ge

By the way, this (ge) at the end it is written by gene, even though the text is specified in the table settings. The word of 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 enter the name: Gena.

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

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

GENNADY
09.24.2016 at 18:41
That is how it is. Thank you

SERGEY
09.25.2016 at 11:51
Hello. How to implement adding to the database via textBox?

ADMIN
09.26.2016 at 20:53
Everything is the same in principle. For example, let's take the very last 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 transfer them to the database

LINARA
09.27.2016 at 17:45
Hello, how can I select a row in dataGridVIew and phpMyAdmin?

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

P.S.H.
09/30/2016 at 03:48
2Linara:
If you want to edit rows that way, take a tool a la HediSQL, configure and change the rows.

2admin
Good day! Thanks for the materials - everything is very cool)
Question: I add data with the following request (it’s 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", "9.11.1900");";

The data is entered ok, but in the database (mysql) instead of the Cyrillic alphabet it appears “????”.

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 you direct me what to read/change.
Thank you

ADMIN
10/01/2016 at 09:49
Hello.

In the database (and in the table) the mapping is utf_general_ci

Is there such a comparison? Perhaps utf8_general_ci?

Usually they create a Mysql database by choosing the utf8_general_ci comparison, so there are no problems with the Cyrillic alphabet, unless, of course, crooks come from the client to the server.

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";

P.S.H.
10/01/2016 at 11:34
You're right, I peed myself, utf8_general_ci!
Yes it helped, ;Charset=utf8;
Thank you so much!

SERGY
10/02/2016 at 11:02
Thanks for the working example. Question
I created a text field in which I would like to enter the IP address of the database, but I don’t know how to insert this data here

String conStr = "server=@textBox2;user=test;" +
"database=test;password=test;";
Please tell me how to insert data from text fields in a windows form into this design...

ADMIN
10/03/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(“The addition was successful”, “The addition was successful”, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

The error “Column ‘ID’ cannot be null” is displayed, I remove the addition to the ID column - the same thing is written about the next column, etc.
If I enter any constant values ​​in parentheses into VALUES, the row is added to the database.
Tell me, please, what is the problem? I need to record exactly the data and values ​​entered through the form into the database

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

Your name:
Your email:


This form can be used to register a new user, to send out news, to collect statistics, or for anything... In general, the user enters his data into this form: name and email, clicks on the button and then the data goes into the PHP script:

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


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

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


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

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


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

Data saved successfully!


If any problems arise and the data has not been entered, we will receive an error message:

An error occurred, please try again.


That's all!

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

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

Your email:


add:

Your city:


IN php script, after:

$email = $_POST["email"];


add:

$city = $_POST["city"];


And of course we add it in the request too, like this:

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


This is what you should end up with:
Input form:

Your name:
Your email:
Your city:


Script:

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


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

The lesson will be based on form feedback , necessary on almost any website.

Step one: Creating a database in MySQL

Opening phpMyAdmin(included in the basic package Denwer`a), and create a database called " test_base", select the encoding " cp1251_general_ci".

Step two: Creating a table in MySQL by using SQL query

You can, of course, create a table using standard means phpMyAdmin, but this way the structure of the table being created will be clearly visible.

Create a table called " test_table" and with six fields named:
"name" - user names will be stored here;
"email" - addresses will be stored here electronic mailboxes 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: Creating a Form

index.html:

Form saved in MySQL

Example of a form with data saving in MySQL

























Sending a request
Name:
Email:
Message subject:
Message:
























Step four: Creating a form handler " save_form.php" with saving the received data in MySQL

save_form.php:





/* MySQL table in which the data will be stored */
$table = "test_table";

/* Create a connection */

/* Determine the current date */
$cdate = date("Y-m-d");

/* We create a query to insert information into the table
name...date - the name of specific fields in the database;
in $_POST["test_name"]... $_POST["test_mess"] - these variables contain data received from the form */
$query = "INSERT INTO $table SET name="".$_POST["test_name"].", email="".$_POST["test_mail"].",
theme="".$_POST["test_theme"].", message="".$_POST["test_mess"].", data="$cdate"";

/* Close the connection */
mysql_close();

/* If saving is successful, display a message and a return link */
echo("


Data saved successfully!



come back
");

Step five: Displaying saved data" view_data.php"

view_data.php:

/* Connect to the database */
$hostname = "localhost"; // server name/path, with MySQL
$username = "root"; // username (in Denwer the default is "root")
$password = ""; // user password (in Denwer there is no password by default, this parameter can be left empty)
$dbName = "test_base"; // database name


$table = "test_table";

/* Create a connection */
mysql_connect($hostname, $username, $password) or die("Can't create connection");

/* Select a database. If an error occurs, display it */
mysql_select_db($dbName) or die (mysql_error());

/* We create a query to extract data from the fields "name", "email", "theme",
"message", "data" tables "test_table" */
$query = "SELECT id, name, email, theme, message, data FROM $table";

/* Execute the request. If an error occurs, display it. */


echo("

Outputting data from MySQL

Outputting previously saved data from a MySQL table










");



echo "

\n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
echo " \n\n";
}

Echo("

# date of the application Usernames Users' email Message subject User messages
".$row["id"]."".$row["data"]."".$row["name"]."".$row["email"]."".$row["theme"]."".$row["message"]."
\n");

/* Close the connection */
mysql_close();

Step six: Removing records from the database" del_data.php"

del_data.php:

/* Connect to the database */
$hostname = "localhost"; // server name/path, with MySQL
$username = "root"; // username (in Denwer the default is "root")
$password = ""; // user password (in Denwer there is no password by default, this parameter can be left empty)
$dbName = "test_base"; // database name

/* MySQL table where data is stored */
$table = "test_table";

/* Create a connection */
mysql_connect($hostname, $username, $password) or die("Can't create connection");

/* Select a database. If an error occurs, display it */
mysql_select_db($dbName) or die (mysql_error());

/* If the delete link was clicked, delete the entry */
$del = $query = "delete from $table where (id="$del"");
/* Execute the request. If an error occurs, display it. */
mysql_query($query) or die(mysql_error());



/* Execute the request. If an error occurs, display it. */
$res = mysql_query($query) or die(mysql_error());

$row = mysql_num_rows($res);

/* Output data from the table */
echo("

Outputting and deleting data from MySQL

Outputting and deleting previously saved data from a MySQL table











");

/* Cycle of outputting data from the database of specific fields */
while ($row = mysql_fetch_array($res)) (
echo "

\n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
/* Generate a link to delete a field */
echo " \n";
echo "\n";
}

Echo("

# date of the application Usernames Users' email Message subject User messages Removal
".$row["id"]."".$row["data"]."".$row["name"]."".$row["email"]."".$row["theme"]."".$row["message"]."Delete
\n");

/* Close the connection */
mysql_close();

Step seven: Editing and updating records in the database " update_data.php"

update_data.php:

/* Connect to the database */
$hostname = "localhost"; // server name/path, with MySQL
$username = "root"; // username (in Denwer the default is "root")
$password = ""; // user password (in Denwer there is no password by default, this parameter can be left empty)
$dbName = "test_base"; // database name

/* MySQL table where data is stored */
$table = "test_table";

/* Create a connection */
mysql_connect($hostname, $username, $password) or die("Can't create connection");

/* Select a database. If an error occurs, display it */
mysql_select_db($dbName) or die (mysql_error());

/* If the edit button was pressed, make changes */
if(@$submit_edit) (
$query = "UPDATE $table SET name="$test_name", email="$test_mail", theme="$test_theme", message="$test_mess" WHERE id="$update"";
/* Execute the request. If an error occurs, display it. */
mysql_query($query) or die (mysql_error());
}

/* We put the entire database into the $res variable */
$query = "SELECT * FROM $table";
/* Execute the request. If an error occurs, display it. */
$res = mysql_query($query) or die(mysql_error());
/* Find out the number of records in the database */
$row = mysql_num_rows($res);

/* Output data from the table */
echo("

Editing and updating data

Editing and updating data in a MySQL table


");

/* Cycle of outputting data from the database of specific fields */
while ($row = mysql_fetch_array($res)) (
echo "

\n";
echo " \n";
echo " \n";
echo " \n";
echo " \n";
echo "\n";
echo " \n";
echo "\n";
echo " \n";
echo "\n";
echo " \n";
echo "\n";
echo " \n";
echo "\n";
echo " \n";
echo "
#".$row["id"]."
".$row["data"]."
Username:
User email:
Message subject:
Message:
\n\n";
}

/* Close the connection */
mysql_close();

Well that's all, happy coding:1133:

_________________________________

Last update: 11/1/2015

To add data, use the "INSERT" expression:

$query ="INSERT INTO goods VALUES(NULL, " Samsung Galaxy III","Samsumg"");

The "INSERT" statement inserts a single row into a table. After keyword INTO indicates the name of the table, and after VALUES in parentheses the set of values ​​for all columns is indicated. Since we have three columns in the table, we indicate three values.

Since in the previous topic, when creating a table, we specified the following order of columns: id, name, company, in this case the value 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 don't have to give it a specific numeric value and 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 the connection mysqli_close($link); ) ?>

Add new model

Enter model:

Manufacturer:

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 the 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 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 with 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 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 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 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 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"

Other possible problems:

  • Columns are incorrectly specified (non-existent columns or misspelled column names).
  • One value type 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. There was also a spelling mistake 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 with data added.

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

Computer
The most interesting:
Label each polygon and measure the lengths of the sides of each
Connecting YouTube to TV
Names of large numbers Solving a compound problem