indigetal

Validate the submitted email address in forgot_password.php using the Filter extension or a regular expression. It’s been a while since I’ve worked on this, but I’m glad I left it at this example, as it’s very easy to implement. Really, all I’m doing is taking the example from post #3 and applying the portion that relates to the email input. $_POST['email'] is already run through the mysqli_real_escape_string() function, which makes data safe to use in a query by escaping what could be problematic characters, within the db query itself, so this seems to be all that is needed: if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {

If you’ve added the last_login field, set it to print a message on the home page as to how many users have logged in in the past say, hour or day. After validating that the main db query ran successfully, run the db query to retrieve the necessary data: $q = "SELECT COUNT(user_id) AS num_login FROM users WHERE last_login > DATE_SUB(NOW(), INTERVAL 60 MINUTE)"; $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br>MySQL Error: " . mysqli_error($dbc)); The DATE_SUB() function subtracts an amount of days, months, years, hours, minutes and seconds from a DateTime object. The first parameter is a DateTime object and the second parameter is a DateInterval object, here 60 minutes. So we are counting all user_id's whose last_login value is greater than the span of time over the last 60 minutes. This is given the alias of num_login so we know what the data refers to. Next, we fetch the results as an associative array: if (@mysqli_num_rows($r) == 1) { $row = mysqli_fetch_array($r, MYSQLI_ASSOC); } else { echo '<p class="error">Oops, A system error occurred!</p>'; } Finally, display the results in index.php: echo "<h5>There have been " . $row['num_login'] . " users logged-in, in the last hour.</h5>";

Add a last_login DATETIME field to the users table and update its value when a user logs in. Use this info to indicate to the user how long it has been since the last time she or he accessed the site. Thank you everybody for all of your help with the last task. I've found that this next one was a bit over my head also, as my first attempt ended up being way off. However, having searched the forum I've found several previous threads that I was able to use as a reference, particularly this one (Thank you Christopher for your work). So my work here is going to largely be a detailed walk-through of others work. I have to learn this stuff somehow! The first thing that I'll do is create the last_login column in the users table of the database: ALTER TABLE users ADD COLUMN last_login DATETIME NOT NULL; Next, I want to retrieve the last_login date from the users table within login.php in the main database query that logs the user in. However, I want to retrieve the last_login data as both a formatted date to display and an unformatted date for performing further calculations: $q = "SELECT user_id, first_name, user_level, DATE_FORMAT(last_login, '%a, %b %e at %l:%i%p') as formatted_login, last_login FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; The DATE_FORMAT() function formats the date and time as: Sat, Feb. 21 at 3:42AM, as an example (see Ullman 162). The returned rows are already being registered to the session as an associative array and can be accessed as $_SESSION['last_login']. After doing that I need to update last_login, but only after validating that the main db query ran successfully, so within if (@mysqli_num_rows($r) == 1) { I do so with the following: $q = "UPDATE users SET last_login=UTC_TIMESTAMP()"; $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br>MySQL Error: " . mysqli_error($dbc)); if (@mysqli_num_rows($r) != 1) { echo '<p class="error">Oops! Something went wrong.</p>'; } I'm choosing to use UTC_TIMESTAMP here over NOW(), but what's the difference between NOW() and UTC_TIMESTAMP()? There is a handy table on page 159 differentiating the various Date and Time functions. The UTC_TIMESTAMP() function, for instance, returns "the number of seconds since the epoch until the current moment or until the date specified, in Coordinated Universal Time (UTC) and the NOW() function returns the current data and time (on the server) (Ullman 159). On page 189, Ullman explains that the NOW() function reflects the date and time on the server, which doesn't account for timezones. Storing dates and times using the UTC_* functions store date and times in a time-zone neutral way. Therefore, I'll prefer UTC_TIMESTAMP() over NOW(). In order to print the message when the user logs-in, I go into index.php (where users are redirected when logging-in) and begin a conditional to test if the last_login date was stored in the session. //If the user is logged-in: if (isset ($_SESSION['last_login'])) { Next, I want to begin performing the calculations for the number of days, hours, minutes and seconds since the user last logged in. But first, I want to store the current time: $date1 = time(); The php manual says the time() function returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT). After doing that, I get the last_login value stored in the database using the $_SESSION[]. The strtotime() function parses about any English textual datetime description into a Unix timestamp: $date2 = strtotime($_SESSION['last_login']); Now I can find out the span of time that has passed between the two dates: $dateDiff = $date1 - $date2; And break it down in order to determine the number of days, hours, minutes, and seconds that have passed. floor() will round any fractions down so only whole numbers are returned: $fullDays = floor($dateDiff/(60*60*24)); $fullHours = floor(($dateDiff - ($fullDays*60*60*24))/(60*60)); $fullMinutes = floor(($dateDiff-($fullDays*60*60*24)-($fullHours*60*60))/60); Since the time() function is already set in seconds, I need to determine how many seconds are remaining after taking into account the days, hours, and minutes. To do this I determine how many seconds that consists of and then find the remainder of seconds left over: $sec_used = ($fullDays * 60 * 60 * 24) + ($fullHours * 60 * 60) + ($fullMinutes * 60); $sec_rem = $dateDiff - $sec_used; Finally, I print the messages displaying the date the user last logged-in (which was the formatted last_login value stored as the alias, formatted_login) and the time that has passed since the last login: echo "<h4>The last time you logged in is: " . $_SESSION['formatted_login'] . "</h4>"; echo "<h4>There have been $fullDays days, $fullHours hours $fullMinutes minutes and $sec_rem seconds since you last logged in.</h4>"; NOTE: This code had a known issue with $fullDays resulting in a negative number. Mr. Ullman mentioned something about storing the last_login as a timestamp so that we don't have to convert it to a timestamp in PHP. I've replaced NOW() with UTC_TIMESTAMP() in the update query and seem to have resolved the issue, but I'm not sure that's exactly what Mr. Ullman meant.

