We enter user data into the MySql database. SQL query to add and delete records Adding data through a form in mysql

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

Creating a field in the MySQL database to add an image

To begin with, I want to say that for storing images in a MySQL database It is necessary to define one of the table fields as a derivative of 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 an image file in a database you need to read the file into a variable and create a query to add data to the table.

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

In my case the task was add two images to the database via a 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"

Let me remind you that the attribute action specifies the file that will do the loading of image files. Attribute enctype indicates how the form content is encoded and file upload information. See how to fill the attribute correctly enctype in order to avoid .

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

Writing PHP Code to Save an Image in a MySQL Database

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

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

The original file name on the client machine.

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

The 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 downloaded file was saved on the server.

How to get the values of each file?

For example, suppose 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

Likewise, $_FILES[‘userfile’][‘size’] will contain the file size value 1.jpg, and so on. Now let's look at the code of the add_image.php file, which was specified in the form attribute action.

1024*1024||$image_size==0) ( $ErrorDescription="Each image should not exceed 1MB! The image cannot be added to the database."; return ""; ) // If the file has arrived, then check whether 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 the file content $image=mysql_escape_string($image); return $image; )else( ErrorDescription="You did not load an image, so it is 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.

This guide will show you how to start managing a database from your PHP script. You will study adding a record to a MySQL table, using PHP code. Before you start, check out our other tutorials that cover the basic steps of working with PHP and databases - connecting from PHP to a MySQL database.

Before you begin, check for the following:

Access to your hosting control panel

Step 1 - Creating a Table

First of all, we need to create a table for your data. This is a very simple procedure that you can do in phpMyAdmin from your hosting control panel. We've already covered the process of creating a MySQL database in a previous tutorial, so we'll skip that part here.

After logging into the phpMyAdmin page, you will see a picture like this:

Let's create a table with the name Students for our database u266072517_name. You can create a new table using the button Create Table. After this you will see new page where you can enter all the necessary data for your table:

This is the simplest way to create a table, for more information on the table/database structure and what settings can be used for each field, please refer to the official phpMyAdmin documentation.

Here are some simple explanations of the fields we will use:

Name is the name of your field. Will appear at the very top of your table.
Type– here you can set the field type. For example, we choose varchar because here we need to enter a string with a name (which has letters, not numbers).
Length/Values– used to set the maximum length of your entry in this field.
Index– we use the “Primary” index for our “ID” field. When creating a table, it is recommended to have one ID field. It is used to index records in a table when relationships between tables are configured. It can also be noted here "A_I", which means Auto Increment. This setting will automatically increase the index (1,2,3,4...).

Click Save and your table will be created.

Step 2 - Create PHP code and add an entry to the MySQL table

Option 1 – MySQLi Method

First of all, you need to establish a connection to the database, according to our previous tutorial. After this we can continue with the SQL query to add a record to the MySQL table − INSERT. Here's a complete code example with connection and insertion method:

" . mysqli_error($conn); ) mysqli_close($conn); ?>

Thus, the first part of the code (lines 3 – 18 ) refer to the database connection establishment part. We won't go through this part again, if you want to know what each line means, refer to our previous guide on how to connect to a database.

Let's start with the line 19 :

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

This is the most important line of code, it does everything we cover in this tutorial - adding a record to a MySQL table in the database. INSERT INTO is an expression that adds a record to the specified MySQL database table. In our example we are adding data to a table Students.

Moving further, in parentheses, we define the table fields to which we will add values: (name, lastname, email). The data will be added in a specific order. If we write (email, lastname, name), the values will be added in a different order.

Next part of the meaning VALUES. Here we set our values in the previously specified fields. Thus, each field will receive its own value. For example, in our case it would be something like: name = Thom, lastname = Vial, email = [email protected] .

What is important to note is that here we are forming SQL query using PHP code. SQL queries must be enclosed in quotes. In our example, everything between the quotes and coming after $sql = is an SQL query.

The next part of the code ( 20 – 22 lines) runs our request and checks the success of the request:

If (mysqli_query($conn, $sql)) ( echo "New record created successfully"; )

A success message is displayed if the query was run correctly.

And the final part ( 22 – 24 lines) show another message in case our request fails:

