Interaction between HTML and PHP and MySQL
Why use the database?
World Wide Web (WWW) is more than just a place to provide information. If you have something, you can also share it with people all over the world. However, this is not an easy task. As the website gets bigger and bigger, you may encounter problems like this:
The website contains too many things that make visitors unable to get what they want very quickly. This problem is to some extent fatal to a website.
Visitors want to provide you with information, and this information must be saved for later use.
The above two problems can be solved through the database!
In the WWW world, databases are everywhere. As big as Yahoo!, Amazon, eBay, as small as a simple message board, you can see where the database comes in. It can even be said that databases are the basis of all advanced applications.
Why use PHP and MYSQL
As far as I know, almost all the major commercial website databases are SQL-based. The most popular one is probably Oracle. It is very powerful, and of course, it is also expensive. SQL is not an application, but a language. It is the abbreviation of Structured Query Language, used to operate and query databases.
In recent years, some companies have developed "open code" SQL applications, the most famous of which may be considered MySQL. It is not just free, but it does not perform worse than Oracle for general small and medium-sized database applications.
To run MySQL on a website, you need a scripting language to interact with the database. In the past, Perl was the most popular. But now it seems PHP seems to be a little better. Don't ask me what the difference is between them?? I used to use Perl and it also worked very well, but now it seems that everyone likes to use PHP. Its popularity certainly has its reasons.
Required software
The content of this part has been introduced in ChinaByte Network Academy's articles a few times ago. Readers can refer to the article "Setting Up Local PHP Development for Win98". I won't introduce it in detail here.
HTML and PHP
Author: Yangmei Compiled Number of clicks on this article: 398
Let's take a look at how PHP works. Take a look at the following code:
< html>
< body>
< ?php
print "Hello, world.";
?>
< /body>
< /html>
When requesting this page, it will display "Hello, world" in the browser.
As you can see, the PHP script is embedded in the HTML file. It starts with " < ? " and ends with " ?> ". Not only that, we can even embed HTML tags in PHP scripts:
< ?php
print "< html>";
print "< body>";
print "Hello, world.";
print "< /body>";
print "< /html>";
?>
The two methods have the same outcome, and the effects are the same. But in some special cases, it is more convenient to choose one of them.
PHP prints statement
Author: Yangmei Compiled Number of clicks on this article: 398
The simplest interaction between PHP and HTML is achieved through print statements:
< ?php
print "Hello, world.";
?>
print is the simplest and most commonly used function, used to display some text in a browser window. The echo function is similar to print, but you can use the "," to separate multiple contents to be displayed, which is more convenient when mixing the display of string constants and variables.
There is also a printf function that formats the output of numbers. A number can be displayed as an integer or using scientific notation.
Among these functions, the use of parentheses is different:
echo must not be bracketed
printf must have
print optional
It is simple to display a string or a number, just follow the variable name or constant after the print statement. However, if you want to display an array, is it written like this:
print $myarray;
The result of its output will be "Array", and PHP tells you that $myarray is an array. This will be useful if you are not sure whether a variable is an array, but now what we want to see is the content of the array.
You can use the implode function to convert an array into a string. It contains two parameters, the first is the array variable name and the second is the separator of the array content. After the conversion is completed, the contents of the array are linked by delimiters to form a string:
$implodedarray = implode ($myarray, ", ");
print $implodedarray;
You can also use the array_walk function to implement the display of arrays. This function performs the same function operation on each content of the array. For example:
function printelement ($element)
{
print ("$element< p>");
}
array_walk($myarray, "printelement");
How to send data to MySQL in PHP
Author: Yangmei Compiled Number of clicks on this article: 398
You should have a better understanding of 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 the data and press the submit button, this form will send the data to submitform.php3 . This PHP script then processes the received data. The following is the code for 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 ("Thanks for filling in the registration form");
?>
< /body>
< /html>
"username" and "password" in the third line of the code represent your account and password for logging into the MySQL database. "dbname" in the fifth line indicates the name of the MySQL database. "tablename" in line 13 is the name of a data table in the database.
After you press submit, you can see that the name you entered is displayed in a new page. Let’s take a look at the URL bar of the browser, and its content should be like this:
… /submitform.php3?first_name=Fred&last_name=Flintstone
Because we are using the form GET method, the data is transferred to submitform.php3 through the URL. Obviously, the GET method has limitations. When there is a lot of content to be passed, you cannot use GET, you can only use the POST method. But no matter what method is used, when the data transfer is completed, PHP automatically creates a variable with the same name as their name (the form's name attribute) for each field in the form.
PHP variables have all started with a dollar sign. In this way, during the process of submitform.php3 script processing, there will be two variables, $first_name and $last_name. The content of the variable is the content you entered.
Let's check whether 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 that is what you just entered:
+------------+------------+
| first_name | last_name |
+------------+------------+
| Liu | Rufeng
+------------+------------+
1 rows in set (0.00 sec)
Let's analyze how submitform.php3 works:
The 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 specific parameters have been mentioned just now.
The following line is to execute a SQL statement:
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
The mysql_query function is used to perform a SQL query on the selected database. You can execute any SQL statement in the mysql_query function. The executed SQL statement must be enclosed in double quotes as a string, and the variables in it must be enclosed in single quotes.
There is one thing to note: MySQL statements should be ended 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 the MySQL command at the prompt in mysql>, 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 needs to be removed. This is because some statements, such as SELECT and INSERT, can work without semicolons. But there are some statements, such as UPDATE, and it 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>
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 must be a php script to process this form, and we will 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 record was 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 tables. These are necessary for each database application. Then there are a few 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. It should be noted that the two equal signs are, because most of the syntax of PHP originates from C language, and the usage of equal signs here is the same as C: an equal sign is an assignment sign, and only 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 for SQL language. After understanding one point, you should know what these two lines mean: If the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences also 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 search. When the mysql_query function completes a query, it returns an integer flag.
The query selects records from all records that have the same first_name column and the $first_name variable, and the last_name column and the $last_name variable values, and puts them in the temporary record set, and uses the returned integer as the flag 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 record was found in our database. ";}
This is the last step, and it is to display the part. The mysql_fetch_array function first extracts the content of the first line of the query result and displays it using the PRINT statement. The parameters of this function are the integer flags returned by the mysql_query function. After the mysql_fetch_array is executed successfully, the record set pointer will automatically move down. In this way, when mysql_fetch_array is executed again, the content of the next line of record is 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 curly braces 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. Unlike while loop, it first executes the loop body and then checks whether the loop condition is satisfied. Since we already know that when the record set is not empty, we must at least execute the loop body once, so we should use do … while instead of while loop. In curly braces is the loop body to be executed:
print $row["fir
How to extract data from MySQL in PHP
Author: Yangmei Compiled Number of clicks on this article: 398
Now we create another HTML form to perform this task:
< html>
< body>
< form action=searchform.php3 method=GET>
Please enter your query content:
< 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 must be a php script to process this form, and we will 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 record was 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 tables. These are necessary for each database application. Then there are a few 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. It should be noted that the two equal signs are, because most of the syntax of PHP originates from C language, and the usage of equal signs here is the same as C: an equal sign is an assignment sign, and only 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 for SQL language. After understanding one point, you should know what these two lines mean: If the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences also 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 search. When the mysql_query function completes a query, it returns an integer flag.
The query selects records from all records that have the same first_name column and the $first_name variable, and the last_name column and the $last_name variable values, and puts them in the temporary record set, and uses the returned integer as the flag 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 record was found in our database. ";}
This is the last step, and it is to display the part. The mysql_fetch_array function first extracts the content of the first line of the query result and displays it using the PRINT statement. The parameters of this function are the integer flags returned by the mysql_query function. After the mysql_fetch_array is executed successfully, the record set pointer will automatically move down. In this way, when mysql_fetch_array is executed again, the content of the next line of record is 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 curly braces 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. Unlike while loop, it first executes the loop body and then checks whether the loop condition is satisfied. Since we already know that when the record set is not empty, we must at least execute the loop body once, so we should use do … while instead of while loop. In curly braces is the loop body to be executed:
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
Then 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 loops around. When no next record exists, mysql_fetch_array returns false, the loop ends, and the record set is completely traversed once.
The returned array can not only be called with the field name, but also with subscripts to refer to the various components of the array 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 record was found in our database. ";}
Check if the query works normally
Author: Yangmei Compiled Number of clicks on this article: 398
Do your SELECT, DELETE or other queries work properly? This must be understood, and never draw conclusions easily.
Checking an INSERT query is relatively simple:
$result = mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
if(!$result)
{
echo "< b>INSERT query failed :< /b> ", mysql_error();
exit;
}
However, this checking method does not work for SELECT query. At this time, this should be done:
$selectresult = mysql_query ("SELECT * FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_num_rows($selectresult) == 1)
{
print "SELECT query succeeded.";
}
elseif (mysql_num_rows($selectresult) == 0)
{
print "SELECT query failed.";
exit;
}
And for DELETE queries, this should be:
$deleteresult = mysql_query ("DELETE FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_affected_rows($deleteresult) == 1)
{
print "DELETE query succeeded";
}
elseif (mysql_affected_rows($deleteresult) != 1)
{
print "DELETE query failed";
exit;
}
Why use the database?
World Wide Web (WWW) is more than just a place to provide information. If you have something, you can also share it with people all over the world. However, this is not an easy task. As the website gets bigger and bigger, you may encounter problems like this:
The website contains too many things that make visitors unable to get what they want very quickly. This problem is to some extent fatal to a website.
Visitors want to provide you with information, and this information must be saved for later use.
The above two problems can be solved through the database!
In the WWW world, databases are everywhere. As big as Yahoo!, Amazon, eBay, as small as a simple message board, you can see where the database comes in. It can even be said that databases are the basis of all advanced applications.
Why use PHP and MYSQL
As far as I know, almost all the major commercial website databases are SQL-based. The most popular one is probably Oracle. It is very powerful, and of course, it is also expensive. SQL is not an application, but a language. It is the abbreviation of Structured Query Language, used to operate and query databases.
In recent years, some companies have developed "open code" SQL applications, the most famous of which may be considered MySQL. It is not just free, but it does not perform worse than Oracle for general small and medium-sized database applications.
To run MySQL on a website, you need a scripting language to interact with the database. In the past, Perl was the most popular. But now it seems PHP seems to be a little better. Don't ask me what the difference is between them?? I used to use Perl and it also worked very well, but now it seems that everyone likes to use PHP. Its popularity certainly has its reasons.
Required software
The content of this part has been introduced in ChinaByte Network Academy's articles a few times ago. Readers can refer to the article "Setting Up Local PHP Development for Win98". I won't introduce it in detail here.
HTML and PHP
Author: Yangmei Compiled Number of clicks on this article: 398
Let's take a look at how PHP works. Take a look at the following code:
< html>
< body>
< ?php
print "Hello, world.";
?>
< /body>
< /html>
When requesting this page, it will display "Hello, world" in the browser.
As you can see, the PHP script is embedded in the HTML file. It starts with " < ? " and ends with " ?> ". Not only that, we can even embed HTML tags in PHP scripts:
< ?php
print "< html>";
print "< body>";
print "Hello, world.";
print "< /body>";
print "< /html>";
?>
The two methods have the same outcome, and the effects are the same. But in some special cases, it is more convenient to choose one of them.
PHP prints statement
Author: Yangmei Compiled Number of clicks on this article: 398
The simplest interaction between PHP and HTML is achieved through print statements:
< ?php
print "Hello, world.";
?>
print is the simplest and most commonly used function, used to display some text in a browser window. The echo function is similar to print, but you can use the "," to separate multiple contents to be displayed, which is more convenient when mixing the display of string constants and variables.
There is also a printf function that formats the output of numbers. A number can be displayed as an integer or using scientific notation.
Among these functions, the use of parentheses is different:
echo must not be bracketed
printf must have
print optional
It is simple to display a string or a number, just follow the variable name or constant after the print statement. However, if you want to display an array, is it written like this:
print $myarray;
The result of its output will be "Array", and PHP tells you that $myarray is an array. This will be useful if you are not sure whether a variable is an array, but now what we want to see is the content of the array.
You can use the implode function to convert an array into a string. It contains two parameters, the first is the array variable name and the second is the separator of the array content. After the conversion is completed, the contents of the array are linked by delimiters to form a string:
$implodedarray = implode ($myarray, ", ");
print $implodedarray;
You can also use the array_walk function to implement the display of arrays. This function performs the same function operation on each content of the array. For example:
function printelement ($element)
{
print ("$element< p>");
}
array_walk($myarray, "printelement");
How to send data to MySQL in PHP
Author: Yangmei Compiled Number of clicks on this article: 398
You should have a better understanding of 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 the data and press the submit button, this form will send the data to submitform.php3 . This PHP script then processes the received data. The following is the code for 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 ("Thanks for filling in the registration form");
?>
< /body>
< /html>
"username" and "password" in the third line of the code represent your account and password for logging into the MySQL database. "dbname" in the fifth line indicates the name of the MySQL database. "tablename" in line 13 is the name of a data table in the database.
After you press submit, you can see that the name you entered is displayed in a new page. Let’s take a look at the URL bar of the browser, and its content should be like this:
… /submitform.php3?first_name=Fred&last_name=Flintstone
Because we are using the form GET method, the data is transferred to submitform.php3 through the URL. Obviously, the GET method has limitations. When there is a lot of content to be passed, you cannot use GET, you can only use the POST method. But no matter what method is used, when the data transfer is completed, PHP automatically creates a variable with the same name as their name (the form's name attribute) for each field in the form.
PHP variables have all started with a dollar sign. In this way, during the process of submitform.php3 script processing, there will be two variables, $first_name and $last_name. The content of the variable is the content you entered.
Let's check whether 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 that is what you just entered:
+------------+------------+
| first_name | last_name |
+------------+------------+
| Liu | Rufeng
+------------+------------+
1 rows in set (0.00 sec)
Let's analyze how submitform.php3 works:
The 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 specific parameters have been mentioned just now.
The following line is to execute a SQL statement:
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
The mysql_query function is used to perform a SQL query on the selected database. You can execute any SQL statement in the mysql_query function. The executed SQL statement must be enclosed in double quotes as a string, and the variables in it must be enclosed in single quotes.
There is one thing to note: MySQL statements should be ended 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 the MySQL command at the prompt in mysql>, 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 needs to be removed. This is because some statements, such as SELECT and INSERT, can work without semicolons. But there are some statements, such as UPDATE, and it 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>
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 must be a php script to process this form, and we will 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 record was 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 tables. These are necessary for each database application. Then there are a few 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. It should be noted that the two equal signs are, because most of the syntax of PHP originates from C language, and the usage of equal signs here is the same as C: an equal sign is an assignment sign, and only 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 for SQL language. After understanding one point, you should know what these two lines mean: If the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences also 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 search. When the mysql_query function completes a query, it returns an integer flag.
The query selects records from all records that have the same first_name column and the $first_name variable, and the last_name column and the $last_name variable values, and puts them in the temporary record set, and uses the returned integer as the flag 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 record was found in our database. ";}
This is the last step, and it is to display the part. The mysql_fetch_array function first extracts the content of the first line of the query result and displays it using the PRINT statement. The parameters of this function are the integer flags returned by the mysql_query function. After the mysql_fetch_array is executed successfully, the record set pointer will automatically move down. In this way, when mysql_fetch_array is executed again, the content of the next line of record is 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 curly braces 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. Unlike while loop, it first executes the loop body and then checks whether the loop condition is satisfied. Since we already know that when the record set is not empty, we must at least execute the loop body once, so we should use do … while instead of while loop. In curly braces is the loop body to be executed:
print $row["fir
How to extract data from MySQL in PHP
Author: Yangmei Compiled Number of clicks on this article: 398
Now we create another HTML form to perform this task:
< html>
< body>
< form action=searchform.php3 method=GET>
Please enter your query content:
< 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 must be a php script to process this form, and we will 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 record was 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 tables. These are necessary for each database application. Then there are a few 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. It should be noted that the two equal signs are, because most of the syntax of PHP originates from C language, and the usage of equal signs here is the same as C: an equal sign is an assignment sign, and only 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 for SQL language. After understanding one point, you should know what these two lines mean: If the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences also 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 search. When the mysql_query function completes a query, it returns an integer flag.
The query selects records from all records that have the same first_name column and the $first_name variable, and the last_name column and the $last_name variable values, and puts them in the temporary record set, and uses the returned integer as the flag 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 record was found in our database. ";}
This is the last step, and it is to display the part. The mysql_fetch_array function first extracts the content of the first line of the query result and displays it using the PRINT statement. The parameters of this function are the integer flags returned by the mysql_query function. After the mysql_fetch_array is executed successfully, the record set pointer will automatically move down. In this way, when mysql_fetch_array is executed again, the content of the next line of record is 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 curly braces 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. Unlike while loop, it first executes the loop body and then checks whether the loop condition is satisfied. Since we already know that when the record set is not empty, we must at least execute the loop body once, so we should use do … while instead of while loop. In curly braces is the loop body to be executed:
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
Then 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 loops around. When no next record exists, mysql_fetch_array returns false, the loop ends, and the record set is completely traversed once.
The returned array can not only be called with the field name, but also with subscripts to refer to the various components of the array 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 record was found in our database. ";}
Check if the query works normally
Author: Yangmei Compiled Number of clicks on this article: 398
Do your SELECT, DELETE or other queries work properly? This must be understood, and never draw conclusions easily.
Checking an INSERT query is relatively simple:
$result = mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
if(!$result)
{
echo "< b>INSERT query failed :< /b> ", mysql_error();
exit;
}
However, this checking method does not work for SELECT query. At this time, this should be done:
$selectresult = mysql_query ("SELECT * FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_num_rows($selectresult) == 1)
{
print "SELECT query succeeded.";
}
elseif (mysql_num_rows($selectresult) == 0)
{
print "SELECT query failed.";
exit;
}
And for DELETE queries, this should be:
$deleteresult = mysql_query ("DELETE FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_affected_rows($deleteresult) == 1)
{
print "DELETE query succeeded";
}
elseif (mysql_affected_rows($deleteresult) != 1)
{
print "DELETE query failed";
exit;
}