Project 7-1: Create a Web Interface to Accept and Display Information


As a basic introduction to PHP, this project will show you how to make a simple web interface to accept information from a user over the Internet and then display it on the web page dynamically. Once this has been accomplished, we will talk more in-depth about using PHP to interface with MySQL and expand on this project in both the following text and project.

Step by Step

  1. In a text editor, open a new file and type in the following code:

     <html> <body> <?php echo "<h1>Hello ".$_REQUEST['name']."</h1>"; ?> <h2>A Simple Example</h2> <form action="simple.php" method="post"> <label>Name <input name="name"/></label> </form> </body> </html> 
  2. Save the file as simple.php . Depending on your text editor, it may try to place another file extension ( .txt for instance) after .php . If this occurs, rename the file to remove the unwanted extension.

  3. Load the file onto your Internet server, using your normal means, and view the simple.php page with your web browser.

    Note  

    When you initially load this page, you will see a warning at its top, because the name variable has not been set.

  4. Type Georgia into the Name text box and press the ENTER key. The warning should disappear, and Hello Georgia should appear at the top of the page.

Project Summary

In Project 7-1, you wrote a PHP script and used it to display a dynamic web page on the Internet. You also submitted data over the Internet and placed it on the web page. This is a simple form of a PHP-generated, dynamic web page. In the following sections, you will use this experience as a stepping-stone to creating a web interface to access your MySQL database and display its contents on a web page.

Connecting to a Database

Assuming you are in an environment where PHP is already configured to support MySQL, the first step is to establish a communication link to the MySQL Server. You can use two functions for this:

  • mysql_connect() creates a new link to the server. This method automatically closes the connection when the PHP script is finished running.

  • mysql_pconnect() creates a persistent connection, which means that the function will look for a previous link and use it instead of creating a new one. This method does not automatically close the connection when the script ends; the connection remains open until you send a command to close it.

Both functions return a resource handler if the connection is a success, and a Boolean false otherwise . The resource handler should be stored as a variable for subsequent actions.

mysql_connect() has the following syntax (the optional arguments are enclosed in brackets [] ).

  link  = mysql_connect(  server  , [  username  ], [  password  ],    [  new  ], [  flags  ]) 

The server parameter is the name of the server where the MySQL database server is located. username and password are the user name and password to use when connecting, respectively. new is a Boolean parameter that forces a new link; that is, if your script calls mysql_connect multiple times, using a True value for new creates a new link each time. The flags parameter is one or more of the following constants: MYSQL_CLIENT_COMPRESS , MYSQL_CLIENT_ IGNORE_SPACE , and MYSQL_CLIENT_INTERACTIVE .

mysql_pconnect() takes the same arguments as mysql_connect() , except mysql_pconnect() does not have the new parameter.

Caution  

If you use the pconnect method, you will be responsible for housekeeping regarding connection links. This means your program needs to keep track of the connections and close them when they are no longer needed; otherwise, you could use up all of the available connections or open numerous , redundant links, thereby wasting resources.

Here is an example of connecting to a server running on Windows, using the user and password you set up in Module 1 of this book. Notice that this example uses the compression flag ( MYSQL_CLIENT_ COMPRESS ) for more efficient communication.

 <?php    $link = mysql_connect('localhost', 'root',       'pa55w0rd', false, MYSQL_CLIENT_COMPRESS);    if ($link === false)       {       echo 'Connection failure!';       }    else       {       echo 'Connection success!';       }?> 
Note  

The indentations and blank lines in the PHP code examples are used in accordance with style standards and are meant to make the code easier to read. However, they are not necessary to make the code function properly.

Checking Your Connection

Now that you are connected to a MySQL Server, you can verify that the server is running by using the ping function, mysql_ping() , which has the following syntax:

  boolean  = mysql_ping(  link  ) 

The link parameter is an integer that represents the query resource link (or connection) identifier.

Make sure the MySQL Server is running, and then execute the following script.

 <?php     $link = mysql_connect('localhost', 'root', '', false,                      MYSQL_CLIENT_COMPRESS);     $alive = mysql_ping();     if($alive) {         echo "It's Alive!\n";     } else {         echo "MySQL is not alive\n";     }?> 

You should see the It's Alive! text.

If you stop the MySQL Server and try this script, you ll see warnings like these (the warning lines have been truncated, but you get the idea):

 PHP Warning:  mysql_connect(): Can't connect to local MySQL ... PHP Warning:  mysql_ping(): Can't connect to local MySQL ... PHP Warning:  mysql_ping(): A link to the server could ... MySQL is not alive 

In PHP, if a MySQL function fails, it produces a warning. This information is very useful to a programmer, but not so useful to the end user. In fact, it actually may be dangerous, because it gives away information about the database. Having warnings such as these show up on a dynamic web page, in place of the data that would have displayed if there were no problems, places in public view information that might be used to compromise your database security. For this reason, you might want to suppress PHP warnings.

