home Questions Adding data to mysql table via php. Writing data to MySQL - so that the song of the code sounds. Add new model

Adding data to mysql table via php. Writing data to MySQL - so that the song of the code sounds. Add new model

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 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 simplest setup that 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 marked "A_I", which stands for "Auto Increment" - the option to automatically assign the number of records (1,2,3,4...).
Click the Save button and the table will be created.

Step 2. Writing PHP code to insert data into MySQL.

Option 1 - MySQLi method

First you need to establish a connection to the database. After that we use 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]")";

She inserts data into 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. AT program code SQL queries must be escaped with quotes. 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 methods PDO. Object methods are called like this:

$the_Object->the_Method();

PDO allows you to "prepare" SQL code before it is executed. The SQL query is evaluated and "corrected" before being run. For example, the simplest SQL injection attack can be 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 statements, you need to write a new variable that calls the prepare() method of the database object.

Correct code:

getMessage(); ) // Set variables for the person we want to add to the database $first_Name = "Thom"; $last_Name = "Vial"; $email = " [email protected]"; // Create a variable that calls the prepare() method of the database object // The SQL query you want to execute is entered as a parameter, and placeholders are written like this: placeholder_name $my_Insert_Statement = $my_Db_Connection->prepare("INSERT INTO Students ( name, lastname, email) VALUES (:first_name, :last_name, :email)"); // Now we tell the script which variable 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"

Adding entries with phpMyAdmin

The phpMyAdmin shell for administering the MySQL DBMS implements a “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 should enter the program with administrator rights. Then, in the lists on the left, select the desired database and table. Then in the top menu go through the item "Insert".

After that, in order to make a record in the MySQL database, fill in the next window for all columns of the "Value" field and click "OK" at the bottom.

The screenshot above shows that the modified table "Animals" consists of two columns (fields): id and name. The second section specifies the type of each of the columns. We only need to enter a value for the name field, because 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, adding 1 to the previous integer value.

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

Inserting data using SQL queries

But phpMyAdmin is just a wrapper, and the real administrators "talk" to the MySQL server using Structured Query Language. That is, they “talk” with him in the SQL language. Since we are striving 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 a table and writing data to the MySQL database (via the "Insert" tab), the program will inform you that a line 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. SQL uses the INSERT statement to insert a new row into a table. It tells the server that in the database table (my_db1 . animal) you need to insert the specified values \u200b\u200bin the id and name fields (VALUES (NULL, 'Cat').

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

How to insert a post using PHP

Everything we have considered is only a “prelude” to the main action, in which “His Highness” the server-side programming language PHP enters the scene. It is thanks to him that MySQL as a DBMS has become so widespread on the Web.
A large part of the World Wide Web is built on a bunch of these two Internet technologies. Wherever you look, you will find them everywhere: in modern CMS, "self-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, then in this way you can collect a whole menagerie :)

$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 hosted on the server side.

Using the mysql_connect() function, we connect to the MySQL database server. As arguments, the function takes the host, DBMS username, password, and the name of the database to connect to. We have an empty password because we are using a server installed on the local (client) machine.

To demonstrate all the described examples of writing to the MySQL database using PHP, we used the "gentleman's kit" from Denver. It includes a local Apache server, a MySQL server, phpMyAdmin, and a few other useful coding and testing tools.

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 base to which we will connect. Using the mysql_query() function, we launched the SQL query written to the $query_str variable. And at the end, the established connection was closed (mysql_close() function). Now, if we look into our menagerie (table animal), we will find a new “pet” there.

To write it in MySQL, PHP "kindly" provided all the necessary set of functions. The main thing that beginners “burn out” on when using SQL in program code is incorrect spelling of queries, violation of syntax and alternation of characters for escaping (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 for execution. If something is wrong, the application will start to “swear”, display a red message and indicate the location of the error.

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

And are given
. And now we'll talk about how add images to MySQL database via form using PHP.

Creating a field in MySQL database to add an image

To begin with, I would like to say that storing images in a MySQL database it is necessary to define one of the fields of the table as derived from the BLOB type.

The abbreviation BLOB stands for Binary Large Object. The BLOB data storage type has several options:

TINYBLOB - Can store up to 255 bytes
BLOB can store up to 64 kilobytes of information
MEDIUMBLOB - up to 16 megabytes
LONGBLOB up to 4 gigabytes

For storing the image file in the database you need to read the file into a variable and create a query to add data to the table.

Preparing a form on a page to add an image to a MySQL database

In my case the task was add two images to database via form using PHP. We have a form with two fields and a submit button:

form name="form1" method="post" action="add_image.php"
enctype="multipart/form-data"

Recall that the attribute action specifies the file that will load the image files. Attribute enctype specifies how the form content is to be encoded and information about file uploads. See how to correctly fill in the attribute enctype to avoid .

Note: support for uploading multiple files was introduced in version 3.0.10.

Writing PHP Code to Save Image to MySQL Database

Since we are sending two files in the attribute name after the word, we indicate “userfile” with square brackets, by this we make it clear that we are sending several files using an array that contains file attributes:

$_FILES['userfile']['name']

The original filename on the client machine.

$_FILES['userfile']['type']

mime type of the file, if the browser provided this information.
Example: "image/gif" .

$_FILES['userfile']['size']

$_FILES['userfile']['tmp_name']

Temporary file name under which the uploaded file was saved on the server.

How to get the values of each file?

For example, suppose that files named /home/test/1.jpg and /home/test/2.jpg are sent.

In this case $_FILES['userfile']['name']
will contain the value 1.jpg,
and $_FILES['userfile']['name']
- value 2.jpg

Similarly, $_FILES['userfile']['size'] will contain the file size value 1.jpg, and so on. Now consider the code of the add_image.php file, which was specified in the form attribute action.

1024*1024||$image_size==0) ( $ErrorDescription="Each image must not exceed 1Mb! The image cannot be added to the database."; return ""; ) // If the file has arrived, then check if the graphic // it (for security reasons) if(substr($_FILES["userfile"]["type"][$num], 0, 5)=="image") ( //Read the contents of the file $image=file_get_contents($_FILES ["userfile"]["tmp_name"][$num]); //Escape special characters in file content $image=mysql_escape_string($image); return $image; )else( ErrorDescription="You didn't upload an image, so it's cannot be added."; return ""; ) )else( $ErrorDescription="You did not upload an image, the field is empty, so the file cannot be added to the database."; return ; ) return $image; ) ?>

So, in this article, we talked about how to save an image in a MySQL database. , using PHP.

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 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.

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.

NIKOLAY
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?

NIKOLAY
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 database mysql data added an article.

NIKOLAY
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 the Cyrillic alphabet, unless, of course, the server does not receive bugs 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 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

Just about the complex. Programs. Iron. Internet. Windows