PHP operation MySQL 3rd pass the next day

Last Update:2017-09-14 Source: Internet

Author: User

Keywords Web Programming PHP Tutorials

Tags added address array button change check code content

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

In this lesson, we're going to do some more complicated work to display data from multiple rows of records, and databases Exchange data

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.

<html>
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB", $db);
$result = mysql_query ("SELECT * FROM Employees", $DB);
echo "<table border=1>";
echo "<tr><td> name </td><td> position </td></tr>";
while ($myrow = Mysql_fetch_row ($result)) {
printf ("<tr><td>%s%s</td><td>%s</td></tr>", $myrow [1], $myrow [2], $myrow [3]);
}
echo "</table>";
?>
</body>
</html>

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. We then 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. >>

Ii. Else

See the program below.

　　
<html>
<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>";
echo "<tr><td> name </td><td> address </td></tr>";
do {
printf ("<tr><td>%s%s</td><td>%s</tr>", $myrow ["a"], $myrow ["Last"], $myrow [" Address "]);
}
while ($myrow = Mysql_fetch_array ($result));
echo "</table>";
else {
echo "Sorry, no record found!" ";
}
?>
</body>
</html>

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 instead of the array subscript, such as $myrow["first". So we can save a lot of energy. In addition, the Do/while loop and else conditional judgment statement are added in the program.

The implication of the 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 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 and then determine the cyclic condition. So we don't accidentally miss the first record.

Finally, if the query results have no 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 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 scroll 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 parameter content 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.

　　
<html>
<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>", $PATH _info, $myrow ["id"], $myrow ["a"], $myrow ["Last "]);
while ($myrow = Mysql_fetch_array ($result));
else {
echo "Sorry, no record found!" ";
}
?>
</body>
</html>

There's nothing special here, but the printf function is a little different. 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 note the use of variable $path_info. This 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", it does some special processing. 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 to execute this program 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:

<html>
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB ", $db);
//Display individual record
//Show single Records content
if ($id) {
$result = mysql_query ("SELECT * FROM Employees WHERE id=$ ID ", $db);
$myrow = mysql_fetch_array ($result);
printf (name:%s <br>, $myrow ["a"]);
printf (LastName:%s <br>, $myrow ["last"]);
printf ("Address:%s <br>", $myrow ["adress"]);
printf (Position:%s <br>, $myrow ["position"]); 　　
} else {
//Show Employee List
//Display employee lists
$result = mysql_query ("SELECT * FROM Employees", $db);
　　if ($myrow = mysql_fetch_array ($result)) {
//Display list if there are records to display
///If there is a record, display the list
do {
printf ("<a href="%s?id=%s ">%s%s</a><br>", $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>
</html>

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 MySQL script! Now we're going to look at how to add a Web table and send data to the database.

Iv. sending 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? It's not really a PHP problem.

First, we create a Web page with a simple table.

<html>
<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>
</html>

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, let's add some code to check what the user has entered in 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 useful debugging tool. If you want to see all the variables, you can use $globals.

<html>
<body>
<?php
if ($submit) {
Working with form input
while (the list ($name, $value) = each ($HTTP _post_vars)) {
echo "$name = $value <br>";
}
} 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 Ends
?>
</body>
</html>

Now that the program is working properly, we can now take the contents of the form and send them to the database.

<html>
<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. ";
} 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 Ends
?>
</body>
</html>

You have now inserted data into the database. But there are still a lot of good work to do. What if the user doesn't fill out a column? What to do if you fill in a number? Or fill in the wrong?

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 the 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 like this:

<html>
<body>
<?php
$db = mysql_connect ("localhost", "root");
mysql_select_db ("MyDB ", $db);
if ($id) {
//query 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 "id" Value= "<?php echo $myrow [" id "]?>"
Name: <input type= "Text" name= "i" value= "<?php echo
$myrow [" First "]?>" ><br>
Surname: <input type= "Text" Name= "Last" value= "<?php echo
$myrow ["]?> " ><br>
Addresses: <input type= "Text" name= "Address" value= "<?php echo
$myrow [" adress "]?>" >< BR
Position: <input type= "Text" name= "position" value= "<?php echo
$myrow [" position "]?>" ><br>
<input type= "Submit" name= "submit" value= "Input information"
</form>
<?php
} else {
//Show Employee List
$result = Mysql_Query ("SELECT * FROM Employees", $DB);
while ($myrow = Mysql_fetch_array ($result)) {
printf ("<a href="%s?id=%s ">%s%s</a><br>", $ Path_info,
$myrow [id], $myrow ["a"], $myrow ["last"]);
}

?
</body>
</html>

We have just written the contents of the field into the value attribute of the corresponding table element, which is the corresponding simplicity. 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.

<html>
<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 ";
} else {
//query 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 "id" Value= "<?php echo $myrow [" id "]?>"
Name: <input type= "Text" Name= "" The "" "Value=
Echo $myrow [" First "]?>" ><br>
Surname: <input type= "Text" Name= "Last" value= "<?php echo
$myrow ["]?> " ><br>
Addresses: <input type= "Text" name= "Address" value= "<?php echo
$myrow [" adress "]?>" >< 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 Employee list
$result = mysql_query ("SELECT * FROM Employees ", $DB);
while ($myrow = Mysql_fetch_array ($result)) {
printf ("<a href="%s?id=%s ">%s%s</a><br>", $ Path_info,
$myrow [id], $myrow ["a"], $myrow ["last"]);
}

?
</body>
</html>

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.

Vi. Complete Procedures

Before the end of this 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 program below.

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

}

//Send SQL command to database

$result = mysql_query ($sql);

echo "Record modification success!" <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 have not pressed the submit button, execute the following part of the program

if (! $id) {

//If not modified, displays the employee list $result = mysql_query ("SELECT * FROM Employees", $DB);

while ($myrow = Mysql_fetch_array ($result)) {

printf ("<a href="%s?id=%s ">%s%s</a>",

$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 some 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 ID for user edit

?>

<input type=hidden name= "id" value= "<?php echo $id?>" >

<?php

}

?>

Name: <input type= "Text" name= "i" value= "<?php echo $first?>" ><br>

Surname: <input type= "Text" Name= "Last" value= "<?php echo $last?>" ><br>

: <input type= "Text" name= "Address" 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>

This 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 not, we are increasing the record state, 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 know how to transfer information between different Web pages through URL and form input.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More