All PHP warnings can be suppressed using a configuration setting that specifies error levels; some can be suppressed on a perfunction basis by appending an at symbol ( @ ) to the beginning of the function name, as shown in this example:

 <?php    $link =  @  mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    $alive =  @  mysql_ping();    if($alive)       {       echo "It's Alive!\n";       }    else       {       echo "MySQL is not alive\n";       }?> 

When you run this code, you do not see the internal warnings; only the echo statements appear. Later in this module, you will learn about methods for dealing with PHP errors.

Listing Available Databases and Tables

Once you re connected to your MySQL Server, you can use a PHP command that is the equivalent of SHOW DATABASES in MySQL. The mysql_list_dbs() command allows you to retrieve the database names on your server. It has the following syntax:

  result  = mysql_list_dbs([  link  ]) 

The link parameter is an integer that represents the query resource link (or connection) identifier. This is optional. If it s not supplied, PHP will attempt to use the most recently created resource link.

This function returns a resource pointer to a result. You will learn how to use a resource pointer when we cover the mysql_fetch_row() function later in this module. The following code is an example using the mysql_list_dbs() command to list databases.

 <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    $result = mysql_list_dbs($link);    while ($row = mysql_fetch_array($result))       {       echo $row[0] . "\n";       }?> 

The "\n" newline character works if the script is typed on the command line. If you save this script into a file and run it through a browser, replace the "\n" with a "< br > " to output a newline character. When you run this script, it will produce the following results:

 Duckwear Mysql test 

You can also find your list of tables using a PHP command that is the equivalent to the MySQL SHOW TABLES command: mysql_list_tables() . This function, like the mysql_list_dbs() function, returns a resource pointer.

  result  = mysql_list_tables(  database  , [  link  ]) 

The database parameter is a string type that specifies the name of the database whose tables you want to list. The link parameter is the optional query resource link (or connection) identifier. If you don t supply a link, PHP will attempt to use the most recently created resource link.

The following example lists the tables of the mysql database.

 <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    $result = mysql_list_tables('mysql', $link);    while($array = mysql_fetch_row($result))       {       echo $array[0]."\n";       }?> 

The results of the previous script will be as follows :

 columns_priv db func host tables_priv user 

Selecting a Database

Now that you know which databases are available, you can set your default database. The equivalent of the MySQL USE DATABASE command in PHP is the mysql_select_db() function, which has the following syntax:

 mysql_select_db(  database  , [  link  ]) 

The database parameter is a string type that specifies the name of the database to use. The link parameter is the optional query resource link identifier. Again, if you don t supply this parameter, PHP will attempt to use the most recently created resource link.

You can still use the full name to a database field with the dot nomenclature (for example, mysql.user ) to access tables. However, if you use mysql_select_db() to set the default database, you can then refer to each table by its name only.

The following script sets the default database to duckwear .

 <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    mysql_select_db('duckwear', $link); ?> 

Issuing Queries

Now that you can list the table names and fields, you can issue any SQL query. The basic PHP command for issuing queries is mysql_query() . This command sends the SQL statement to the server, waits for a full result set, and returns to the script. It has the following syntax:

  result  = mysql_query(  sql  , [  link  ]) 

The sql parameter is a string type that is the SQL statement to send to the server. The link is the optional resource link. If is not supplied, PHP will attempt to use the most recently created resource link.

Note that since you are using the client library to send the statement to the server on a perfunction-call basis, you do not need to include the trailing semicolon in the SQL statement. This is the incorrect syntax:

 $sql = 'SELECT * FROM test;'; // incorrect 

And this is the correct syntax:

 $sql = 'SELECT * FROM test';  // correct 

The mysql_query() function will wait and store the entire results in memory before returning to the script. If you expect large results from your query, consider using the PHP function mysql_unbuffered_query() . This function has the same syntax as mysql_query() , but will stream the results back to the script as they come from the database.

The following are examples of issuing queries:

 <?php    $result = mysql_query('SELECT * FROM table_name');    $result = mysql_unbuffered_query('SELECT * FROM bigtable_name'); ?> 

The return of both functions is a link to a resource identifier, providing access to the results by using one of the methods described in the next section.

Dealing with Result Resources

You can use a host of methods to deal with the results of a query. If the query is not selecting data (that is, a command like DELETE , INSERT , UPDATE , and so on), you typically just verify that the command was successful.

 <?php    $result = mysql_query('DELETE FROM testing');    if ($result)       {       echo "Successfully deleted";       }    else       {L 20       echo "Deletion failed!";       } ?> 

For selection queries where you expect some results back, you typically use one of the mysql_fetch functions, which are described in Table 7-1.