Else ( echo "Error: " . $sql . "
" . mysqli_error($conn); )

This code shows us an error message in case something went wrong.

Option 2 – PHP Data Object Method (P HP D ata O bject)

As in the previous example, we first need to make a connection to the database, which is done when creating a new PDO object - the previous tutorial talks about how this happens. Since a MySQL database connection is a PDO object, we must use various PDO 'methods' (a kind of functions that are part of a specific object) to prepare and run the query. Object methods are called like this:

$the_Object->the_Method();

PDO allows you to 'prepare' SQL code before executing it. SQL Query calculated and adjusted before launch. So, simple attack by SQL injection can be done by filling out SQL code in a form field. For example:

// User writes this in the username field of a login form thom"; DROP DATABASE user_table; // The final query becomes this "SELECT * FROM user_table WHERE username = thom"; DROP DATABASE user_table;

Since the SQL code is syntactically correct, the semicolon makes DROP DATABASE user_table new SQL query and your users table is deleted. Prepared expressions do not allow characters “ And ; to complete the original request, and the instruction DROP DATABASE will never be executed.

Always Use prepared queries when sending or receiving data from a database with PDO.

To use prepared expressions, you need to create a new variable that will call the method prepare() on the database object.

In correct form the code looks like:

$servername = "mysql.hostinger.com"; $database = "u266072517_name"; $username = "u266072517_user"; $password = "buystuffpwd"; $sql = "mysql:host=$servername;dbname=$database;"; $dsn_Options = ; // Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object try ( $my_Db_Connection = new PDO($sql, $username, $password, $dsn_Options); echo "Connected successfully"; ) catch (PDOException $ error) ( echo "Connection error: " . $error->getMessage(); ) // Set the variables for the person we want to add to the database $first_Name = "Thom"; $last_Name = "Vial"; $email = " [email protected]"; // Here we create a variable that calls the prepare() method of the database object // The SQL query you want to run is entered as the 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 actually refers to using the bindParam() method // First parameter is the placeholder in the statement above - the second parameter is a variable that it should refer to $my_Insert_Statement->bindParam(:first_name, $first_Name); $my_Insert_Statement->bindParam(:last_name, $last_Name); bindParam(:email, $email); // Execute the query using the data we just defined // The execute() method returns TRUE if it is successful and FALSE if it is not, allowing you to write your own messages here if ( $my_Insert_Statement->execute()) ( echo "New record created successfully"; ) else ( echo "Unable to create record"; ) // At this point you can change the data of the variables and execute again to add more data to the database $first_Name = "John"; $last_Name = "Smith"; $email = " [email protected]"; $my_Insert_Statement->execute(); // Execute again now that the variables have changed if ($my_Insert_Statement->execute()) ( echo "New record created successfully"; ) else ( echo "Unable to create record"; )

On lines 28, 29 and 30 we use the method bindParam() database object. There is also a method bindValue(), different from the previous one.

bindParam() – this method counts the data when the method execute() achieved. The first time the script reaches the method execute() he sees that $first_Name references “Thom”, binds that value and executes the query. When the script reaches the method a second time execute(), he looks that $first_Name now references “John”, binds that value and runs the query again with the new value. It is important to understand that we create the request once and then substitute different data in different places in the script.
bindValue() – this method calculates the data as soon as it gets its turn. Since the value $first_Name was set to “Thom” at the time we reached the method bindValue(), it will be used when calling the method execute() For $my_Insert_Statement.

Please note that we are reusing the variable $first_Name and give it a new value a second time. If you check your database after running this script, both of the given names will be there, contrary to this variable value $first_Name will be equal to “John” at the end of this script. Remember that PHP evaluates the contents of a script before it runs.

If you change your script by replacing bindParam on bindValue, you will add “Thom Vial” to the MySQL database twice and John Smith will be ignored.

Step 3 - Verify success and resolve general issues

If the query we ran in the MySQL database was successful, we will see the following message:

Solving common errors

MySQLi

In any other case, an error message will be shown instead of the above message. For example, let's make one syntax error in our code and we'll get this:

As we can see, the first part of the code is fine, the connection was successfully established, but our SQL query encountered failure when executed.

"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 right syntax to use near "(name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")" at line 1"

There was a syntax error that causes our script to fail. The error was here:

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

As you can see, we are using curly braces instead of parentheses. This is incorrect and results in a syntax error in our script.

PDO

On line 7 of the PDO connection, the error handling mode is set to ‘display all exceptions’. If you remove this from the script and the request fails, you will not receive any error message. With exceptions enabled, the specific problems encountered will be displayed. This is generally best used when developing a script, as it may reveal database and table names that you would like to hide from anyone who might gain unauthorized access to your data. In the case above, when curly braces were used instead of parentheses, the error looks like below:

Fatal error: Uncaught exception "PDOException" with message "SQLSTATE: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "(name, lastname, email) VALUES ("Thom", "Vial", " [email protected]")" at line 1"

Other problems you may encounter:

The fields are specified incorrectly (non-existent fields or misspelled names).
The value type does not match the field type. For example, when we want to assign the value of a number 47 field Name, we'll get an error because the value is expected to be a string. But, if you specify a number in quotes, for example, “47” , there will be no error because our number will be written as a string in this field.
An attempt to enter data into a table that does not exist or an error in spelling the table name.

All these errors can be fixed by following the error correction guides or by checking the error log.

After successfully adding the data, we should see it in our database. Here's an example of the table we added our data to, looking at phpMyAdmin.

Conclusion

In this tutorial you learned how to use PHP code to add a record to a MySQL table using MySQLi And PDO. We also looked at common errors and their solutions. Knowing how to use PHP code to add to a MySQL database will come in handy whether you're learning to program or already creating your own website.

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, then this request 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 parameter int (for numerical 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.