Chapter: Internet & World Wide Web HOW TO PROGRAM - Rich Internet Application Server Technologies - PHP

| Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail |

Connecting to a Database - PHP

Databases enable companies to enter the world of e-commerce by maintaining crucial data.

Connecting to a Database

 

Databases enable companies to enter the world of e-commerce by maintaining crucial data. Database connectivity allows system administrators to maintain and update such infor-mation as user accounts, passwords, credit card numbers, mailing lists and product inventories. PHP offers built-in support for many databases. In this example, we use MySQL. Install MySQL using the instructions in Sections 22.6–22.7. Then execute the Products script (refer to Section 22.8) from the Script Examples folder of the Chapter 23

examples directory at  www.deitel.com/books/iw3HTP4.

 

In this example, the client selects the name of a column in the database. The PHP script then executes—it builds a SELECT query, queries the database to obtain the column’s data and sends a record set in the form of XHTML to the client. Chapter 22 discusses how to build SQL queries.

 

Figure 23.14 is a web page that posts form data consisting of a selected database column to the server. The script in Fig. 23.15 processes the form data.

 

 

1          <?xml version = "1.0" encoding = "utf-8"?>

 

2          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

 

3          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

4         

5          <!-- Fig. 23.14: data.html -->

 

6          <!-- Form to query a MySQL database. -->

 

7          <html xmlns = "http://www.w3.org/1999/xhtml">

 

8          <head>

 

9          <title>Sample Database Query</title>

 

10        <style type = "text/css">

 

11        body  { background-color: #F0E68C }

 

12        h2 { font-family: arial, sans-serif;

 

 

13        color: blue }

14        input { background-color: blue;

15        color: yellow;

16        font-weight: bold }

17        </style>

 

18        </head>

 

19        <body>

 

20        <h2> Querying a MySQL database.</h2>

 

21        <form method = "post" action = "database.php">

 

22        <div>

 

                       

23        <p>Select a field to display:

24        <!-- add a select box containing options -->

25        <!-- for SELECT query -->

26                    <select name = "select">   

27                    <option selected = "selected">*</option>           

28                    <option>ID</option>           

29                    <option>Title</option>       

30                    <option>Category</option>          

31                    <option>ISBN</option>     

32                    </select></p>

33        <input type = "submit" value = "Send Query" />

 

34        </div>

 

35        </form>

 

36        </body>

 

37        </html>

 


Fig. 23.14 | Form to query a MySQL database

 

1          <?php print( '<?xml version = "1.0" encoding = "utf-8"?>' ) ?>

 

2          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

 

3          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

4

5          <!-- Fig. 23.15: database.php -->

 

6          <!-- Querying a database and displaying the results. -->

 

7          <html xmlns = "http://www.w3.org/1999/xhtml">

 

8          <head>

 

9          <title>Search Results</title>

 

10        <style type = "text/css">

 

11        body  { font-family: arial, sans-serif;

 

           

12                    background-color: #F0E68C }

13        table   { background-color: #ADD8E6 }

14        td         { padding-top: 2px;

15                    padding-bottom: 2px;

16                    padding-left: 4px;

17                    padding-right: 4px;

18                    border-width: 1px;

19                    border-style: inset }

20        </style>

 

21        </head>

 

22        <body>

 

23        <?php

 

24        extract( $_POST );

25       

26        // build SELECT query

 

27        $query = "SELECT " . $select . " FROM books";

28       

29        // Connect to MySQL

 

30        if ( !( $database = mysql_connect( "localhost",

 

"iw3htp4", "iw3htp4" ) ) )                

31                               

32        die( "Could not connect to database </body></html>" );          

33                               

34        // open Products database

 

35        if ( !mysql_select_db( "products", $database ) )

 

 

36        die( "Could not open products database </body></html>" );

37       

38        // query Products database

 

39        if ( !( $result = mysql_query( $query, $database ) ) )

40        {

41        print( "Could not execute query! <br />" );

 

42        die( mysql_error() . "</body></html>" );

 

43        } // end if

44

45        mysql_close( $database );

 

46        ?><!-- end PHP script -->

 

47        <h3>Search Results</h3>

 

48        <table>

 

49        <?php

 

                                   

50        // fetch each  record in result set

51        for ( $counter = 0; $row =    mysql_fetch_row( $result );           

52        $counter++    )                      

53        {                                  

54        // build table to display results

55        print( "<tr>" );

56       

57        foreach ( $row as $key => $value )

58        print( "<td>$value</td>" );

59       

60        print( "</tr>" );

61        } // end for

 

62        ?><!-- end PHP script -->

 

63        </table>

 

64        <br />Your search yielded <strong>

 

65        <?php print( "$counter" ) ?> results.<br /><br /></strong>

 

66        <h5>Please email comments to

 

67        <a href = "mailto:deitel@deitel.com">

68        Deitel and Associates, Inc.</a>

 

69        </h5>

 

70        </body>

 

71        </html>


 

Fig. 23.15 | Querying a database and displaying the results

 

Line 21 of Fig. 23.14 creates an XHTML form, specifying that the data submitted from the form will be sent to script database.php (Fig. 23.15) in a post request. Lines 26–32 add a select box to the form, set the name of the select box to select and set its default selection to *. This value specifies that all rows and columns are to be retrieved from the database. Each database column is set as an option in the select box.

 

Script database.php (Fig. 23.15) builds a SQL query with the specified field name and sends it to the database management system. Line 27 concatenates the posted field name to a SELECT query. Line 30 calls function mysql_connect to connect to the MySQL database. We pass three arguments to function mysql_connect—the server’s hostname, a username and a password (in this case, both our username and password are iw3htp4, based on the account we set up in Chapter 22). This function returns a database handle a representation of PHP’s connection to the database—which we assign to variable $data-base. If the connection to MySQL fails, the function returns false and function die is called to output an error message and terminate the script. Line 35 calls function mysql_select_db to specify the database to be queried (in this case, products), which returns true on success or false on failure. Function die is called if the database cannot be opened.

 

To query the database, line 39 calls function mysql_query, specifying the query string and the database to query. If the query fails, the function returns false. Function die is then called with a call to function mysql_error as an argument. Function mysql_error returns any error strings from the database. If the query succeeds, mysql_query returns a resource containing the query result, which we assign to variable $result. Once we have stored the data in $result, we call mysql_close in line 45 to close the connection to the database. Function mysql_query can also execute SQL statements such as INSERT or

DELETE that do not return results.

 

Lines 51–61 iterate through each record in the result set and construct an XHTML table from the results. The for statement’s condition calls function mysql_fetch_row to return an array containing the values for each column of the current row in the query result ($result). The array is stored in variable $row. Lines 57–58 construct individual cells for each column in the row. The foreach statement takes the name of the array ($row), iter-ates through each index value of the array ($key) and stores the value in variable $value. Each element of the array is then printed as an individual cell. When the result has no more rows, false is returned by function mysql_fetch_row, which terminates the for statement.

 

After all the rows in the result have been displayed, the table’s closing tag is written (line 63). The number of rows contained in $counter is printed in line 65. Alternatively, calling function mysql_num_rows( $result ) would return the number of rows in the result.


Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail


Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.