PHP_MySQL tutorial-the next day while loop and database operations

PHP_MySQL tutorial-the next day while loop and the first page of database operations while loop
In this lesson, we will continue to use PHP and MySQL to write some simple and useful pages. We started to display the data in the database from the database we created yesterday, but it will be slightly improved.
First, we use the following code to query the database content.

The code is as follows:

    
 N "; echo"NamePositionN "; while ($ myrow = mysql_fetch_row ($ result) {printf ("% S% SN ", $ myrow [1], $ myrow [2], $ myrow [3]);} echo"N ";?>  

You may have noticed that we have added some new things in this program. The most obvious is the while () loop. This loop means that as long as there are records in the database that are readable (using the mysql_fetch_row () function), the record is assigned to the variable $ myrow and then braces ({}) are executed ({}). Taking a closer look, this part is important.
Note the mysql_fetch_row () function. There is a small problem here. it returns an array and must access a field in it with an array subscript. The first field subscript is 0, the second is 1, and so on. It is too cumbersome to execute some complex queries.
Now let's take a closer look at the cyclical process. We have seen the first few lines of the program in the example of lesson 1. Then, in the while () loop, we read a record from the query results and assigned the record to the array $ myrow. Then, we use the printf function to display the data content on the screen. Then, the loop is executed repeatedly, and the next record is read and assigned to $ myrow. This continues until all records have been read.
One advantage of using the while () loop is that if the database query does not return any records, you will not receive the error message. When the loop statement is just executed, the loop condition is not met and no data is assigned to $ myrow. then the program runs directly.
But if no data is returned for the query, how can we let the user know this? Maybe we should provide some related messages to users. This can be done. let's take a look at how to do it.>

Page 2 if-else
See the following program.

The code is as follows:

    
 N "; echo"NameAddressN "; do {printf ("% S% SN ", $ myrow [" first "], $ myrow [" last "], $ myrow [" address "]);} while ($ myrow = mysql_fetch_array ($ result); echo"N ";} else {echo" Sorry, no record found! ";}?>  

This program contains a lot of new content, but the content is quite simple. The first is the mysql_fetch_array () function. This function is very similar to mysql_fetch_row () and has only one difference: when using this function, we can use the field name instead of the array subscript to access the fields it returns, such as $ myrow ["first"]. In this way, we can save a lot of effort. In addition, the do/while loop and if-else condition judgment statements are added to the program.
The if-else condition determination statement means that if we successfully assign a record to the $ myrow variable, we will continue; otherwise, we will jump to the else part and execute the commands there.
The do/while loop is a variant of the user's while () loop on the previous page. The reason we need to use do/while is: in the initial if statement, we have assigned the first record returned by the query to the variable $ myrow. If we execute a general while loop (for example, while ($ myrow = mysql_fetch_row ($ result), we will assign the second record to $ myrow, the first record is washed out. However, the do/while loop allows us to determine the loop condition after executing the content of the loop body. Therefore, we will not accidentally miss the first record.
Finally, if the query results do not have any records, the program will execute the statements contained in else. 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 change it to another form, so that there is no record in the query result.
Next we will expand the circular if-else code to enrich the page content. I believe you will like it.

Page 3 First program script
We have just learned a circular statement. next we will look at how to use it in a more practical example. However, before that, you should know how to process Web tables, query parameter strings, and GET and POST methods of forms. We have just introduced this part of the article. if you are not familiar with this part, you can refer to it.
Now, we need to process the query parameter string. as you know, there are three methods to write the parameter content into the query parameter string. The first is to use the GET method in the table; the second is to add query parameters directly when entering the URL in the address bar of the browser; the third is to embed the query parameter string into the hyperlink of the webpage, make the hyperlink content as follows :. Now we need to use the last method.
At the beginning, we will query our database to list employee names. Take a look at the following program. we are familiar with most of the content.

The code is as follows:

    
 N ", $ PATH_INFO, $ myrow [" id "], $ myrow [" first "], $ myrow [" last "]);} while ($ myrow = mysql_fetch_array ($ result);} else {echo "Sorry, no record found! ";}?>  

There is nothing special here, but the printf function is somewhat different. Let's take a closer look.
Note that all quotation marks are preceded by a backslash. This backslash tells PHP to directly display the following characters, instead of processing the subsequent characters as program code. Pay attention to the usage of the variable $ PATH_INFO. This variable can be accessed in the program in use and is used to save the name and directory location of the program. We use it because we need to call the program itself on the page. With $ PATH_INFO, we can ensure that this program can be correctly called even when the program is moved to another directory or even on another machine.
As I mentioned earlier, the web page generated by the program contains hyperlinks that call the program itself again. However, some query parameters will be added when you call it again.
When PHP sees that the query parameter string contains a pair format such as "name = value", it will perform some special processing. It automatically generates a variable. Both the variable 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 program is executed for the first time or for the second time. All we have to do is ask whether the variable PHP $ id exists.
When I know the answer to this question, I can display different results when calling the program for the second time. See:

The code is as follows:

    
 ", $ Myrow [" first "]); printf (" surname: % sn