Hi abigail, Removing first_name from the database query results in the same error message, except for the last_name column. This tells me that it's not the column, but either the table or database. I believe its most likely something to do with the table since the email and pass columns are requested before the first_name and last_name columns and they aren't creating any error messages. Following your advice, I created another table, that I named 'registered,' using the exact same code used to create the users table and ran the script in the browser. What occurred was very interesting, it threw the error message: MySQL Error: Table 'forum2.registered' doesn't exist What's so interesting about that is, in the mysqli_connect.php file that I thought was being used for this website the database is userRegistration, not forum2! So, I went back to my sites folder and discovered this -freakin'- site is using the wrong mysqli_connect.php. I know the config.inc.php file establishes the location of the mysqli_connect.php file to use, and it currently is: DEFINE('MYSQL', '../../mysqli_connect.php'); I had thought that "../.." meant that the file resided two folders up from config.inc.php, which it does, but it's actually directed at the mysqli_connect.php that is 3 folders up from config.inc.php. Ok, so I changed the path to the mysqli_connect.php file to: DEFINE('MYSQL', '../mysqli_connect.php'); Boom! That was the problem... (What a putz)

Thank you Antonio very much for responding. I have now confirmed beyond any doubt that it is in fact a database issue, but even after narrowing that down I've run out of ideas. I hope there is a great lesson in my troubles with this (and hopefully not a completely ridiculous noob mistake), but so far, I have established the following: The database query fails with the exact same error message regardless if I use the code that I have written or if I use the downloaded code, (only changing the database access information). The error that I'm getting is:An error occured in script '/Users/indigital03/Sites/user_registration/htdocs/login.php' on line 30: Query: SELECT user_id, first_name, user_level FROM users WHERE (email='myemail@yahoo.com' AND pass=SHA1('test')) AND active IS NULL MySQL Error: Unknown column 'first_name' in 'field list' The database is named userRegistration, the table at issue is named users and the first_name column is of data type varchar(20) and is set to NOT NULL. The entire SQL code that created the database was written in response #6 and a snapshot of the table's structure in phpMyAdmin can be viewed here. In the file mysqli_connect.php, the constants that establish the database connection are as follows: //Set the MySQL host, username, password, and db name as constants P268 DEFINE ('DB_HOST', 'localhost'); DEFINE ('DB_USER', '*******'); DEFINE ('DB_PASSWORD', '*******'); DEFINE ('DB_NAME', 'userRegistration'); The constant established in config.inc.php that sets up site-wide access to the database is: //establish two constants for site-wide settings: DEFINE('BASE_URL', 'http://localhost/'); DEFINE('MYSQL', '../../mysqli_connect.php'); The location of mysqli_connect.php is: Sites>user_registration>mysqli_connect.php The location of config.inc.php is Sites>user_registration>htdocs>includes>config.inc.php Hopefully there is something here that a more experienced professional will be able to see plain-as-day. I really want to move forward with this example, but this issue is just weird!

Hi Larry, Thanks for the tip, I understood that it had something to do with the database because the same error occurs on both the script that I've written based on the code in the book, and the unadulterated script that I downloaded. I did notice that the database's collation was set as swedish for some crazy reason and so I have since opted to drop the whole database and start over, making sure that the first_name column existed and that the character set and collation was utf8. However, I'm still getting the same exact error message! Just to be sure, here is the SQL commands that I'm using in phpMyAdmin: CREATE DATABASE userRegistration CHARACTER SET utf8 COLLATE utf8_general_ci; USE userRegistration; CREATE TABLE users ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(40) NOT NULL, email VARCHAR(80) NOT NULL, pass CHAR(40) NOT NULL, user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, active CHAR(32), registration_date DATETIME NOT NULL, PRIMARY KEY (user_id), UNIQUE KEY (email), INDEX login (email, pass) ); As a test, I decided to try inserting the records directly into the users table through phpMyAdmin and was able to do so that way. I then manually typed out the url to activate the account: http://localhost/user_registration/htdocs/activate.php?x=myemail@yahoo.com&y=67212e1fabb98f9890b7c84bca14b428 I didn't get a confirmation message after loading this page and checking back in the database the activate row was not set to NULL as activate.php is meant to do, so that didn't work. After doing all of this, I STILL can't understand where the issue is, and I've ensured that the first_name column exists and that the database CHARSET is utf8. I was able to manually insert records into the users table using the same MySQL query as in register.php, but am still unable to utilize register.php, activate.php or to test login.php. The error message in register.php is telling me to look at the first_name column in the database, but there's not much else to look at there: An error occured in script '/Users/indigital03/Sites/user_registration/htdocs/login.php' on line 30: Query: SELECT user_id, first_name, user_level FROM users WHERE (email='indigital03@yahoo.com' AND pass=SHA1('test')) AND active IS NULL MySQL Error: Unknown column 'first_name' in 'field list'

