Home >Backend Development >PHP Tutorial >How to send data to MySQL with PHP_PHP Tutorial
How to send data to MySQL with PHP_PHP Tutorial
- WBOYOriginal
- 2016-07-21 16:02:38862browse
You should be familiar with HTML forms. The following piece of code is a very simple HTML form:
< html>
< body>
< form action=submitform.php3 method=GET>
Last name: < input type=text name=first_name size=25 maxlength=25>
First name: < input type=text name=last_name size=25 maxlength=25>
< p>
< input type=submit>
< /form> body>
< /html>
When you enter data and press the submit button, this form will send the data to submitform.php3. This PHP script will then process the received data. Here is the code of submitform.php3:
< html>
< body>
< ?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname); 🎜>
VALUES ('$first_name', '$last_name')
");
print ($first_name);
print (" ");
print ($last_name);
print ("< p>");
print ("Thank you for filling out the registration form"); ?>
< /body>
< /html>
The "username" and "password" in the third line of the code represent your login respectively. MySQL database account and password. "dbname" in the fifth line represents the name of the MySQL database. "tablename" in line 13 is the name of a table in the database.
When you press submit, you can see the name you entered displayed on a new page. Take another look at the URL bar of the browser. Its content should be like this:
… /submitform.php3?first_name=Fred&last_name=Flintstone
Because we are using the form GET method, so the data is passed to submitform.php3 via the URL. Obviously, the GET method has limitations. When there is a lot of content to be transferred, GET cannot be used and only the POST method can be used. But no matter what method is used, when the data transfer is completed, PHP automatically creates a variable for each field in the form that is the same as their name (name attribute of the form).
PHP variables all start with a dollar sign. In this way, during the processing of the submitform.php3 script, there will be two variables $first_name and $last_name. The content of the variables is what you input. content.
Let’s check whether the name you entered has actually been entered into the database. Start MySQL and enter at the mysql> prompt:
mysql> select * from tablename;
You should be able to get a table with the content you just entered:
+----------------+------------+
| first_name | last_name |
+---- --------+----------------+
| Liu| Rufeng
+---------- --+----------------+
1 rows in set (0.00 sec)
Let’s analyze how submitform.php3 works:
The first two lines of the script are:
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
This Two function calls are used to open the MySQL database. The meaning of the specific parameters has just been mentioned.
The following line executes a SQL statement:
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
The mysql_query function is used to execute a SQL query on the selected database. You can execute any SQL statement in the mysql_query function. The SQL statement to be executed must be enclosed in double quotes as a string, and variables within it must be enclosed in single quotes.
There is one thing to note: MySQL statements must end with a semicolon (;). The same is true for a line of PHP code, but MySQL statements in PHP scripts cannot have semicolons. That is, when you enter a MySQL command at the mysql> prompt, you should add a semicolon:
INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name' , '$last_name');
But if this command appears in a PHP script, the semicolon must be removed.The reason for this is that some statements, such as SELECT and INSERT, work with or without semicolons. But there are some statements, such as UPDATE, which won't work if you add a semicolon. To avoid trouble, just remember this rule.
How to extract data from MySQL in PHP
Now we create another HTML form to perform this task:
< html>
< body> ;
< form action=searchform.php3 method=GET>
Please enter your query content:
< p>
Surname: < input type=text name=first_name size=25 maxlength=25>
< p>
< p>
< input type=submit>
< /form> >< /html>
Similarly, there is also a php script to process this form. Let’s create a searchform.php3 file:
< html>
< body>
< ?php
mysql_connect (localhost, username, password); 🎜>if ($first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%';}
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'
");
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no matching record was found in our database. ";}
?>
< /body>
< /html>
When you enter the data you want to retrieve in the form content, and then press the SUBMIT button, you will enter a new page, which lists all matching search results. Let’s take a look at how this script completes the search task. Several statements are the same as mentioned above. First, establish a database connection, and then select the database and data table. These are necessary for every database application. Then there are several statements like this:
if ($ first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%'; }
These lines are used to check whether each field of the form is empty. Pay attention to the two equal signs, because most of PHP's syntax is derived from the C language, and the usage of the equal sign here is also the same as that of C. : One equal sign is an assignment sign, and two equal signs represent logical equality. It should also be noted that when the condition after IF is true, the statements to be executed later are placed in "{" and "}", and Each statement must be followed by a semicolon to indicate the end of the statement. The percent sign % is a wildcard character in SQL language. After understanding one point, you should know the meaning of these two lines: If " "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The following two sentences have the same meaning.
$result = mysql_query ("SELECT * FROM tablename "
WHERE first_name LIKE '$ first_name%'
AND last_name LIKE '$last_name%'"
");
This line completes most of the search work. When the mysql_query function completes a query, it returns an integer flag.
The query selects from all records those records whose first_name column is the same as the $first_name variable, and the last_name column and the $last_name variable value are also the same, put them into the temporary record set, and use the returned integer as this Recordset logo.
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result ));
} else {print "Sorry, no matching records were found in our database. ";}
This is the last step, which is the display part.The mysql_fetch_array function first extracts the content of the first row of the query result, and then displays it using the PRINT statement. The parameter of this function is the integer flag returned by the mysql_query function. After mysql_fetch_array is successfully executed, the record set pointer will automatically move down, so that when mysql_fetch_array is executed again, the content of the next row of records will be obtained.
The array variable $row is created by the mysql_fetch_array function and filled with the query result fields. Each component of the array corresponds to each field of the query result.
If a matching record is found, the variable $row will not be empty, and the statement in the curly brackets will be executed:
do {
print $row[ "first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
This is a do ... while loop. The difference from the while loop is that it first executes the loop body and then checks whether the loop condition is met. Since we already know that when the record set is not empty, the loop body must be executed at least once, so we should use do...while instead of while loop. What is in the curly braces is the loop body to be executed:
print $row["first_name"];
print (" ");
print $row[" last_name"];
print ("< p>");
The next step is to check whether the while condition is met. The Mysql_fetch_array function is called again to get the contents of the current record. This process keeps looping. When no next record exists, mysql_fetch_array returns false, the loop ends, and the record set is completely traversed.
The array returned by mysql_fetch_array($result) can not only be called by field name, but also can be referenced by subscripts like a normal array. In this way, the above code can also be written like this:
print $row[0];
print (" ");
print $row[1];
print ("< p>");
We can also use the echo function to write these four statements more compactly:
echo $row[0], " ", $row[1], "< p>";
When no matching record is found, there will be no content in $row, and the if statement will be called. Else clause:
else {print "Sorry, no matching record was found in our database. ";}