Creating a MasterDetail Page Set Using PHP | Mastering Dreamweaver MX Databases

Creating a Master/Detail Page Set Using PHP

As we mentioned, Dreamweaver MX does not provide the Master Detail Page Set Server Behavior when you create a website using PHP. However, Dreamweaver MX does provide the core Server Behaviors that compose a master/detail page set. Therefore, you can create a master/detail page set component by component. To see how to create a master/detail page set using PHP, follow these steps:

To begin, first activate your PHP website and

Open two new PHP files.
Save one file as Master.php, and save the other as Detail.php.
To establish a connection to our MySQL database, create a connection as we detailed in Chapter 10.
To create a recordset to hold the contents of the tblCategories table, choose Insert ® Application Objects ® Recordset to open the Recordset dialog box.
In the Name text box, enter RS_Category.
Select your data connection from the Connection list box, and select tblCategories from the Table list box, as shown in Figure 21.26.

Figure 21.26: The PHP recordset
Click OK to close the Recordset dialog box and add the code to your page.

The next step is to display the results of the recordset in our page. You can do so in several ways, but one of the easiest is to use the Dynamic Table Server Behavior. The Dynamic Table Server Behavior is a quick way to construct a Repeat Region of dynamic text based on the contents of our recordset. Follow these steps:

Position your cursor in the body of the master page.
Choose Insert ® Application Objects ® Dynamic Table to open the Dynamic Table dialog box, as shown in Figure 21.27.

Figure 21.27: The Dynamic Table dialog box
In the Recordset list box, select RS_Category.
In the Show section, click the All Records option.
The Border, Cell Padding, and Cell Spacing parameters affect only display properties, so set them as you will.
Click OK to close the dialog box and add the appropriate code to our Master.php page.

Listing 21.13 shows the code, and Figure 21.28 shows our browser result at this point. As you can see in the code and in the browser result, the page is displaying the proper records. However, there isn't a link to pass the CategoryID to our detail page. That's our next step.

Listing 21.13: DYNAMIC TABLE CODE IN MASTER.PHP

<?php require_once('Connections/MySQLConnection.php'); ?>  <?php  mysql_select_db($database_MySQLConnection, $MySQLConnection);  $query_RS_Category = "SELECT * FROM tblcategories";  $RS_Category = mysql_query($query_RS_Category, _  $MySQLConnection) or die(mysql_error()); $row_RS_Category = mysql_fetch_assoc($RS_Category);  $totalRows_RS_Category = mysql_num_rows($RS_Category); ?> <html> <head> <title>Untitled Document</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body> <table border="1">  <tr>     <td>categoryid</td>    <td>category</td>  </tr>  <?php do { ?>  <tr>     <td><?php echo $row_RS_Category['categoryid']; ?></td>    <td><?php echo $row_RS_Category['category']; ?></td>  </tr>    <?php } while ($row_RS_Category = mysql_fetch_assoc($RS_Category)); ?>   </table>     </body>  </html>  <?php  mysql_free_result ($RS_Category);  ?>

click to expand
Figure 21.28: The Dynamic Table browser result

To create a link to pass the CategoryID for each record in the Dynamic Table, follow these steps:

To copy the code that outputs the CategoryID value from our recordset, in Code view select and copy <?php echo $row_RS_Category['categoryid']; ?> from the left column of the table.
Select <?php echo $row_RS_Category['category']; ?> from the right table column.
Go to Windows ® Properties to activate the Properties panel.

In the Link text box, enter "detail.php/categorypara">Now PHP will add the appropriate URL parameter to each link it creates around a Category record as shown in Figure 21.29. Your code should now be identical to Listing 21.14. You can see the browser results in Figure 21.30.

Listing 21.14: MASTER.PHP

<?php require_once('Connections/MySQLconnection.php'); ?> <?php mysql_select_db($database_MySQLconnection, $MySQLconnection); $query_RS_Category = "SELECT * FROM tblcategories"; $RS_Category = mysql_query($query_RS_Category, $MySQLconnection) or die(mysql_error()); $row_RS_Category = mysql_fetch_assoc($RS_Category); $totalRows_RS_Category = mysql_num_rows($RS_Category); ?> <html> <head> <title>master.php</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <table border="1"> <tr> <td>categoryid</td> <td>category</td> </tr> <?php do { ?> <tr> <td><?php echo $row_RS_Category['categoryid']; ?></td> <td><a href="detail.php?categoryid=_ <?php echo $row_RS_Category['categoryid']; ?>">_ <?php echo $row_RS_Category['category']; ?></a></td> </tr> <?php } while ($row_RS_Category = mysql_fetch_assoc($RS_Category)); ?> </table> </body> </html> <?php mysql_free_ result($RS_Category); ?>