", $ Myrow [" last "]); printf (" Address: % sn
", $ Myrow [" address "]); printf (" position: % sn
", $ Myrow [" position "]);} else {// show employee list // Display employee list $ 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 do {printf ("% s
N ", $ PATH_INFO, $ myrow [" id "], $ myrow [" first "], $ myrow [" last "]);} while ($ myrow = mysql_fetch_array ($ result);} else {// no records to display // no record can display echo "Sorry, no record found! ";}}?>  

The program began to become complicated, so I added a comment here to explain what happened. You can use // to add a single line comment, or use/* and */to enclose the comment of a large segment.
Here, we have learned the first truly useful PHP/MySQL script program! Now, let's look at how to add a Web table and send data to the database.

Page 4: send data to the server
It is no longer difficult to read data from the database. But how can we send data to the database in turn? In fact, this is not a problem with PHP.
First, create a webpage with a simple table.

The code is as follows:

Pay attention to the usage 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 more intuitive to understand the code later.
Note that the name attribute is added to the Submit button. In this way, I can test whether the $ submit variable exists in the program. When the webpage is called again, I will know whether the form has been filled in when the page is called.
I should point out that you do not have to write the content of the above webpage to the PHP program, and then return to call the program itself. You can separate the web page for displaying a table from the program for processing a table on two web pages, three web pages, or even more web pages. Put it in a file to make the content more compact.
Now let's add some code to check the content entered by the user in the table. I will display all the query parameter variables with $ HTTP_POST_VARS, just to prove that PHP has actually passed all the variables to the program. This method is a useful debugging method. To view all the variables, use $ GLOBALS.

The code is as follows:

    
 N ";}} else {// Display Table?>
     The program runs normally now, so we can get the input content in the table and send them to the database.    
         
     

You have already inserted data into the database. However, there is still a lot of work to do. What if the user does not enter a column? What should I do if I fill in the text where a number is needed? Or fill in the wrong information. what should I do?
Don't worry. Let's take a step.

Page 5 modify data
In this tutorial, I put the SQL statement to be executed into a variable ($ SQL) before using mysql_query () to execute database queries. This is useful during debugging. If something goes wrong with the program, you can display the content of the SQL statement at any time and check for syntax errors.
We have learned how to insert data into the database. Now let's learn how to modify the existing records in the database. Data Editing consists of two parts: Data Display and table input to return data to the database. we have already mentioned these two parts. However, data editing is a little different. we must first display the relevant data in the table.
First, let's look back at the program code of lesson 1 and display the employee name on the webpage. But this time, we will display the data in the table. The program looks like the following:

The code is as follows:

    
         
 N ", $ PATH_INFO, $ myrow [" id "], $ myrow [" first "], $ myrow [" last "]) ;}}?>  

We just wrote the field content to the value attribute in the corresponding table element. this is simple. Let's proceed further so that the program can write the modified content back to the database. Similarly, we can use the Submit button to determine whether to process table input content. Note that the SQL statements we use are slightly different.

The code is as follows:

    
             
 N ", $ PATH_INFO, $ myrow [" id "], $ myrow [" first "], $ myrow [" last "]) ;}}?>  

That's it. This program already contains most of the features we have learned. You can also see that an if () statement is added to an if () condition discriminant statement to check multiple conditions.
Next, we will add everything together and write a good program.

Page 6 complete program
Before the end of this lesson, we need to add everything to a program so that it can add, edit, modify, and delete records. This is an extension of all the previous content and can also be used as an excellent review method. Look at the following program.

The code is as follows:

    
 ";} Elseif ($ delete) {// DELETE a record $ SQL =" delete FROM employees WHERE id = $ id "; $ result = mysql_query ($ SQL ); echo "record deleted successfully!
";} Else {// if we have not pressed the submit button, execute the following program if (! $ Id) {// if the status is not modified, the employee list is displayed. $ result = mysql_query ("SELECT * FROM employees", $ db ); while ($ myrow = mysql_fetch_array ($ result) {printf ("% s n", $ PATH_INFO, $ myrow ["id"], $ myrow ["first"], $ myrow ["last"]); printf ("(DELETE) <br>", $ PATH_INFO, $ myrow ["id"]) ;}}?>
"> ADD A RECORD
      
      

This process looks complicated, but it is not actually difficult. The program consists of three parts. The first if () statement checks whether we have pressed the "input information" data submit button. If yes, check whether $ id exists. If it does not exist, we are adding the record status. Otherwise, we are modifying the record status.
Next, check whether the variable $ delete exists. If yes, we want to delete the record. Note that the first if () statement checks the variables sent using the POST method, and this time we check the variables passed in the GET method.
Finally, the default action of the program is to display the employee list and table. Similarly, we need to check whether the variable $ id exists. If yes, we can retrieve the corresponding records based on their values. Otherwise, an empty table is displayed.
Now, we have put everything we have learned in a program. We used the while () loop, the if () statement, and executed all the basic SQL operations-SELECT, INSERT, UPDATE, and DELETE. In addition, we also know how to transmit information between different webpages through URL and table input.
In the third lesson, we need to learn how to add intelligent processing capabilities for webpages.