Make the login form sticky Starting on page 91, in Chapter 3, "Creating Dynamic Websites," Larry discusses several strategies for presetting the value of text inputs, as well as radio buttons and check boxes. Basically, by printing the appropriate variables if and when they exist. The login form, login.php currently only has 2 fields, the username and password. Easy enough, however, I wanted to know if I should have to use the original $_POST['value'] or the trimmed value that is passed through the array_map() function, $trimmed['value'] such as: <p><b>Email Address:</b> <input type="text" name="email" size="20" maxlength="60" value="<?php if (isset($trimmed['email'])) echo $trimmed['email']; ?>" /></p> <p><b>Password:</b> <input type="password" name="pass" size="20" maxlength="20" value="<?php if (isset($trimmed['pass'])) echo $trimmed['pass']; ?>" /></p> NOTE: Attempting to register as a new user in order to test this out, I am getting a ton of error messages in register.php, but I think the gist of it is: An error occured in script '../Sites/user_registration/htdocs/register.php' on line 98: Query: INSERT INTO users (email, pass, first_name, last_name, active, registration_date) VALUES ( 'example@yahoo.com', SHA1('test'), 'Joe', 'Schmoe', 'b4dd87dc00e1e9a0376d7ea461b90945', NOW() ) MySQL Error: Unknown column 'first_name' in 'field list' I have compared my code with the downloaded code and there are no discrepancies. The SQL creating the database cannot be the issue, as that was copied and pasted into phpMyAdmin from the downloaded code. A possible explanation for this is that there is no email service installed on my virtual server/local environment... THIS ISSUE IS RESOLVED IN REPLY #10 (thank you to abigail and Antonio Conte).

Hi abigail, Ok, I think I know what needs to happen now! The message board example does not contain a config file and currently the database connection is established within header.html, meaning it absolutely must be included early in the script in order for any database query's to take place. So, adding a config file is necessary if I want to be able to untether the database connection file from the header file. As a reminder, I ultimately want to retrieve the records from the database for the thread's subject ($messages['subject']), before calling the header in read.php in order to use each thread's subject as a subtitle within the title tags in header.html. The issue that arises from this solution is that header.html currently queries the database as well for the language-specific words to use throughout the site ($words['welcome']). Apparently however, including both the config file and then the header.html file doesn't result in a database connection for header.html: An error occured in script '../Sites/forum2/htdocs/includes/header.html' on line 40: Undefined variable: dbc I've also discovered the following: 1. Including config.inc.php in header.html results in a duplication of the custom error_handler function: Fatal error: Cannot redeclare my_error_handler() (previously declared in ../Sites/forum2/htdocs/includes/config.inc.php:18) in ../Sites/forum2/htdocs/includes/config.inc.php on line 18 2. Pasting the code that queries the database for the language-specific words ($words['welcome']) within config.inc.php doesn't work because there is no actual database connection in config.inc.php: An error occured in script '../Sites/forum2/htdocs/includes/config.inc.php' on line 64: Undefined variable: dbc You'll have to excuse me, I've gotten in over my head, or at the very least, I still don't understand a key feature of the basic nature about how php and mysql work together as yet. But I'm eagerly anticipating another a-ha moment soon!