Figure 21.29: The browser results of Master.php

Figure 21.30: Create a filter for the CategoryID URL parameter.

Our final step is to create our detail page to output the books belonging to the passed CategoryID URL parameter. To do so, follow these steps:

Open Detail.php.

Choose Insert ® Application Objects ® Recordset to open the Recordset dialog box.

If you recall the previous examples, we will use a custom SQL statement to return the appropriate book titles. In addition, we use a filter to return only the book titles attached to the passed CategoryID.

To create the filter, name the recordset RS_Category, and choose our data connection from the Connection list box.

Choose tblCategories from the Table list box.

In the Filter section, choose CategoryID, =, and URL Parameter, and enter CategoryID in the text box, as shown in Figure 21.30.

Click the Advanced button to switch the Recordset dialog box to Advanced. Notice that Dreamweaver kindly keeps the filter we created.

To add our custom SQL statement, select from the beginning of the existing SQL statement to the equal sign (=).

Replace your selection with the following custom SQL statement.

SELECT tblBooks.BookID, tblBooks.Title FROM (tblBooks INNER JOIN tblBooksCategories ON tblBooks.BookID = tblBooksCategories.Book) INNER JOIN tblCategories ON tblBooksCategories.Category = tblCategories.CategoryID WHERE tblCategories.CategoryID =

Your SQL statement should now be the following:

SELECT tblBooks.BookID, tblBooks.Title FROM (tblBooks INNER JOIN tblBooksCategories ON tblBooks.BookID = tblBooksCategories.Book) INNER JOIN tblCategories ON tblBooksCategories.Category = tblCategories.CategoryID WHERE tblCategories.CategoryID = colname

Click the Test button to be certain the SQL is accurate.

Click OK to close the Recordset dialog box and return to Detail.php.

We're almost done. The final task is to output the recordset result to the screen. To do so, we can use the Dynamic Table Server Behavior again. Follow these steps:

Choose Insert ® Application Objects ® Dynamic Table to open the Dynamic Table dialog box, which is shown in Figure 21.31.

Figure 21.31: Create a filter for the CategoryID URL parameter

Select our recordset.

In the Show section, click the All Records option.

Click OK to close the Dynamic Table dialog box.

We're done! Now when a user clicks a category link on Master.php, Detail.php displays all book titles listed for that category. Listing 21.15 shows the final code for Detail.php, and Figure 21.32 shows the browser result.

Listing 21.15: DETAIL.PHP

<?php require_once('Connections/MySQLconnection.php'); ?> <?php $colname_RS_Category = "1"; if (isset($HTTP_GET_VARS['categoryid'])) { $colname_RS_Category = (get_magic_quotes_gpc()) ? _ $HTTP_GET_VARS['categoryid'] : _ addslashes($HTTP_GET_VARS['categoryid']); } mysql_select_db($database_MySQLconnection, $MySQLconnection); $query_RS_Category = sprintf("SELECT tblBooks.BookID, _ tblBooks.Title FROM (tblBooks INNER JOIN tblBooksCategories _ ON tblBooks.BookID = tblBooksCategories.Book) _ INNER JOIN tblCategories ON _ tblBooksCategories.Category = tblCategories.CategoryID _ WHERE tblCategories.CategoryID = %s", $colname_RS_Category); $RS_Category = mysql_query($query_RS_Category, $MySQLconnection) _ or die(mysql_error()); $row_RS_Category = mysql_fetch_assoc($RS_Category); $totalRows_RS_Category = mysql_num_rows($RS_Category); ?> <html> <head> <title>detail.php</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <table border="1"> <tr> <td>BookID</td> <td>Title</td> </tr> <?php do { ?> <tr> <td><?php echo $row_RS_Category['BookID']; ?></td> <td><?php echo $row_RS_Category['Title']; ?></td> </tr> <?php } while ($row_RS_Category = mysql_fetch_assoc($RS_Category)); ?> </table> </body> </html> <?php mysql_free_result($RS_Category); ?>

Figure 21.32: The browser result for Detail.php

For More Information
To learn more about Master/Detail Page Sets, consult the Macromedia knowledge base at www.macromedia.com and the Dreamweaver help documentation. To get an inside perspective on how your peers are using Master/Detail Page Sets, browse the Macromedia Forums at http://webforums.macromedia.com/dreamweaver.