MySQL One, while loop
In this lesson, we'll go further and use PHP and MySQL to write some simple and useful pages. We start with the database we created yesterday and display the data in the library, but we'll touch it a little bit.
First, we use the following code to query the contents of the database.
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
$result = mysql_query ("SELECT * FROM Employees", $DB);
echo "<table border=1>\n";
echo "<tr><td> name </td><td> position </td></tr>\n";
while ($myrow = Mysql_fetch_row ($result)) {
printf ("<tr><td>%s%s</td><td>%s</td></tr>\n", $myrow [1], $myrow [2], $myrow [3] );
}
echo "</table>\n";
?>
</body>
You may have noticed that we have added something new to this program. The most obvious is the while () loop. The loop is to say that as long as the database has records to read (using the Mysql_fetch_row () function), assign the record to the variable $myrow, and then execute the instructions within the braces ({}). Take a closer look here, this part is more important.
We should pay attention to the mysql_fetch_row () function. Here's a little problem, it returns an array, and you must access one of the fields with an array subscript. The first field is labeled 0, the second is 1, and so on. When performing some complex queries, this is simply too cumbersome.
Now let's look more closely at the cycle process. The first few lines of the program we have seen in the example of lesson one. Then, in the while () loop, we read a record from the query result and assign the record to the array $myrow. Next, we use the printf function to display the contents of the data on the screen. Subsequently, the loop executes repeatedly, reading the next record to $myrow. Keep going until all the records have been read.
One advantage of using the while () loop is that you do not receive an error message if the database query does not return any records. When the loop statement is just executed, the loop condition is not satisfied and no data is assigned to the $myrow, and the program runs directly down.
But if the query doesn't return any data, how do we let users know about it? Perhaps we should provide some relevant information to the user. This can be done, and we'll see how to do it below. >>
Second, If-else
Please see the following procedure.
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
$result = mysql_query ("SELECT * FROM Employees", $DB);
if ($myrow = mysql_fetch_array ($result)) {
echo "<table border=1>\n";
echo "<tr><td> name </td><td> address </td></tr>\n";
do {
printf ("<tr><td>%s%s</td><td>%s</tr>\n", $myrow ["a"], $myrow ["Last"], $myrow [" Address "]);
}
while ($myrow = Mysql_fetch_array ($result));
echo "</table>\n";
} else {
echo "Sorry, no record found!" ";
}
?>
</body>
This program contains a lot of new content, but these are fairly simple. The first is the mysql_fetch_array () function. The function is very similar to mysql_fetch_row (), and is only a bit different: when using this function, we can access the field returned by the field name rather than the array subscript, such as $myrow["first". So we can save a lot of energy. In addition, the Do/while Loop and if-else conditional judgment statement are added in the program.
The implication of the IF-ELSE conditional decision statement is that if we successfully assign a record to the $myrow variable, we continue; otherwise, we skip to the else part and execute the instructions there.
The Do/while loop is a variant of the while () loop of the user in the previous page. The reason we need to use do/while is that in the original if statement, we have assigned the first record returned by the query to the variable $myrow. If we perform a normal while loop (for example, while ($myrow = Mysql_fetch_row ($result)), we assign the second record to $myrow, and the first record is flushed out. But the Do/while loop allows us to perform a loop-body content once again to determine the cyclic condition. Therefore, we will not accidentally miss the first record.
Finally, if the query results do not have any records, the program executes the statements contained in the else{} section. If you want to see the execution of this part of the program, you can change the SQL statement to select * FROM Employees WHERE id=6, or to other forms, so that there are no records in the query results.
Let's expand the loop If-else code to make the page content richer. I'm sure you'll like it. >>
Third, the first program script
We've just learned the loop statement, and we'll see how to use it in a more practical example. But before that, you should know how to work with Web tables, query parameter strings, and the Get and post methods of a form. Not long ago we just an article to introduce this part of the content, if you are not familiar with this part can look at the article.
Now, we're going to handle the query parameter string, and as you know, there are three ways to write the parameter contents to the query parameter string. The first is to use the Get method in the table; the second is to add the query parameters to the URL in the browser's address bar, and the third is to embed the query parameter string into the hyperlinks of the Web page so that the contents of the hyperlink are as follows: <a href= "http://my_machine/ Mypage.php3?id=1 ">. We're going to use the last one.
First, we'll check our database and list the names of the employees. Look at the following program, most of which we are already familiar with.
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
$result = mysql_query ("SELECT * FROM Employees", $DB);
if ($myrow = mysql_fetch_array ($result)) {
do {
printf ("<a href=\"%s?id=%s\ ">%s%s</a><br>\n", $PATH _info, $myrow ["id"], $myrow ["a"], $myrow [" Last "]);
while ($myrow = Mysql_fetch_array ($result));
} else {
echo "Sorry, no record found!" ";
}
?>
</body>
There's nothing special here, but the printf function is a little different. So let's take a closer look.
The first thing to note is that all quotes are preceded by a backslash. This backslash tells PHP to display the following characters directly, not the following characters as the program code to handle. Also pay attention to the use of variable $path_info. The variable is accessible in the program used to hold the program's own name and directory location. We use it because we want to call the program itself in the page. With $path_info, we can do that even if the program is moved to another directory, or even to other machines, we can ensure that the program is invoked correctly.
As I mentioned earlier, a program generates a Web page that contains hyperlinks that call the program itself again. However, when called again, some query parameters are added.
When PHP sees that the query parameter string contains a pair format such as "name = value", some special processing is done. It automatically generates a variable whose name and value are the same as the name and value given in the query parameter string. This function allows us to determine in the program whether the first time the program is executed or the second time. All we have to do is ask Php$id if this variable exists.
When I know the answer to this question, I can show some different results when I invoke the program the second time. Please see:
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
Display individual record
Show single record content
if ($id) {
$result = mysql_query ("SELECT * FROM Employees WHERE id= $id", $db);
$myrow = Mysql_fetch_array ($result);
printf ("Name:%s\n<br>", $myrow ["a"]);
printf ("Surname:%s\n<br>", $myrow ["last"]);
printf ("Address:%s\n<br>", $myrow ["addresses"]);
printf ("Position:%s\n<br>", $myrow ["position"]);
} else {
Show Employee List
Show list of employees
$result = mysql_query ("SELECT * FROM Employees", $DB);
if ($myrow = mysql_fetch_array ($result)) {
Display list If there are records to display
If there are records, the list is displayed
do {
printf ("<a href=\"%s?id=%s\ ">%s%s</a><br>\n", $PATH _info,
$myrow ["id"], $myrow ["a"], $myrow ["last"]);
while ($myrow = Mysql_fetch_array ($result));
} else {
No records to display
No record to show echo "Sorry, no record found!" ";
}
}
?>
</body>
The program started getting complicated, so I added a note here to explain what happened. You can add a single-line comment with///////to enclose a large paragraph of comments.
Here, we have learned the first really useful php/mysql script program! Now we're going to look at how to add a Web table and send data to the database. >>
Four, send data to the server
Now we don't have much difficulty reading data from the database. But how do you send data to the database in turn? In fact, this is not a problem with PHP.
First, we create a Web page with a simple table.
<body>
<form method= "POST" action= "<?php echo $PATH _info?>" >
Name: <input type= "Text" name= "one" ><br>
Surname: <input type= "Text" Name= "Last" ><br>
Address: <input type= "Text" name= "adress" ><br>
Position: <input type= "Text" name= "position" ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
</body>
Also pay attention to the use of $path_info. As I mentioned in the first lesson, you can use PHP anywhere in the HTML code. You will also notice that each element in the table corresponds to a field in the database. This correspondence is not necessary, it is only a little more intuitive to make it easier for you to understand the code later.
Also note that I added the name attribute to the Submit button. So I can test the existence of $submit variables in the program. Then, when the page is called again, I know whether the page has been filled in with the form when it is called.
I should point out that you do not have to write the contents of the above page into a PHP program, and then come back and call the program itself. You can put the page that shows the table and the process of processing the table separately on two pages, three pages or even more pages, whichever you want. Putting it in a file can only make the content more compact.
Well, now we're going to add some code to check what the user has entered into the table. I'll show all the query parameter variables with $http_post_vars, just to show that PHP does pass all variables to the program. This method is a very useful debugging tool. If you want to see all the variables, you can use $globals.
<body>
<?php
if ($submit) {
Working with form input
while (the list ($name, $value) = each ($HTTP _post_vars)) {
echo "$name = $value <br>\n";
}
} else{
Show Table
?>
<form method= "POST" action= "<?php echo $PATH _info?>" >
Name: <input type= "Text" name= "one" ><br>
Surname: <input type= "Text" Name= "Last" ><br>
Address: <input type= "Text" name= "adress" ><br>
Position: <input type= "Text" name= "position" ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
<?php
}//End If,if
?>
</body>
Now that the program is working properly, we can now take the contents of the form and send them to the database.
<body>
<?php
if ($submit) {
Working with form input
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
$sql = "INSERT into employees (first,last,address,position)
VALUES (' $first ', ' $last ', ' $address ', ' $position ');
$result = mysql_query ($sql);
echo "Thank you! Information entered.\n ";
} else{
Show Table Contents
?>
<form method= "POST" action= "<?php echo $PATH _info?>" >
Name: <input type= "Text" name= "one" ><br>
Surname: <input type= "Text" Name= "Last" ><br>
Address: <input type= "Text" name= "adress" ><br>
Position: <input type= "Text" name= "position" ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
<?php
}//End If,if
?>
</body>
You have now inserted data into the database. But there are still a lot of good work to do. What if a user doesn't fill out a column? What to do if you fill in a number where you need to fill in the text? Or fill in the wrong?
Don't worry. Let's take it one step at a step. >>
V. Modification of data
In a tutorial, I put the SQL statement to execute into a variable ($sql) and then use mysql_query () to execute the database query. This is useful when debugging. If something is wrong with your program, you can always display the contents of the SQL statement and check for grammatical errors.
We've learned how to insert data into a database. Now let's learn how to modify the records that are already in the database. The editing of the data consists of two parts: the data display and the data returned to the database through the form input, both of which we have already mentioned. However, data editing is a little bit different, and we have to show the relevant data in the table first.
First, we'll look back at the first lesson's program code, and display the employee's name in the Web page. But this time, we're going to show the data in the table. The program looks something like the following:
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
if ($id) {
Querying the database
$sql = "SELECT * FROM Employees WHERE id= $id";
$result = mysql_query ($sql);
$myrow = Mysql_fetch_array ($result);
?>
<form method= "POST" action= "<?php echo $PATH _info?>" >
<input type=hidden name= "id" value= "<?php echo $myrow [" id "]?>" >
Name: <input type= "Text" Name= "the" One "value=" <?php Echo
$myrow ["?>]" ><br>
Surname: <input type= "Text" Name= "Last" value= "<?php Echo
$myrow ["Last"]?> ><br>
Address: <input type= "Text" name= "Addresses" value= "<?php Echo
$myrow ["Address"]?> "><br>
Position: <input type= "Text" name= "position" value= "<?php Echo
$myrow ["position"]?> ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
<?php
} else {
Show list of employees
$result = mysql_query ("SELECT * FROM Employees", $DB);
while ($myrow = Mysql_fetch_array ($result)) {
printf ("<a href=\"%s?id=%s\ ">%s%s</a><br>\n", $PATH _info,
$myrow ["id"], $myrow ["a"], $myrow ["last"]);
}
}
?>
</body>
We have just written the contents of the field into the value attribute of the corresponding table element, which is correspondingly simple. Let's go further and make it possible for the program to write the user's modified content back to the database. Again, we use the Submit button to determine if the table entry is processed. Also note that the SQL statements we use are slightly different.
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
if ($id) {
if ($submit) {
$sql = "UPDATE employees SET first= ' $first ', last= ' $last ',
Address= ' $address ', position= ' $position ' WHERE id= $id ';
$result = mysql_query ($sql);
echo "Thank you!" Data change complete \ n ";
} else {
Querying the database
$sql = "SELECT * FROM Employees WHERE id= $id";
$result = mysql_query ($sql);
$myrow = Mysql_fetch_array ($result);
?>
<form method= "POST" action= "<?php echo $PATH _info?>" >
<input type=hidden name= "id" value= "<?php echo $myrow [" id "]?>" >
Name: <input type= "Text" Name= "the" "Value=" <?php
echo $myrow ["?>]" ><br>
Surname: <input type= "Text" Name= "Last" value= "<?php Echo
$myrow ["Last"]?> ><br>
Address: <input type= "Text" name= "Addresses" value= "<?php Echo
$myrow ["Address"]?> "><br>
Position: <input type= "Text" name= "position" value= "<?php Echo
$myrow ["position"]?> ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
<?php
}
} else {
Show list of employees
$result = mysql_query ("SELECT * FROM Employees", $DB);
while ($myrow = Mysql_fetch_array ($result)) {
printf ("<a href=\"%s?id=%s\ ">%s%s</a><br>\n", $PATH _info,
$myrow ["id"], $myrow ["a"], $myrow ["last"]);
}
}
?>
</body>
That's it. This program already contains most of the features we've learned. You have also seen that we have added an if () statement to check for multiple conditions in an if () conditional discriminant statement.
Next, we're going to add everything together and write a good program. >>
Vi. Complete Procedures
Before the end of the lesson, we'll add everything to a program that has the ability to add, edit, and delete records. This is an extension of all the previous content, and can also be an excellent review method. Look at the following procedure.
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
if ($submit) {
If there is no ID, then we are increasing the record, otherwise we are modifying the record
if ($id) {
$sql = "UPDATE employees SET first= ' $first ', last= ' $last ',
Address= ' $address ', position= ' $position ' WHERE id= $id ';
} else {
$sql = "INSERT into employees (first,last,address,position)
VALUES (' $first ', ' $last ', ' $address ', ' $position ');
}
To issue SQL commands to the database
$result = mysql_query ($sql);
echo "Record modification successful! <p> ";
} elseif ($delete) {
Delete a record
$sql = "DELETE from Employees WHERE id= $id";
$result = mysql_query ($sql);
echo "Record deletion succeeded!" <p> ";
} else {
If we haven't pressed the submit button, then execute the following part of the program
if (! $id) {
If the status is not modified, the list of employees is displayed $result = mysql_query ("SELECT * FROM Employees", $DB);
while ($myrow = Mysql_fetch_array ($result)) {
printf ("<a href=\"%s?id=%s\ ">%s%s</a> \ n",
$PATH _info, $myrow ["id"], $myrow ["a"], $myrow ["last"]);
printf ("<a href=\"%s?id=%s&delete=yes\ "> (delete) </a><
Br> ", $PATH _info, $myrow [" id "]);
}
}
?>
<P>
<a href= "<?php echo $PATH _info?>" >add a record</a>
<P>
<form method= "POST" action= "<?php echo $PATH _info?>" >
<?php
if ($id) {
We are editing the change status, because we select a record
$sql = "SELECT * FROM Employees WHERE id= $id";
$result = mysql_query ($sql);
$myrow = Mysql_fetch_array ($result);
$id = $myrow ["id"];
$first = $myrow ["a"];
$last = $myrow ["Last"];
$address = $myrow ["Address"];
$position = $myrow ["position"];
Display IDs for users to edit and modify
?>
<input type=hidden name= "id" value= "<?php echo $id?>" >
<?php
}
?>
Name: <input type= "Text" name= "one" value= "<?php echo $first?>" ><br>
Surname: <input type= "Text" Name= "Last" value= "<?php echo $last?>" ><br>
Address: <input type= "Text" name= "adress" value= "<?php echo $address?>" ><br>
Position: <input type= "Text" name= "position" value= "<?php echo $position?>" ><br>
<input type= "Submit" name= "submit" value= "Input Info" >
</form>
<?php
}
?>
</body>
< '/html>
The program looks complicated, but it's not really difficult. There are three main parts of the program. The first if () statement checks whether we have pressed the "Enter information" data submission button. If so, the program checks to see if the $id exists. If it does not exist, then we are increasing the state of the record, otherwise we are modifying the record state.
Next we check whether the variable $delete exists. If there is, we are going to delete the record. Note that the first if () statement examines a variable that is sent with the Post method, and this time we examine the variable passed in the Get method.
Finally, the default action of the program is to display a list of employees and tables. Again, we want to check whether the variable $id exists. If it exists, we will retrieve the corresponding record according to its value. Otherwise, we will display an empty table.
Now, we've put everything we've learned into one program. We used the while () loop, used the IF () statement, and performed all the basic SQL operations-SELECT, INSERT, update, and delete. In addition, we also know how to transfer information between different Web pages through URL and form input.
In the third lesson, we want to learn how to increase the intelligent processing capacity of the Web page.