Table 7-1: Variations of the PHP mysql_fetch Function

Fetch Function

Description

mysql_fetch_row

Fetch a single row as an anonymous array, following the column order.

mysql_fetch_array

Fetch the entire result set (multiple rows if applicable ), accessed as an anonymous array following the column order.

mysql_fetch_assoc

Fetch the result set as a hash array, following a key = value format, where the field name is the key .

mysql_fetch_object

Fetch the result set as a PHP object, where the variable is the field name, as in $result_row->fieldname = value.

Fetching a Row Use mysql_fetch_row() when you expect a single row of results. It has the following syntax:

  array  = mysql_fetch_row(  result  ) 

The result parameter is the resource link, which indicates where the result set can be found.

The following is an example of using mysql_fetch_row() .

 <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    mysql_select_db('mysql');    $sql = 'SELECT count(*) FROM user';    $result = mysql_query($sql, $link);    $array = mysql_fetch_row($result);    echo "There are ".$array[0]."\n"; ?> 

This script will return the number of database users, similar to this:

 There are 4 

Fetching Multiple Rows The other three mysql_fetch functions are useful when you expect a number of rows back. It is common to use these functions within a looping control structure, like a while loop.

Note  

It is possible to manually keep track of, and move, the result set pointer thereby looping around the results, but the mysql_fetch_array() , mysql_fetch_assoc() , and mysql_fetch_object() functions are a bit easier to use and subject to fewer human errors.

The mysql_fetch_array() function returns a row of results as an array, and then moves the pointer to the next row. If there is no row to return, the function returns false. It has the following syntax:

  array  = mysql_fetch_array(  result  , [  result type  ]) 

The result parameter is the result resource. The result type is an integer that determines what kind of array to return. The valid result types (constants) include the following:

  • MYSQL_ASSOC returns an associative array, where the keys are the field names.

  • MYSQL_NUM returns an anonymous array, where the key is an integer starting with 0 and incrementing to the total number of fields requested .

  • MYSQL_BOTH , the default, returns a hybrid.

An alternative to calling mysql_fetch_array() with the MYSQL_ASSOC flag is the more specific mysql_fetch_assoc() function. It behaves like mysql_fetch_row() and has the following syntax:

  array  = mysql_fetch_assoc(  result  ) 

The result parameter is the resource link, which indicates where the result set can be found.

To capture the results as PHP objects instead of an array, you can use the mysql_ fetch_ object() function. It returns the current row as an object, and then advances the data pointer to the next row. If there are no rows to return, this function will return false. It has the following syntax:

  object  = mysql_fetch_object(  result  ) 

The result parameter is the resource link, which indicates where the result set can be found.

The following is an example of using the various mysql_fetch functions. (The "\t" outputs a tab character.)

 <?php    $link = mysql_connect('localhost', 'root', '', false,       MYSQL_CLIENT_COMPRESS);    mysql_select_db('duckwear');    $sql = 'SELECT cust_last, cust_first, cust_city, cust_state       FROM duck_cust';    $result = mysql_query($sql, $link);    echo "Fetching a row using mysql_fetch_row:<br>";    $row = mysql_fetch_row($result);    echo "\t".$row[0];    echo "\t".$row[1];    echo "\t".$row[2];    echo "\t".$row[3];    echo "<br>"; // The next line clears out data from $row for the next example.    unset($row);    echo "\n\nFetching all the rows using mysql_fetch_array:<br>";    $result = mysql_query($sql, $link);    while ($row = mysql_fetch_array($result))       {       echo "\t".$row[0];       echo "\t".$row[1];       echo "\t".$row[2];       echo "\t".$row[3];       echo "<br>";       } // The next line clears out data from $row for the next example.    unset($row);    echo "<br>Fetching all the rows using mysql_fetch_assoc:<br>";    $result = mysql_query($sql, $link);    while ($row = mysql_fetch_assoc($result))       {       echo "\t".$row['cust_last'];       echo "\t".$row['cust_first'];       echo "\t".$row['cust_city'];       echo "\t".$row['cust_state'];       echo "<br>";       }    echo "<br>Fetching all the rows using mysql_fetch_object: <br>";    $result = mysql_query($sql, $link);    while ($row = mysql_fetch_object($result))       {       echo "\t".$row->cust_last;       echo "\t".$row->cust_first;       echo "\t".$row->cust_city;       echo "\t".$row->cust_state;       echo "<br>";       } ?> 
Note  

To include comments that are longer than one line, you can start the comment with /* on the first line and end it with */ several lines later, instead of placing // at the beginning of each line.

Figure 7-1 shows the results of the example. Notice that the first function returns only the first row, whereas the other three functions return all the rows in the table, due to the while loop.


Figure 7-1: Fetching results from queries



MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net