Home > Articles > Data > MySQL

Creating an Online Storefront in PHP, MySQL and Apache, Part 3

  • Print
  • + Share This
In Part 3 of this chapter, you’ll learn the design process behind a simple discussion forum; specifically, displaying the posts in a topic.
Read part 1 and part 2 of this chapter series. If you're interested in seeing more projects like this, you can purchase Sams Teach Yourself PHP, MySQL and Apache All in One.
This chapter is from the book

Displaying the Posts in a Topic

As you might have guessed, the next item on the task list is to build that showtopic.php file to show the topic’s postings. Listing 21.5 does just that. In this listing, lines 3–6 check for the existence of a value for topic_id in the GET query string. Because you intend to show all the posts within a selected topic, you need to know which topic to use in your query, and this is the manner in which the information is given to you. If a value in $_GET["topic_id"] does not exist, the user is redirected back to the topic listing page, presumably to try again.

Listing 21.5 Script to Show Topic Posts

1: <?php
2: include("ch21_include.php");
3: doDB();
4: 
5: //check for required info from the query string
6: if (!isset($_GET["topic_id"])) { 
7:   header("Location: topiclist.php");
8:   exit;
9: } 
10: 
11: //verify the topic exists
12: $verify_topic_sql = "SELECT topic_title FROM forum_topics 
13:           WHERE topic_id = ‘".$_GET["topic_id"]."’";
14: $verify_topic_res = mysqli_query($mysqli, $verify_topic_sql) 
15:           or die(mysqli_error($mysqli));
16:
17: if (mysqli_num_rows($verify_topic_res) < 1) { 
18:  //this topic does not exist
19:  $display_block = "<p><em>You have selected an invalid topic.<br/>
20:  Please <a href=\ "topiclist.php\ ">try again</a>.</em></p>";
21: } else { 
22:  //get the topic title
23:  while ($topic_info = mysqli_fetch_array($verify_topic_res)) { 
24:    $topic_title = stripslashes($topic_info[‘topic_title’]);
25:  } 
26:
27:  //gather the posts
28:  $get_posts_sql = "SELECT post_id,post_text, DATE_FORMAT(post_create_time,
29:           ‘%b %e %Y at %r’) AS fmt_post_create_time, post_owner 
30:           FROM forum_posts 
31:           WHERE topic_id = ‘".$_GET["topic_id"]."’ 
32:           ORDER BY post_create_time ASC";
33:  $get_posts_res = mysqli_query($mysqli, $get_posts_sql) 
34:           or die(mysqli_error($mysqli));
35:
36:   //create the display string
37:   $display_block = "
38:   <p>Showing posts for the <strong>".$topic_title."</strong> topic:</p>
39:   <table width=\ "100%\ " cellpadding=\ "3\ " cellspacing=\ "1\ " border=\ "1\ ">
40:   <tr>
41:   <th>AUTHOR</th>
42:   <th>POST</th>
43:   </tr>";
44: 
45:   while ($posts_info = mysqli_fetch_array($get_posts_res)) { 
46:     $post_id = $posts_info[‘post_id’];
47:     $post_text = nl2br(stripslashes($posts_info[‘post_text’]));
48:     $post_create_time = $posts_info[‘fmt_post_create_time’];
49:     $post_owner = stripslashes($posts_info[‘post_owner’]);
50: 
51:     //add to display
52:     $display_block .= "
53:     <tr>
54:     <td width=\ "35%\ " valign=\ "top\ ">".$post_owner."<br/>
55:     [".$post_create_time."]</td>
56:     <td width=\ "65%\ " valign=\ "top\ ">".$post_text."<br/><br/>
57:     <a href=\ "replytopost.php?post_id=".$post_id."\ ">
58:     <strong>REPLY TO POST</strong></a></td>
59:     </tr>";
60:   } 
61:
62:   //free results
63:   mysqli_free_result($get_posts_res);
64:   mysqli_free_result($verify_topic_res);
65:
66:   //close connection to MySQL
67:   mysqli_close($mysqli);
68:
69:   //close up the table
70:   $display_block .= "</table>";
71: } 
72: ?>
73: <html>
74: <head>
75: <title>Posts in Topic</title>
76: </head>
77: <body>
78: <h1>Posts in Topic</h1>
79: <?php echo $display_block; ?>
80: </body>
81: </html>

Lines 12–15 show the first of these queries, and this one is used to validate that the topic_id sent in the query string is actually a valid entry by selecting the associated topic_title for the topic in question. If the validation fails the test in line 17, a message is created in lines 19–20, and the script breaks out of the if...else statement and finishes up by printing HTML. This output looks like Figure 21.5.

Figure 21.5

Figure 21.5 Invalid topic selected.

If, however, the topic is valid, extract the value of topic_title in line 24, again using stripslashes() to remove any escape characters. Next, the script issues a query in lines 28–34 to gather all the posts associated with that topic in ascending order by time. In this case, newest posts are at the bottom of the list. Line 37 starts a block of text, containing the beginnings of an HTML table. Lines 38–43 set up a table with two columns: one for the author of the post and one for the post text itself. The script stops writing the text block momentarily, and at line 45 begins to loop through the results of the original query.

The while loop in line 45 says that although there are elements to be extracted from the resultset, extract each row as an array called $posts_info, and use the field names as the array element to assign the value to a new variable. So, the first element the script tries to extract is the post_id field on line 46. It assigns the value of $posts_info[‘post_id’] to the variable $post_id, meaning that it gets a local value for $post_id from an array called $posts_info, containing a field called post_id. Continue doing this for the $post_text, $post_create_time, and $post_owner variables in lines 47–49. The stripslashes() function is again used to remove any escape characters, and the nl2br() function is used on the value of $posts_info[‘post_text’] to replace all newline characters with XHTML-compliant line break characters.

In line 52, the script continues to write to the $display_block string, using the concatenation operator (.=) to make sure that this string is tacked on to the end of the string we have created so far. Line 54 creates the HTML table column to display the author and creation time of the post. The second HTML table row, on line 56, shows the text of the post as well as a link to reply to the post. The script breaks out of the while loop on line 60, and on line 70 adds the last bit to the $display_block string to close the table. The remaining lines print the HTML for the page, including the value of the $display_block string.

If you save this file as showtopic.php and place it in your web server document root, and if you have posts in your database tables, you might see something like Figure 21.6.

Figure 21.6

Figure 21.6 Posts in a topic.

A one-post topic is boring, so let’s finish up this chapter by creating the script to add a post to a topic.

  • + Share This
  • 🔖 Save To Your Account