Apply the same validation techniques to login.php as used in register.php In Chapter 9, "Using PHP with MySQL" - page 285, Larry explains that "database security with respect to PHP comes down to three broad issues: 1. Protecting the MySQL access information 2. Not revealing too much about the database. 3. Being cautious when running queries, particularly those involving user-submitted data." (Ullman 285) This bullet point concerns the third objective in that we are applying extra precautions when using user-supplied data to query the database: a. Validate that some value has been submitted (Ullman 45 & 49). b. Use typecasting (Ullman 409-411) or the filter extension (Ullman 421) to guarantee that some value is the proper type (number, string, etc.) (also see type hinting a functions parameters in "PHP Advanced and Object-Oriented Programming, 15) c. Use regular expressions to make sure that submitted data matches what you would expect, see ch.14, perl-compatible regular expressions. d. Run user-submitted data through the mysqli_real_escape_string() function in order to make data safe to use in a query by escaping what could be problematic characters (Ullman 285). Larry was kind enough to show us how to implement a lot of these solutions in the single script, register.php, so that we can use that as a reference for login.php and in our future projects as well. Looking over register.php, the validation process seems to consist of five steps: 1. Use the array_map() function (Ullman 406) to apply the trim() function to each element submitted through the form in order to trim off extraneous spaces (Ullman 154). //update: use the array_map() function to trim all incoming data through $_POST: $trimmed = array_map('trim', $_POST); 2. Establish some flag variables with a default value of FALSE. //establish some flag variables and set them all to FALSE: $e = $p = FALSE; //this is a shortcut syntax (email, password) 3. Validate the user-inputted values. //validate the email address with the Filter extension: if ( filter_var($trimmed['email'], FILTER_VALIDATE_EMAIL) ) { and: //validate the password: if ( preg_match ('/^\w{4,20}$/', $trimmed['pass']) ) { //must be between 4 and 20 characters and only contain letters, numbers, and the underscore (\w) 4. Prevent problematic characters by using mysqli_real_escape_string() function. //assign the value to a variable: $e = mysqli_real_escape_string($dbc, $trimmed['email']); and: $p = mysqli_real_escape_string($dbc, $trimmed['pass']); 5. Check that every flag variable passed as TRUE and then continue to execute the database query. //if both validation routines passed: if ($e && $p) { P.S. Hi Christopher, I'm originally trained as a graphic designer, so I just used illustrator to create all of the charts and their graphics that I'm posting here.

Hi abigail! Thanks for the tip, I never thought to tackle the issue from that angle and playing around with it I did get something going. However, I wanted to be able to add the name of each thread within read.php as the subtitle (namely, $messages['subject']), and maybe even use language-specific subtitles for the other pages (like $words['specific_page']). In my last attempt at a solution, reply #4 - this thread, I imagined that I needed to: Possibly turning the $messages['subject'] into a globally accessible variable. Concatenate $page_title within the code somewhere before the browser parses the page's title in order to reflect the updated $page_title variable. Your tip aligns well with the second objective, but the first objective is a massive wrench in the gears of the larger goal.

I created a chart demonstrating the site architecture of this example, which can be found here. REVIEW: 1. What is output buffering? What are the benefits of using it? Output Buffering prevents everything that a PHP script prints and any HTML outside of the PHP tags from immediately being sent to the Web browser by placing them in a memory buffer until it is flushed. Therefore, you are free to call header(), setcookie(), and session_start() functions, which can normally only be called if nothing has been sent to the Web browser, at any point in a script. This can be a performance improvement and eliminates "headers already sent" error messages (Ullman 561). 2. Why shouldn't detailed error information be displayed on live sites? For security and aesthetic purposes, it's generally unwise for a public user to see PHP's detailed error messages. Frequently, error messages - particularly those dealing with the database - will reveal certain behind-the-scenes aspects of your Web application that are best not shown (Ullman 250). 3. Why must the active column in the users table allow for NULL values? What is the result if active is defined as NOT NULL? The active column in the users table will either store the 32-chacaracter-long activation code or have a NULL value. These values will be used in login.php to indicate whether a user has activated their account (by clicking a link in the registration email) or not. A NULL value indicates an active account, so if the active column is defined as NOT NULL, no one will ever be able to log in (Ullman 573, 586 & 589). 4. What are the three steps in terminating a session? · Clear an individual session variable or every session variable: unset($_SESSION['var']); $_SESSION = array(); · Destroy all of the session data from the server: session_destroy(); · Destroy the session ID stored in a cookie: setcookie('PHPSESSID', '', time() -3600, '/', 0, 0); (see Ullman 393) 5. What does the session_name() function do? The session_name() function allows you to set the name of a session to a more user-friendly one. The benefits of creating your own session name are twofold: it's marginally more secure and it may be better received by the end user (since the session name is the cookie name the end user will see) (Ullman 396). 6. What are the differences between truly encrypting data and creating a hash representation of some data? With a hash representation of a value comparisons can still be made, but the original data cannot be retrieved from the database. Data stored in an encrypted form is more secure but can also be decrypted if needed (Ullman 237). PURSUE • Apply the same validation techniques to login.php as used in register.php • Make the login form sticky • Add a last_login DATETIME field to the users table and update its value when a user logs in. Use this info to indicate to the user how long it has been since the last time she or he accessed the site. • If you’ve added the last_login field, se it to print a message on the home page as to how many users have logged in in the past say, hour or day. • Validate the submitted email address in forgot_password.php using the Filter extension or a regular expression. • Create view_users.php and edit_user.php scripts (see p.309 and 316) as recommended in the final sidebar. Restrict access to these scripts to administrators (those users whose access level is 1).

Well, that completes all the bullet points for review and pursue. Sadly, there is still three known issues that I was unable to resolve: The first bullet point, modifying the page title to include a page-specific subtitle, was pretty much an epic failure, even though I went in thinking it was going to be the easiest. I would appreciate if anybody could provide new insights into the issue, see reply #3 for details. While adding paginating functionality, reply #5, the database query for the last and first posts within forum.php doesn't produce any results. Adding a search page, search.php - reply #12, redirects me to forum.php no matter what I search for and without filtering the results. The code that I've developed for this example can be viewed here, if anyone would like to take a look at the completed examples and to offer any advice on fixing the known (or unknown) issues. I will move on to the next example, Ch.18 Review And Pursue - User Registration Example, but will integrate this example into that one in order to fulfill my larger goal of creating a finished web project, ready for launch on the web. Thanks for reading!

5. Create a search page for this forum. If you need help, see the search.php basic example available in the downloadable code. The first thing I noticed about this is that search.php was nowhere to be found in the book, but I did find it in the downloaded scripts just as was instructed. So, this is going to have to be reverse-engineered like in the real world. I tested this in the browser right away and noticed that there is no translations in the words table for "search," so that will be the first thing to do: ALTER TABLE words ADD COLUMN search VARCHAR(30) NOT NULL; SHOW COLUMNS FROM words; Then: UPDATE words SET search = 'search' WHERE lang_id = 1; SELECT search FROM words WHERE lang_id = 1; I want a search bar in the main menu, so I'll use most of the same code from search.php in header.html. However, I don't want the results to show in the sidebar, so I'll get rid of this code and just let search.php handle the details: // Handle the form. if (isset($_GET['terms']) { // Clean the terms: $terms = mysqli_real_escape_string($dbc, htmlentities(strip_tags($_GET['terms']))); // Run the query... $q = "SELECT * FROM languages WHERE lang_id = 100"; $r = mysqli_query($dbc, $q); if (mysqli_num_rows($r) > 0) { echo '<h2>Search Results</h2>'; } else { echo '<p>No results found.</p>'; } After doing that I came across some formatting issues, so I decided to move the word "search" into the input field by setting it as the default value. I do this within the conditional that makes the input value sticky in both header.html and search.php: } else { echo 'value="' . $words['search'] . '" '; } This also means I had to make sure the submitted form ignores that default value in search.php: if (isset($_GET['terms']) && $_GET['terms'] != $words['search']) { This has a couple of effects. First, the message "No results found." no longer appears when the default value of "search" is used. This is probably because the IF conditional returns false right away and nothing happens. So, maybe I should add an else-if conditional to it (search.php): } else if ($_GET['terms'] == $words['search']) { echo '<p>Please enter a search term.</p>'; } Another thing that is happening is when I click submit within search.php's input I go to: http://localhost/forum2/forum.php?lid=0&terms=search&submit=Submit - Even with the else if clause, WHAT'S THAT! Also, this happens no matter the search term used within search.php's input field. I hope I didn't mess it up P.S. Can someone explain how the db query: $q = "SELECT * FROM languages WHERE lang_id = 100"; Actually produces results specific to the search term?

Hi eric, I don't know if this helps you, but I added new records into the words table using: UPDATE words SET new_col = 'english_value.' WHERE lang_id = 1;

4. Apply the redirect_user() function to post_form.php here (see p372). The redirect_user() function was introduced in Chapter 12, "Cookies and Sessions" and script 12.2 - login_functions.inc.php. Looking over that script, the function seems pretty straightforward, dynamically creating an absolute URL and redirecting users that log-in or to the home page when not logged-in. The post_form.php is meant to be an included file and therefore it can never be accessed directly, also, because only logged-in users can access it, it makes sense to immediately re-direct users to index.php when they access this file in error. Currently, post_form.php has a function that redirects users, but the redirect_user() function seems much more versatile. But, to be honest it is that versatility that makes implementing this example no fun - it's a copy and paste job! // Function that re-directs a user upon successfully logging-in, or to an index page if they aren't logged-in //begin defining the new function: function redirect_user ($page = 'index.php') { //defines a default URL //start defining the URL: $url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']); //assigns the values of http:// plus the host name (www or localhost), the current //directory and then the current script (http://www.example.com/somedir/) //remove any ending slashes from the URL: $url = rtrim($url, '/\\'); //by default rtrim removes spaces from the right side of a string, if provided with a list of characters as a second argument, it'll chop those off instead //append the specific page to the URL: $url .= '/' . $page; // '/' is added because all trailing slashes were removed by the rtrim function //redirect the user (see p355-357 on header function): header("Location: $url"); exit(); //Quit the script } //end of redirect_user() function NOTE: This function is invoked within login.php and logged in.php to redirect the user upon successful/unsuccessful log-ins. Come to think of it, it makes a lot of sense to define this function within login_functions.inc.php in order to modularize the site ("PHP Advanced and Object-Oriented Programming" 44). I don't see where I could use this in the current site's architecture, but I'm thinking this is going to be rethought when I integrate the message board with the user registration example from chapter 18...

3. If you want, add the necessary columns to the words table, and the appropriate code to the PHP scripts, so that every navigational, error, and other element is language-specific. Use a Web site such as Yahoo! BabelFish (http://babelfish.yahoo.com) for the Translations. This would require a lot more digging and user-testing than I'm willing to put in, so I'll find one example where this is needed and try to implement a translation in another language. In fact, post.php is riddled with error messages and read.php has an error message in only english as well. I'll tackle read.php's single error message in order to consider that page completed: } else { // invalid thread ID: echo '<p>This page has been accessed in error.</p>'; } // End IF ($tid) First, I would have to create a new column within the words table (see Ullman 222): ALTER TABLE words ADD COLUMN access_error VARCHAR(40) NOT NULL; phpMyAdmin says the query ran successfully, but I want to check that the new column exists in the table (Ullman 188): SHOW COLUMNS FROM words; After confirming that the new column exists, I add French and English translations using an UPDATE (Ullman 149): UPDATE words SET access_error = 'This page has been accessed in error.' WHERE lang_id = 1; Then I confirm the new row (Ullman 138 - 140): SELECT access_error FROM words WHERE lang_id = 1; NOTE: I figured out how to do this by using phpMyAdmin's UI and then looking at the query that was used, so yes, I cheated. For instance, I wouldn't have thought I could get at the words table within a query that I previously thought was only meant to manipulate the database as a whole. But, I did learn a couple of things along the way, first, because the lang_id is assigned a UNIQUE index it cannot be replicated (Ullman 179), so trying a simple INSERT INTO will not work, second, you cannot use a WHERE clause with an INSERT INTO query (such as INSERT INTO WHERE lang_id = 1). It just didn't work. Third, using an IF conditional function within my query (Ullman 218) got me close as an alternative solution: INSERT INTO words (access_error) VALUES (IF(lang_id = 1, 'This page has been accessed in error.', '')); It went through all right, but nothing was inserted. I'm not sure what the issue was, but it seems like a reasonable alternative and I walked away from it thinking it's possible, but that I was doing something wrong. Finally, to wrap up this bullet point, I simply change the original error message within read.php to this: } else { //invalid thread ID: echo "<p>{$words['access_error']}</p>"; } //End IF ($tid) To test this out I go to, localhost/forum2/read.php?tid=1000 and there is my message!

I have to say that the main database query in forum.php of chapter 17 was mind-boggling after the months since I've read this book or reviewed the scripts. At the same time, I view it's complexity as an opportunity to review many of the material covered in the book on querying databases with PHP. Since that's what I'm trying to do here, I thought I would break it down and try to understand every last bit of it: $q = "SELECT t.thread_id, t.subject, username, COUNT(post_id) - 1 AS responses, MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} GROUP BY (p.thread_id) ORDER BY $order_by LIMIT $start, $display"; The query returns the ID and subject of each thread from the threads table, the name of the user who started the thread from the users table, the # of replies to each thread (assigned to the alias, responses). NOTE: When selecting from multiple tables dot syntax (table.column) is required (see Ullman 205): $q = "SELECT t.thread_id, t.subject, username, COUNT(post_id) - 1 AS responses, As well as the date the thread was started (assigned to the alias, last), and the date the thread last had a reply (assigned the alias, first), from the posts table: MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first Meaning this query selects 3 tables, total, in order to produce a virtual table of results (see Ullman 204 on joins). The DATE_FORMAT function is explained on page 162 and can be used to format both the date and time using a combination of key codes and the percent sign to indicate what values you want returned (see table 5.5). The results of the formatting, %e-%b-%y %l:%i %p, would be 10-Jan-14 3:32 PM, for example. This function is used in conjunction with the MAX() and MIN() functions which predictably return the largest and smallest values in a column (see p.214). The overarching structure of this query is a join between threads and posts using the thread_id column: FROM threads AS t INNER JOIN posts AS p USING (thread_id) An inner join returns all of the records from the named tables wherever a match is made (here, thread_id). This result is then joined with the users table using the user_id column. Joining 3 or more tables results in a relationship between the initial virtual table and the third table: INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} An inner join between the newly created virtual table and the users table returns all of the records from the named tables wherever a match is made. Here, based on two conditionals, when the user_id in the threads table matches the user_id in the users table and where the lang_id column in the threads table matches the lid set in the session. When those two conditions are met, the result is a final virtual table that finds every post from the posts table and returns 2 records from the threads table, and 3 records from the posts table (2 of which are the result of a function) based on a matching thread_id between the threads and posts tables. It then finds every username from the users table, but only when t.user_id = u.user_id between the threads (or if you'd like, the newly created virtual table because those results have already been filtered by the first joins conditions) and the users tables and lastly, only those records where the language id matches the language id set in the session (see Ullman 205 on inner joins and 212 on joining multiple tables). Again, here is the code joining all 3 tables: FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} Lastly, the columns of the virtual table are grouped by the thread_id column in posts, the ORDER BY clause is set to reflect the sorting order and a LIMIT clause will determine the start and finish of the returned columns (much like an array set to start on x and finish on y). The ORDER BY clause is set by the $order_by variable within the switch statement and will indicate how the returned results should be ordered. The LIMIT clause dictates with which record to begin retrieving ($start) and how many to return ($display) from that point (see Ullman 320 & 326): GROUP BY (p.thread_id) ORDER BY $order_by LIMIT $start, $display"; Now that mind-boggling query doesn't seem so mind-boggling anymore! $q = "SELECT t.thread_id, t.subject, username, COUNT(post_id) - 1 AS responses, MAX(DATE_FORMAT($last, '%e-%b-%y %1:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %1:%i %p')) AS first FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} GROUP BY (p.thread_id) ORDER BY $order_by LIMIT $start, $display";

Hi Larry, Thanks, it's so great that you have provided us all with this valuable resource and that you make yourself available to your adoring fans! I'm hoping that I can really get into web development and have realized that participating in a vibrant community of experts and novices could help me to achieve my goals.

2. Add pagination to the forum.php script (see p316, view_users.php) Instructions for paginating search results were given on p.316 and implemented in the file, view_users.php, from chapter 10. Mirroring those steps for the forum.php file, I begin by declaring a variable that sets the number of records to be displayed on each page. However, I've changed the number to be displayed to 4 so that I can test the pagination without having to create a dozen new threads. I added this new code right before the main database query in forum.php, and will simply include my heavily commented code here: //set the number of records to be displayed on each page (this will correspond to the y in a LIMIT clause): $display = 4; //check if the number of pages, $_GET['p'] has already been determined in the //URL (by the if ($pages > 1) function below) and is a numeric value: if (isset($_GET['p']) && is_numeric($_GET['p'])) { //retrieve that information and store it in the $pages variable: $pages = $_GET['p']; } else { A tip on page 322 says that "if the paginated links don't match the number of records returned it's most likely because the main query and the COUNT() query are too different. These two queries will never be the same, but they must perform the same join (if applicable) and have the same WHERE and/or GROUP BY clauses to be accurate" (Ullman 322). So, I took the main query and carried over the same JOIN and GROUP BY clauses to the COUNT() query in order to reconcile the 2 queries and get the correct amount of paginated links (let me know if it can be stripped further because now it's a MOTHER): //count the number of records in the db. Adjusted to match the main query, see tip2 p.322: $q = "SELECT COUNT(subject) FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']}"; $r = @mysqli_query ($dbc, $q); //Handle the returned row of the SELECT query as an Indexed array and assign to a variable: $row = @mysqli_fetch_array ($r, MYSQLI_NUM); //store the retrieved info in a variable: $records = $row[0]; //if the # of records is greater then the # assigned to the $display variable (line 23): if ($records > $display) { //determine the number of pages by dividing the # of records with $display $pages = ceil ($records/$display); //ceil returns the next highest integer of the result } else { //if $records is not greater than $display: //only 1 page is required: $pages = 1; } } //end of check $_GET['p'] --------------------------------------------------------- //check if the starting point, $_GET['s'] has already been set (by if ($pages > 1) function below): if (isset($_GET['s']) && is_numeric($_GET['s'])) { //if it has, assign it to the $start variable (this will correspond to the x in a LIMIT clause): $start = $_GET['s']; } else { $start = 0; } //end the starting point conditional ---------------------------------------------- Adding sorting functionality, if the user sets the $sort by clicking a link in the table header, then $sort uses that value, otherwise $sort is assigned a default value of 'last' (corresponds with the alias set for the last post in the db query): $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'last'; Just as in view_users.php, I determine how the results should be ordered in a switch statement (the variable, $order_by, will also be used in the db query): switch ($sort) { case 'subj': $order_by = 't.subject ASC'; break; case 'user': $order_by = 'username ASC'; break; case 'resp': $order_by = 'responses ASC'; break; case 'first': $order_by = 'first ASC'; break; case 'last': $order_by = 'last ASC'; break; default: $order_by = 'last ASC'; $sort = 'last'; break; } // end switch Moving down the view_users.php file, I change forum.php's ORDER BY clause to reflect the number of records to return, as well as the sorting order. The ORDER BY clause is set by the $order_by variable within the switch statement and will indicate how the returned results should be ordered. The LIMIT clause dictates with which record to begin retrieving ($start) and how many to return ($display) from that point (see Ullman 320 & 326): ... ORDER BY $order_by LIMIT $start, $display"; Next, I adjusted the table header row in order to make the results sortable, assigning the values passed in the url to the corresponding values in the switch statement created above: echo '<table width="100%" border="0" cellspacing="2" cellpadding="2" align="center"> <tr> <td align="left" width="50%"><em><a href="forum.php?sort=subj">' . $words['subject'] . '</a></em>:</td> <td align="left" width="20%"><em><a href="forum.php?sort=user">' . $words['posted_by'] . '</a></em>:</td> <td align="center" width="10%"><em><a href="forum.php?sort=first">' . $words['posted_on'] . '</a></em>:</td> <td align="center" width="10%"><em><a href="forum.php?sort=resp">' . $words['replies'] . '</a></em>:</td> <td align="center" width="10%"><em><a href="forum.php?sort=last">' . $words['latest_reply'] . '</a></em>:</td> </tr>'; NOTE: Nothing is displayed in the html table for the "latest reply" and "posted on" columns using the following in the database query: MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first After closing the html table, I inserted code directly from view_users.php, with minimal changes to reflect the page's url, as shown below with heavy commenting for explanation: //begin paginated-links creation section: if ($pages > 1) { //when there is more than 1 page: echo '<br /><p>'; //determine the current page by dividing the starting number by the display number + 1 (p321): $current_page = ($start/$display) + 1; //create a 'Previous' link to the previous page when necessary: if ($current_page != 1) { //if the current page is not the first page: //create a previous link to the earlier result (made up of the script //name, the starting point, which is calculated by taking the starting //point minus the # being displayed, and the number of pages) //this has been modified to pass a sort value, see p326: echo '<a href="forum.php?s=' . ($start - $display) . '&p=' . $pages . '&sort=' . $sort . '">Previous</a> '; } //make the paginated-links (represented as numbers) by //looping from 1 to the total number of pages: for ($i = 1; $i <= $pages; $i++) { //make all the pages active links, except for the current one: if ($i != $current_page) { //For each link, the starting point, s, will be calculated by multiplying //the # of records to display per page times one less than $i (p322): //this has been modified to pass a sort value, see p326: echo '<a href="forum.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&sort=' . $sort . '">' . $i . '</a> '; } else { echo $i . ' '; } //end of if conditional } // End of FOR loop. // If it's not the last page, make a 'Next' link: if ($current_page != $pages) { //this has been modified to pass a sort value, see p326: echo '<a href="forum.php?s=' . ($start + $display) . '&p=' . $pages . '&sort=' . $sort . '">Next</a>'; } } //end of pagination link creation section NOTE Attempting to test this code, I discovered that I was unable to create a new thread using post.php. Looking over the downloaded code, the book's code, and the code that I've written using the book, there is no issue or discrepancies. I believe this issue is because only registered users can create new threads or post messages, and I haven't set up a registration or log-in system yet (I may just combine the message board and user registration example into one application, since a log-in system is assumed here). Therefore, I have to manually insert new threads into the db. This would involve adding records into the thread and posts tables, which I did in phpMyAdmin (only changing the thread_id, post_id, subject, message, and posted_on values for each new thread): INSERT INTO threads (thread_id, lang_id, user_id, subject) VALUES (13, 1, 1, 'Test 5'); INSERT INTO posts (post_id, thread_id, user_id, message, posted_on) VALUES (20, 13, 1, 'This is test 5.', '2011-07-29 05:01:02'); I've added 5 new threads all together and also changed the $display variable set in forum.php to 4 to test the pagination.

I've made some progress, namely, if I place the code: $page_title .= ' - ' . $messages['subject']; echo $page_title; Within the conditional checking if the message is printed at or around line 60, which is itself within the while loop that assigns the queries results into an associative array, $messages[] (meaning that $messages['subject'] is local to the while loop). I can now print "Default Page title - name of the thread" onto the page as long as I do so within the while loop. However, outside of the while loop, $messages['subject'] is an unknown variable and, furthermore, the page title is not updated because the browser more than likely parses it waaay before I update it in read.php. So, I see two issues to resolve in order to get this to work: Possibly turning the $messages['subject'] into a globally accessible variable. Concatenate $page_title within the code somewhere before the browser parses the page's title in order to reflect the updated $page_title variable. Any tips, advice, or suggestions would be appreciated of course! Meanwhile, I will continue on to the next Pursue bullet point and consider alternative solutions to implement this bullet point.

Hi Eric, I'm a newb just doing research on issues raised in this forum. As you said, ON DUPLICATE KEY is not mentioned in the book, so I thought I would add some useful information here about it. If it helps you with your problem great, otherwise this is just for those of us who aren't yet familiar with that particular statement. ON DUPLICATE KEY checks if the data you're inserting violates a unique key requirement, turning into an UPDATE on the row which has the key combination that caused the violation. For example: INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3; If your primary key is only one of the fields (e.g. 'a'), and you already had a row in the table where a=1, then you'd get an UPDATE of that existing row that results in the 'c' value being set to 3. If it's a composite key (say, 'a,b'), then if you had existing records with a=1 and b=2, then that rows' C would get changed to 3 instead of a new record being created. If there's no unique/primary keys on this table, then you'd never get an update, it'd just be an extra-verbose INSERT statement. -http://stackoverflow.com/questions/8610269/mysql-on-duplicate-key-update also see, MySQL Reference Manual NOTE: As a refresher, UNIQUE indexes require that each row have a unique value for that column. UNIQUE indexes are added to any columns, here lang_id, whose values cannot be duplicated within the table (Ullman 179-180).

1. Modify the header and other files so that each page’s title uses both the default lang page title and a subtitle based upon the page being viewed (e.g., the name of the thread currently shown). Currently, the content of the page title is set in header.html by querying the words table in the database for the language-specific title. This means that every page displays the same page title set in header.html, namely, "PHP and MySQL For Dynamic Web Sites: The Forum!" (except in either english or the language set by registered users). I believe this request is asking for the title of each page to read "Default Page title - name of the thread." One possible solution is to concatenate the page title set in header.html to include page-specific subtitles. I will have to set the page title in header.html to it's own variable, which I've done right after the results of the language database query are assigned to an associate array at or around line 50: //Set the page title: $page_title = $words['title']; Then I change the code in the title tag to reflect the variable: <title><?php echo $page_title; ?></title> After I do that, I then concatenate $page_title to include the name of the thread in read.php right after assigning the fetched database rows to another associative array at or around line 55: $page_title .= ' - ' . $messages['subject']; ​This is where I admit defeat on my first attempt at completing the books "Review and Pursue" suggestions. Nothing is happening in the title as a result of this code. P.S. I am aware that the instructions for the Review and Pursue may very well be asking to implement a broader execution whereby the title of each page would read "Default Page title - page specific message," in each language and for every page. I believe that would require adding a new column to the words table for the subtitle of each page. Then assigning base name($SERVER['PHP_SELF']) to a variable and checking its result in a switch statement to determine which row in the database to fetch and display in the title. However, I'm not even going to try it until I figure out how to crack this much softer nut that I've been struggling with above!

I created a chart demonstrating the site architecture of this example, which can be found here, and the database ERD of this example can be found here. REVIEW: 1. What impact does a database's character set, or a PHP or HTML page's encoding have? An HTML page or PHP script can specify its encoding, which dictates what characters, and therefore languages, are supported. Similarly, by setting your MySQL databases encoding, you can impact what characters can be stored in it. (Ullman 184) 2. Why does the encoding and character set have to be the same everywhere? What happens if there are differences? The encoding and character set affects what characters and languages are supported and can be stored. When communicating with MySQL those characters need to be transferred using the same encoding (Ullman 186). Failing to do so will likely result in odd characters when viewing the page in a Web browser. (Ullman 2) 3. What is a primary key? What is a foreign key? A Primary key is a unique identifier that has to abide by certain rules Always have a value (cannot be NULL) Have a value that remains the same (Never Changes) Have a unique value for each record in a table. A foreign key is the representation in Table B of the primary key from Table A (Ullman 167). 4. What is the benefit of using UTC for stored dates and times? Using UTC stores dates and times in a time-zone neutral way. UTC, like Greenwich Mean Time (GMT), provides a common point of origin, from which all times in the world can be expressed as UTC plus or minus some hours and minutes (Ullman 189) 5. Why is the pass column in the users table set as a CHAR instead of a VARCHAR, when each user's password could be of a variable length? CHAR will always be stored as a string the length of the column, using spaces to pad it (Ullman 117). The SHA1() function is being used to encrypt the password, which creates an encrypted string that is always exactly 40 characters long. Therefore, the users table's pass column is defined as CHAR(40) (Ullman 135). 6. How do you begin a session in PHP? How do you store a value in a session? How do you retrieve a previously stored value? session_start(); $_SESSION['key'] = value; $_SESSION['var']; 7. How do you create an alias in a SQL command? What are the benefits of using an alias? Aliases are created using the term AS within queries. An alias is a symbolic renaming of an item used in a query to a shorter length alternative for easier callback and less typing work (Ullman 153). PURSUE: Modify the header and other files so that each page’s title uses both the default lang page title and a subtitle based upon the page being viewed (e.g., the name of the thread currently shown). Add pagination to the forum.php script (see p316, view_users.php). If you want, add the necessary columns to the words table, and the appropriate code to the PHP scripts, so that every navigational, error, and other element is language-specific. Use a Web site such as Yahoo! BabelFish (http://babelfish.yahoo.com) for the Translations. Apply the redirect_user() function to post_form.php here (see p372). Create a search page for this forum. If you need help, see the search.php basic example available in the downloadable code.

Shouldn't be an issue, just follow the instructions here as well.

Thanks Larry, I can understand that, I will try out a couple of more services but linking to my blog does seem to work. I'm really into charts and supplementary resources so I tend to upload images, but they're not essential, I'm just trying to make myself useful.