mysql| Data | Database summary
In this chapter we will learn how to store information in a Web page and display it in a database.
(2002-08-29-14:11:25)
--------------------------------------------------------------------------------
By Wing, Origin: Linuxaid
Fourth chapter: Using PHP to access the MySQL database
In this chapter we will learn how to store information in a Web page and display it in a database. We have previously installed the MySQL relational database engine and PHP server-side scripting language, and learned about their basics. After completing this chapter, we will understand how to use these two new tools to build a database-driven Web site!
A review of the previous section
Before we go any further, it would be worthwhile to review the purpose of our study. There are now two powerful new tools in our system: scripting language PHP and database engine MySQL. Figuring out the two is important if you work together.
The essence of a database-driven Web site is to allow the content of the site to exist in a database, and it is possible to dynamically generate Web pages from this database to allow our visitors to display it through a standard Web browser. So at one end of your system is a visitor to your site who accesses the http://www.yoursite.com/to get a standard HTML-formatted Web page and display it in a Web browser. At the other end of your system is the content of your site stored in a MySQL database with one or several datasheets that only understands how to respond to SQL queries (commands).
PHP scripting language assumes the role of a liaison between the two, using PHP, you can write a standard HTML "template", this "template" determines the appearance of your site (including drawings and page design). The content is this "template", you can use some PHP code to connect to the MySQL database and use SQL query to get the data and display it in its corresponding location, where the SQL query is the same as we used in the second chapter to build jokes datasheet.
Now you should have a clear understanding of what happens when a visitor accesses a page in your database-driven Web site:
The visitor's Web browser uses a standard URL to request this page.
The Web server Software (Apache, IIS, or others) determines that the requested page is a PHP script and therefore interprets it using its PHP plugin before responding to the page request.
Some of the PHP commands (which we haven't learned yet) will connect to the MySQL database and ask the database for content that belongs to the Web page.
The MySQL database responds and sends the requested content to the PHP script.
The PHP script stores the content in one or several PHP variables and uses our familiar echo function to output it as part of a Web page.
The PHP plug-in completes processing and returns the generated HTML copy to the Web server.
The Web server sends this HTML copy to the Web browser, which will be a standard HTML file, except that it comes directly from an HTML file, but from the output provided by the PHP plug-in.
Connect MySQL with PHP
Before we get to the contents of our web page from our MySQL database, we must first know how to establish a connection with MySQL. In chapter two, we use a program called MySQL to make this connection. PHP does not need such a program, the support of the connection MySQL is language built. The following function is used to establish such a connection:
Mysql_connect (<ADDRESS>, <username>, <password>);
Here,<address> is the IP address or hostname of the computer on which the MySQL service software runs (if this is the same as the computer running the Web service software, you can use "localhost"),<username> and < Password> is the username and password you used to connect to the MySQL server in Chapter two.
You may recall that functions in PHP often return (output) a value when invoked. Please do not worry that we are not reminding you that we will detail it for you when we first touch a function. Most functions, after being invoked, return a value that can be stored in a variable for the next use. For example, the Mysql_connect function we described above will return a number to identify the connection that has been established. Because we're going to use this connection, so we have to save this value. Here is an example of how to connect to our MySQL database:
$DBCNX = mysql_connect ("localhost", "root", "mypasswd");
Note that for your MySQL server, the values of the three parameters in the above function may be different. You should notice here that our mysql_connect returns a value (which we call a connection identifier) that we store in the variable $DBCNX.
Because MySQL is a fully distributed software, we have to take into account the possibility that the service is not available, that the network is blocked, or that our username and password do not match. In these cases, the Mysql_connect function cannot return a connection identity (because the connection was not established). At this point, a logical leave is returned. This allows us to use an if statement to handle the connection: $DBCNX = @mysql_connect ("localhost", "root", "mypasswd");
if (! $dbcnx) {
Echo ("<p>unable to connect to".)
"Database server at this time.</p>");
Exit ();
}
There are three new things in the code snippet above, first of all, we add a @ symbol before the mysql_connect function. Many functions, including mysql_connect, can display unsightly error messages after a failure. Adding an @ symbol in front of the function name tells the function to allow us to display our own friendly error message when execution fails.
Second, in the condition of our if statement, the $DBCNX variable is preceded by an exclamation point. This exclamation point is the "no operator" in PHP. In other words, logic is turned into logical false, and logical false to logical truth. Thus, if this connection fails, Mysql_connect returns a logical false,! $DBCNX will be equal to the logic true, so our if statement will be executed. Conversely, if the connection is successful, the connection ID stored in the $DBCNX will be equal to the logic true (in PHP, any Non-zero number is considered logical), so! $DBCNX equals logical false, and if statements will not be executed.
The last one is the Exit function, which is the first function we've encountered that has no parameters. The whole purpose of this function is to cause PHP to stop reading this page. This is a good response if the database connection fails, because in most cases the page does not display any useful information if it is not connected to the database.
As we have done in chapter two, the next step when a connection is established is to select the working database. We are going to work in the database of jokes set up in chapter two. This database is named jokes. Another function to select a database in PHP:
mysql_select_db ("jokes", $DBCNX);
Note that we use the $DBCNX variable here to indicate the connection identifier used by this function. This parameter is actually an ellipsis. When this argument is omitted, the function automatically uses the last connection that is opened. This function returns logic true after success, and returns logical leave after failure. For the sake of caution, we also used an if statement to handle the error:
if (! @mysql_select_db ("jokes")) {
Echo ("<p>unable to locate the joke").
"Database at this time.</p>");
Exit ();
}
Once the connection is established and the database is selected, we can start using the data stored in the database.
Executing SQL queries in PHP
In the second chapter, we use a program called MySQL to connect to the MySQL database server, in which we can enter the SQL query (command) and immediately display the results of the query. In PHP, there is a similar mechanism: the mysql_query function.
mysql_query (<query>, <connection id>);
Here,<query> is a string containing the SQL command that will be executed. As with mysql_select_db, the connection identification parameter is optional.
The return of this function depends on the type of query being emitted. For most SQL commands, mysql_query returns logical OR logical false to indicate whether the execution was successful. Please refer to the following example, which is used to build the jokes datasheet we created in Chapter two:
$sql = "CREATE TABLE Jokes (".
' ID INT not NULL auto_increment PRIMARY KEY, '.
"Joketext TEXT,".
' Jokedate DATE not NULL '.
")";
if (mysql_query ($sql)) {
Echo ("<p>jokes table successfully created!</p>");
} else {
Echo ("<p>error Creating Jokes Table:".)
Mysql_error (). "</P>");
}
The mysql_error used here will return the last error message from the MySQL server in the form of a string.
For delete, insert, and update (used to modify stored data), MySQL can know how many rows of data are affected by this query. See the following SQL command, which we used in chapter two to set the date for all jokes containing the word "chicken":
$sql = "UPDATE jokes SET jokedate= ' 1990-04-01 '".
"WHERE joketext like '%chicken% '";
When we execute this query, we can use the Mysql_affected_rows function to display the number of data rows affected by the modification:
if (mysql_query ($sql)) {
Echo ("<p>update affected").
Mysql_affected_rows (). "Rows.</p>");
} else {
Echo ("<p>error Performing Update:".)
Mysql_error (). "</P>");
}
The Select command is somewhat different because it gets a lot of information, and PHP must provide a way to process that information.
Working with Select result Sets
For most SQL queries, the mysql_query function simply returns logical TRUE or logical false. For a select query, this is simply not enough. You should remember that the select query is used to display data stored in the database. In addition to pointing out whether the query succeeds or fails, PHP must also get the results of the query. As a result, when we execute a select query, Mysql_query returns a number that identifies the result set, which contains a list of all the rows returned by the query. If the query fails, the function still returns a logical leave.
$result = mysql_query ("Select Joketext from Jokes");
if (! $result) {
Echo ("<p>error performing Query:".)
Mysql_error (). "</P>");
Exit ();
}
Assuming that no errors are encountered while executing the query, the above code locates a result set for the body of all jokes stored in the joke library, which is stored in the variable $result. Because there is no limit to the number of jokes in the database, this result set can be very large.
We mentioned before that the while loop is a very useful control statement for processing a large amount of data, which is the basic format of the code that handles the data rows in the result set individually:
while ($row = Mysql_fetch_array ($result)) {
Process the row ...
}
The conditions in this while loop may seem different from what we used to do, so it's important to explain how it works here. You can first consider this condition as a separate statement:
$row = Mysql_fetch_array ($result);
The Mysql_fetch_array function takes an argument (stored in the $result variable for this example) to a result set and returns the next row in the result set as a data. If you are not familiar with the concept of arrays, don't worry, we'll discuss it in detail below. If there are no more rows of data in this result set, Mysql_fetch_array returns logical leave.
Now, the statement above defines a value into the $row variable, while the entire statement gets the same value. This is why we use this statement in the condition of the while loop, because the while loop executes the loop until the condition equals logical false, the result set has several rows, the loop executes several times, and each time the $row gets a value for the next line, and now the rest is how to loop through the $ Get the corresponding value in the row variable.
The rows in the result set are described as an array. An array is a special type of variable that can contain multiple values, and if you think of a variable as a container of values, you can think of an array as a container with an interval, in which a single value can be stored at each interval. For our data rows, this interval is named after the column name of the datasheet. If $row is a row in our result set, then $row["Joketext" is the value of the Joketext column in this row. So if we want to show the body of the joke in our database, the while loop should be like this:
while ($row = Mysql_fetch_array ($result)) {
Echo ("<P>" $row ["Joketext"]. "</P>");
}
Finally, as a summary, this is the code for a complete PHP Web page that connects our database, takes out the body of all the jokes in the database, and displays it in HTML:
<HTML>
<HEAD>
<TITLE> our List of jokes </TITLE>
</HEAD>
<BODY>
<?php
Connect to the database server
$DBCNX = @mysql_connect ("localhost",
"Root", "mypasswd");
if (! $dbcnx) {
Echo ("<p>unable to connect to".)
"Database server at this time.</p>");
Exit ();
}
Select the Jokes database
if (! @mysql_select_db ("jokes")) {
Echo ("<p>unable to locate the joke").
"Database at this time.</p>");
Exit ();
}
?>
<P> Here are all the jokes in our database: </P>
<BLOCKQUOTE>
<?php
Request the text of the all jokes
$result = mysql_query (
"Select Joketext from Jokes");
if (! $result) {
Echo ("<p>error performing Query:".)
Mysql_error (). "</P>");
Exit ();
}
Display the text of each joke in a paragraph
while ($row = Mysql_fetch_array ($result)) {
Echo ("<P>" $row ["Joketext"]. "</P>");
}
?>
</BLOCKQUOTE>
</BODY>
</HTML>
Inserting data into the database
In this section, we'll see how we can use these tools to get visitors from our site to add their own jokes to the database. If you like the challenge, try to figure out what to do roughly before you look down. There are only a few new things in this section. It's just a simple application for what we've learned.
If we want visitors to be able to enter new jokes, we need a form first, here's the code for this form:
<form action= "<?php Echo ($PHP _self);?>" method=post>
<p>type your joke here:<br>
<textarea name= "Joketext" rows=10 cols=40 wrap></textarea><br>
<input type=submit name= "Submitjoke" value= "SUBMIT" >
</FORM>
As we have seen above, this form will load the same page at the time of submission (because we use the $php_self variable in the form's action attribute), but the request contains two variables in the second load, first $joketext, This is the text of the joke entered in the text field, the other is $submitjoke, the value of this variable will always be "submit", which is used to mark the joke has been submitted.
To add a committed joke to the database, we need to run an insert query with mysql_query, which will contain the value of the $joketext variable that has been committed:
if ("SUBMIT" = = $submitjoke) {
$sql = "INSERT into Jokes SET".
"Joketext= ' $joketext ',".
"Jokedate=curdate ()";
if (mysql_query ($sql)) {
Echo ("<p>your joke has been added.</p>");
} else {
Echo ("<p>error adding submitted joke:".
Mysql_error (). "</P>");
}
}
In all of the content there is only one new thing in the SQL code. Here we use a MySQL function curdate () to place the value of the Jokedate column of the newly inserted database joke into the current date. In fact, MySQL has many of these functions, but we will only introduce them when we use them, and to get a complete description of the function, you can refer to the MySQL reference manual.
