Displaying Results with Embedded Links

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 Figure, let's display the list of titles with hyperlinks to purchase the titles.

Using embedded links to provide a purchase button in pear_purchase_example.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>";

In Figure, 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 Figure. 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.

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 Figure.

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
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>";
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 shows the purchase record we created in Figure, plus the newly created entry from Figure.

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.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows