Section 12.3. Displaying Results with Embedded Links


12.3. Displaying Results with Embedded Links

You may want to give your web user the ability to click on a hyperlink to launch an action that relates to the current row in the results from a query. You do this by adding URL links to the results of a query when they display on the screen. The links contain a unique identifier to the row and the script that handles the action.

The PHP script that is the target of the link typically queries the database based on the unique identifier that was passed to it. The types of action you can do range fromadding or deleting a row to expanding on details from a related table, such as authors for book titles.

In Example 12-4, let's display the list of titles with hyperlinks to purchase the titles.

Example 12-4. Using embedded links to provide a purchase button in pear_purchase_example.php

 <?php require_once('db_login.php'); require_once('DB.php'); $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database"); if (DB::isError($connection)){ die ("Could not connect to the database: <br />". DB::errorMessage($connection)); } $query = "SELECT * FROM `books`"; $result = $connection->query($query); if (DB::isError($result)){ die("Could not query the database: <br />". $query." ".DB::errorMessage($result)); } echo '<table border="1">'; echo "<tr><th>Title</th><th>Pages</th><th>Buy</th></tr>"; while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { echo "<tr><td>"; echo $result_row["title"] . '</td><td>'; echo $result_row["pages"] . '</td><td>'; echo '<a href="purchase.php?title_id='.$result_row["title_id"].'">Click to purchase</a></td></tr>'; } echo "</table>"; $connection->disconnect(); ?> 

In Example 12-4, you modify the format of the last bolded table cell to build a hyperlink for purchasing the book. The target of that link is the file purchase.php, which is defined in Example 12-5. You send it a parameter called title_id, which is the primary key from the titles table. This unique ID specifies which book the user wants to purchase, and is used as a link in the table shown in Figure 12-4.

Figure 12-4. Users can click the purchase link to add the purchase to the purchases table


Next, you'll define the script that handles the purchase action in Example 12-5.

Example 12-5. The file purchase.php processes the user action based on the title_id parameter

 1 <?php 2 require_once('db_login.php'); 3 require_once('DB.php'); 4 $connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database"); 5 if (DB::isError($connection)){ 6 die ("Could not connect to the database: <br />". DB::errorMessage($connection)); 7 } 8 $title_id = $_GET["title_id"]; 9 $user_id = 'mdavis'; 10 $query = "INSERT INTO `purchases` VALUES (NULL,'$user_id',$title_id,NULL)"; 11 $result = $connection->query($query); 12 if (DB::isError($result)){ 13 die("Could not query the database: <br />". $query." ".DB::errorMessage($result)); 14 } 15 ?> 16 <html> 17 <head> 18 <title>Thanks for your purchase!</title> 19 <meta http-equiv="refresh" content="4; url=pear_purchase_example.php"> 20 </head> 21 <body> 22 Thanks for your purchase!<br /> 23 <?php 24 25 $query = "SELECT * FROM purchases NATURAL JOIN books NATURAL JOIN authors"; 26 $result = $connection->query($query); 27 if (DB::isError($result)){ 28 die("Could not query the database: <br />". $query." ".DB::errorMessage($result)); 29 } 30 echo '<table border="1">'; 31 echo "<tr><th>User</th><th>Title</th><th>Pages</th>"; 32 echo "<th>Author</th><th>Purchased</th></tr>"; 33 while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { 34 echo "<tr><td>"; 35 echo $result_row["user_id"] . '</td><td>'; 36 echo $result_row["title"] . '</td><td>'; 37 echo $result_row["pages"] . '</td><td>'; 38 echo $result_row["author"] . "</td><td>"; 39 echo $result_row["purchased"] . "</td></tr>"; 40 } 41 echo "</table>"; 42 43 $connection->disconnect(); 44 ?> 45 </body> 46 </html> 

Since this example is fairly lengthy, we'll discuss the major additions on a line-by-line basis.

  • Line 8 takes the parameter from the calling script and assigns it to a local variable called $title_id, which we'll reference in the insert statement.

  • Line 9 sets a $user_id variable to mdavis. Ideally, the username wouldn't be hardcoded. In the next chapter, you'll learn about logging users into a session that holds their identity.

  • Line 10 sets up the query with the INSERT statement using the user-supplied values.

  • Line 19 uses a META tag to redirect users back to the page from which they came after briefly displaying a message that their purchases (that you processed as an INSERT to the database) were successful. The syntax for redirecting to another page after a delay is:

     <meta http-equiv="refresh" content="seconds_before_refreshing; url=url_to_redirect_to"> 

  • The META statement should be placed in the <head> section of the HTML.

  • Line 25 defines a new query to select all purchases. Subsequent lines display the results in an HTML table.

The end result is that a new purchase is added to the purchases table, and the user briefly sees the contents of the purchases table before returning to the previous page.

Figure 12-5 shows the purchase record we created in Example 12-3, plus the newly created entry from Example 12-4.

Figure 12-5. After clicking "Click to purchase" for "Linux in a Nutshell"


With the click of a link, you can add customized data to your table. Let's integrate form submission and insert data.



Learning PHP and MySQL
Learning PHP and MySQL
ISBN: 0596101104
EAN: 2147483647
Year: N/A
Pages: 135

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