How to send data to MySQL in PHP

How to send data to MySQL in PHP

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>

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

mysql_query ("INSERT INTO tablename (first_name, last_name)

VALUES ('$first_name', '$last_name')

");

PRint ($first_name);

print (" ");

print ($last_name);

print ("< p>");

print ("Thank you for filling in the registration form");

?>

< /body>

< /html>

The "username" and "password" in the third line of the code represent your account number and password respectively to log in to the MySQL database. "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 your browser. Its content should look like this:

… /submitform.php3?first_name=Fred&last_name=Flintstone

Because we are using the form GET method, the data is transmitted to submitform.php3 through 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 (the 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.

Let’s check if the name you entered is actually entered into the database. Start MySQL, 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);

These 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 (;), and 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 with PHP

Now we create another HTML form to perform this task:

< html>

< body>

< form action=searchform.php3 method=GET>

Please enter your query:

< p>

Last name: < input type=text name=first_name size=25 maxlength=25>

< p>

Name: < input type=text name=last_name size=25 maxlength=25>

< p>

< input type=submit>

< /form>

< /body>

< /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);



mysql_select_db (dbname);

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 records were found in our database. ";}

?>

< /body>

< /html>

When you enter the content you want to retrieve in the form and press the SUBMIT button, you will enter a new page that lists all matching search results. Let's take a look at how this script completes the search task.

The previous 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 sentences 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 in 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 subsequent statements to be executed are placed in "{" and "}", and a semicolon must be added after each statement 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 the "FIRST_NAME" field is empty, then all FIRST_NAMEs will be listed. The next 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 does most of the work of searching. 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 whose 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 the mark of this record set.

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 executed successfully, 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 curly brackets 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 general 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 else clause of the if statement will be called:

else {print "Sorry, no matching records were found in our database. ";}

The above introduces how to send data to MySQL in PHP, including the relevant aspects. I hope it will be helpful to friends who are interested in PHP tutorials.