You are previewing Head First PHP & MySQL.

Head First PHP & MySQL

Cover of Head First PHP & MySQL by Michael Morrison... Published by O'Reilly Media, Inc.
  1. Head First PHP & MySQL
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. Advance Praise for Head First PHP & MySQL
  5. Praise for Head First HTML with CSS & XHTML
  6. Praise for Head First JavaScript
  7. Author(s) of Head First PHP & MySQL
  8. How to Use This Book: Intro
    1. Who is this book for?
    2. Who should probably back away from this book?
    3. We know what you’re thinking
    4. We know what your brain is thinking
    5. Metacognition: thinking about thinking
    6. Here’s what WE did:
    7. Here’s what YOU can do to bend your brain into submission
    8. Read Me
    9. The technical review team
    10. Acknowledgments
    11. Safari Books Online
  9. 1. Add Life to your Static Pages: It’s Alive
    1. HTML is static and boring
    2. PHP brings web pages to life
    3. Dogs in space
    4. A form helps Owen get the whole story
    5. Forms are made of HTML
    6. The HTML form has problems
    7. HTML acts on the CLIENT
    8. PHP acts on the SERVER
    9. PHP scripts run on the server
    10. Use PHP to access the form data
    11. PHP scripts must live on a server!
    12. Get your PHP scripts to the server
    13. The server turns PHP into HTML
    14. Deconstructing Owen’s PHP script
    15. A few PHP rules to live code by
    16. Finding the perfect variable name
    17. Variables are for storing script data
    18. $–POST is a special variable that holds form data
    19. $–POST transports form data to your script
    20. Creating the email message body with PHP
    21. Even plain text can be formatted... a little
    22. Newlines need double-quoted strings
    23. Assemble an email message for Owen
    24. Variables store the email pieces and parts
    25. Sending an email message with PHP
    26. Owen starts getting emails
    27. Owen starts losing emails
    28. Your PHP & MySQL Toolbox
  10. 2. Connecting to MySQL: How it fits together
    1. Owen’s PHP form works well. Too well...
    2. MySQL excels at storing data
    3. Owen needs a MySQL database
    4. Create a MySQL database and table
    5. The INSERT statement in action
    6. Use SELECT to get table data
    7. Let PHP handle the tedious SQL stuff
    8. PHP lets data drive Owen’s web form
    9. Connect to your database from PHP
    10. Insert data with a PHP script
    11. Use PHP functions to talk to the database
    12. Get connected with mysqli_connect()
    13. Build the INSERT query in PHP
    14. Query the MySQL database with PHP
    15. Close your connection with mysqli–close()
    16. $–POST provides the form data
    17. Owen needs help sifting through his data
    18. Owen’s on his way to finding Fang
  11. 3. Create and Populate a Database: Creating your own data
    1. The Elvis store is open for business
    2. Elmer needs an application
    3. Visualize Elmer’s application design
    4. It all starts with a table
    5. Make contact with the MySQL server
    6. Create a database for Elmer’s emails
    7. Create a table inside the database
    8. We need to define our data
    9. Take a meeting with some MySQL data types
    10. Create your table with a query
    11. Getting the cart table in front of the horse database
    12. USE the database before you use it
    13. DESCRIBE reveals the structure of tables
    14. Elmer’s ready to store data
    15. Create the Add Email script
    16. The other side of Elmer’s application
    17. The nuts and bolts of the Send Email script
    18. First things first, grab the data
    19. mysqli_fetch_array() fetches query results
    20. Looping for a WHILE
    21. Looping through data with while
    22. You’ve got mail...from Elmer!
    23. Sometimes people want out
    24. Removing data with DELETE
    25. Use WHERE to DELETE specific data
    26. Minimize the risk of accidental deletions
    27. is a web application
    28. Your PHP & MySQL Toolbox
  12. 4. Realistic and Practical Applications: Your Application on the Web
    1. Elmer has some irritated customers
    2. Protecting Elmer from... Elmer
    3. Demand good form data
    4. The logic behind Send Email validation
    5. Your code can make decisions with IF
    6. Testing for truth
    7. IF checks for more than just equality
    8. The logic behind Send Email validation
    9. PHP functions for verifying variables
    10. Test multiple conditions with AND and OR
    11. Form users need feedback
    12. Ease in and out of PHP as needed
    13. Use a flag to avoid duplicate code
    14. Code the HTML form only once
    15. A form that references itself
    16. Point the form action at the script
    17. Check to see if the form has been submitted
    18. Some users are still disgruntled
    19. Table rows should be uniquely identifiable
      1. What Elmer’s table contains now:
      2. What Elmer’s table should contain:
    20. Primary keys enforce uniqueness
    21. The five rules of primary keys:
    22. From checkboxes to customer IDs
    23. Loop through an array with foreach
    24. Your PHP & MySQL Toolbox
  13. 5. Working with Data Stored in Files: When a database just isn’t enough
    1. Virtual guitarists like to compete
      1. Text can’t be trusted
    2. The proof is in the rockin’ picture
    3. The application needs to store images
    4. Planning for image file uploads in Guitar Wars
    5. The high score database must be ALTERed
    6. How do we get an image from the user?
    7. Insert the image filename into the database
    8. Find out the name of the uploaded file
    9. Where did the uploaded file go?
    10. Create a home for uploaded image files
    11. Shared data has to be shared
    12. Shared script data is required
    13. Think of require_once as “insert”
    14. Timing Order is everything with high scores
    15. Honoring the top Guitar Warrior
    16. Format the top score with HTML and CSS
    17. Only small images allowed
    18. File validation makes the app more robust
    19. Plan for an Admin page
      1. These pages are for users:
      2. This page is only for the administrator:
    20. Generate score removal links on the Admin page
    21. Scripts can communicate with each other
    22. Of GETs and POSTs
    23. GET, POST, and high score removal
    24. Isolate the high score for deletion
    25. Control how much you delete with LIMIT
    26. Your PHP & MySQL Toolbox
  14. 6. Securing your Application: Assume they’re all out to get you
    1. The day the music died
    2. Where did the high scores go?
    3. Securing the teeming hordes
    4. Protecting the Guitar Wars Admin page
    5. HTTP authentication requires headers
    6. Take control of headers with PHP
    7. Authenticating with headers
      1. OK, so maybe Guitar Wars is NOT secure
    8. Create an Authorize script
    9. Guitar Wars Episode II : Attack of the High Score Clones
    10. Subtraction by addition
    11. Security requires humans
    12. Plan for moderation in Guitar Wars
    13. Make room for approvals with ALTER
    14. Unapproved scores aren’t worthy
    15. The million-point hack
    16. Everything in moderation... ?
    17. How exactly did she do it?
    18. Tricking MySQL with comments
    19. The Add Score form was SQL injected
    20. Protect your data from SQL injections
    21. A safer INSERT (with parameters)
    22. Form validation can never be too smart
    23. Cease fire!
    24. Your PHP & MySQL Toolbox
  15. 7. building personalized web apps: Remember me?
    1. They say opposites attract
    2. Mismatch is all about personal data
    3. Mismatch needs user log-ins
      1. Username
      2. Password
    4. Come up with a user log-in gameplan
    5. Prepping the database for log-ins
    6. Constructing a log-in user interface
    7. Encrypt passwords with SHA()
    8. Decrypting Comparing passwords
      1. Making room for the encrypted password
    9. Authorizing users with HTTP
    10. Logging In Users with HTTP Authentication
    11. A form for signing up new users
    12. Give users a chance to sign up
    13. Sometimes you just need a cookie
    14. What’s in a cookie?
    15. Bake Use cookies with PHP
    16. Rethinking the flow of log-ins
    17. A cookie-powered log-in
    18. Navigating the Mismatch application
    19. Logging out means deleting cookies
    20. Sessions aren’t dependent on the client
    21. The life and times of sessions
    22. Keeping up with session data
    23. Renovate Mismatch with sessions
    24. Log out with sessions
    25. Complete the session transformation
    26. Users aren’t feeling welcome
    27. Sessions are short-lived...
    28. ... but cookies can last forever!
    29. Sessions + Cookies = Superior log-in persistence
  16. 8. Eliminate Duplicate Code: Sharing is caring
    1. Mismatch is in pieces
    2. Rebuilding Mismatch from a template
    3. Rebuild Mismatch with templates
    4. Mismatch is whole again... and much better organized
  17. 9. Control your Data, Control your World: Harvesting data
    1. Making the perfect mismatch
    2. Mismatching is all about the data
    3. Break down the Mismatch data
      1. Categories
      2. Topics
      3. Responses
    4. Model a database with a schema
    5. Wire together multiple tables
    6. Foreign keys in action
    7. Tables can match row for row
    8. One row leads to many
    9. Matching rows many-to-many
    10. Build a Mismatch questionnaire
    11. Get responses into the database
    12. We can drive a form with data
    13. Speaking of efficiency...
    14. Generate the Mismatch questionnaire form
    15. The data is now driving the form
    16. Strive for a bit of normalcy
    17. When normalizing, think in atoms
    18. Why be normal, really?
    19. Three steps to a normal database
    20. Altering the Mismatch database
    21. So is Mismatch really normal?
    22. A query within a query within a query...
    23. Let’s all join hands tables
    24. Connect the with dots
    25. Surely we can do more with inner joins
    26. Simplifying ON with USING
    27. Nicknames for tables and columns
    28. Joins to the rescue
    29. Love is a numbers game
    30. Five steps to a successful mismatch
    31. Prepare for the mismatch search
    32. Compare users for “mismatchiness”
    33. All we need is a FOR loop
    34. Finishing the mismatching
    35. Your PHP & MySQL Toolbox
  18. 10. String and Custom Functions: Better living through functions
    1. A good risky job is hard to find
    2. The search leaves no margin for error
    3. SQL queries can be flexible with LIKE
    4. Explode a string into individual words
    5. implode() builds a string from substrings
    6. Preprocess the search string
    7. Replace unwanted search characters
    8. The query needs legit search terms
    9. Copy non-empty elements to a new array
    10. Sometimes you just need part of a string
    11. Extract substrings from either end
    12. Multiple queries can sort our results
    13. Functions let you reuse code
    14. Build a query with a custom function
    15. SWITCH makes far more decisions than IF
    16. Give build_query() the ability to sort
    17. We can paginate our results
    18. Get only the rows you need with LIMIT
    19. Control page links with LIMIT
    20. Keep track of the pagination data
    21. Set up the pagination variables
    22. Revise the query for paginated results
    23. Generate the page navigation links
    24. Putting together the complete Search script
    25. The complete Search script, continued...
    26. Your PHP & MySQL Toolbox
  19. 11. Regular Expressions: Rules for replacement
    1. Risky Jobs lets users submit resumes
    2. Decide what your data should look like
    3. Formulate a pattern for phone numbers
    4. Match patterns with regular expressions
    5. Build patterns using metacharacters
    6. Fine-tune patterns with character classes
    7. Check for patterns with preg_match()
    8. Standardize the phone number data
    9. Get rid of the unwanted characters
    10. Matching email addresses can be tricky
    11. Domain suffixes are everywhere
    12. Use PHP to check the domain
    13. Email validation: putting it all together
    14. Your PHP & MySQL Toolbox
  20. 12. Visualizing your Data... and More!: Drawing dynamic graphics
    1. Guitar Wars Reloaded: Rise of the Machines
    2. No input form is safe
    3. We need to separate man from machine
    4. We can defeat automation with automation
    5. Generate the CAPTCHA pass-phrase text
    6. Visualizing the CAPTCHA image
    7. Inside the GD graphics functions
    8. The GD graphics functions continued...
    9. Drawing text with a font
    10. Generate a random CAPTCHA image
    11. Returning sanity to Guitar Wars
    12. Add CAPTCHA to the Add Score script
    13. Five degrees of opposability
    14. Charting mismatchiness
    15. Storing bar graph data
    16. From one array to another
    17. Build an array of mismatched topics
    18. Formulating a bar graphing plan
    19. Crunching categories
    20. Doing the category math
    21. Bar graphing basics
    22. Draw and display the bar graph image
    23. Individual bar graph images for all
    24. Mismatch users are digging the bar graphs
    25. Your PHP & MySQL Toolbox
  21. 13. Syndication and Web Services: Interfacing to the world
    1. Owen needs to get the word out about Fang
    2. Push alien abduction data to the people
    3. RSS pushes web content to the people
    4. RSS is really XML
    5. From database to newsreader
    6. Visualizing XML RSS
    7. Dynamically generate an RSS feed
    8. Link to the RSS feed
    9. A picture video is worth a thousand million words
    10. Pulling web content from others
    11. Syndicating YouTube videos
    12. Make a YouTube video request
    13. Owen is ready to build a REST request
    14. YouTube speaks XML
    15. Deconstruct a YouTube XML response
    16. Visualize the XML video data
    17. Access XML data with objects
    18. From XML elements to PHP objects
    19. Drill into XML data with objects
    20. Not without a namespace!
    21. Fang sightings are on the rise
    22. Lay out videos for viewing
    23. Format video data for display
    24. Your PHP & MySQL Toolbox
  22. A. Leftovers: The Top Ten Topics (we didn’t cover)
    1. #1. Retrofit this book for PHP4 and mysql functions
    2. #2. User permissions in MySQL
    3. #3. Error reporting for MySQL
    4. #4. Exception handling PHP errors
    5. #4. Exception handling PHP errors (cont.)
    6. #5. Object-oriented PHP
    7. #5. Object-oriented PHP (cont.)
      1. So two big advantages of using Object Oriented PHP are:
    8. #6. Securing your PHP application
    9. #6. Securing your PHP application (cont.)
    10. #7. Protect your app from cross-site scripting
    11. #7. Protect your app from cross-site scripting (cont.)
      1. Validate everything
      2. Built-in PHP functions can help
      3. Data is guilty until proven innocent
    12. #8. Operator precedence
    13. #9. What’s the difference between PHP 5 and PHP 6
      1. More Unicode support
    14. #9. What’s the difference between PHP 5 and PHP 6 (cont.)
      1. OO refinements, XML support, and other changes
    15. #10. Reusing other people’s PHP
      1. Drupal
      2. phpBB
      3. Coppermine Gallery
      4. WordPress
  23. B. Set up a Development Environment: A place to play
    1. Create a PHP development environment
    2. Find out what you have
    3. Do you have a web server?
    4. Do you have PHP? Which version?
    5. Do you have MySQL? Which version?
    6. Start with the Web Server
    7. Apache installation... concluded
    8. PHP installation
    9. PHP installation steps
    10. PHP installation steps... concluded
    11. Installing MySQL
      1. Instructions and Troubleshooting
    12. Steps to Install MySQL on Windows
      1. Download your installer
      2. Pick a destination folder
      3. Click “Install” and you’re done!
    13. Enabling PHP on Mac OS X
    14. Steps to Install MySQL on Mac OS X
    15. Moving from production to a live site
    16. Dump your data (and your tables)
    17. Prepare to use your dumped data
    18. Move dumped data to the live server
    19. Connect to the live server
  24. C. Extend your PHP: Get even more
    1. Extending your PHP
      1. If you’re using Windows, you’re in luck
    2. And on the Mac...
  25. Index
  26. About the Authors
  27. Copyright

Chapter 4. Realistic and Practical Applications: Your Application on the Web

image with no caption

Sometimes you have to be realistic and rethink your plans.

Or plan more carefully in the first place. When your application’s out there on the Web, you may discover that you haven’t planned well enough. Things that you thought would work aren’t good enough in the real world. This chapter takes a look at some real-world problems that can occur as you move your application from testing to a live site. Along the way, we’ll show you more important PHP and SQL code.

Elmer has some irritated customers

Elmer’s customer mailing list has grown by leaps and bounds, but his emails have generated some complaints. The complaints vary, but they all seem to involve customers receiving blank email messages or multiple messages, neither of which is good. Elmer needs to figure out what’s gone wrong and fix it. His business depends on it.

image with no caption
image with no caption

Protecting Elmer from... Elmer

So “operator error’ is really the problem here—Elmer inadvertently clicks Submit without entering the email information, and blank emails get sent to the entire list. It’s never safe to assume a web form will be used exactly the way it was intended. That’s why it’s up to you, the vigilant PHP scripter, to try and head off these kinds of problems by anticipating that some users will misuse your forms.

Let’s take a look at the code in our current sendemail.php script to see how Elmer’s empty email messages are getting created.

image with no caption
image with no caption

Write down what you think should be changed in the sendemail.php script code to fix the blank email problem:



Demand good form data

Elmer’s Send Email form’s in need of validation, which is the process of checking to make sure form data is OK before doing anything with it. Elmer already uses validation even though he doesn’t call it that. Whenever he receives an order for Elvis gear, he doesn’t just immediately fill it and send it out... he validates it first!

In the case of an order, Elmer first checks to see if the customer’s credit card is valid. If so, he fills the order and gets it ready to ship. But then he has to check if the customer’s shipping address is complete. If that checks out, then Elmer goes ahead and sends out the order. A successful order for Elmer’s store always hinges on the validation of the order data.

Validation means making sure the data you get is the data you expect.

image with no caption

To solve Elmer’s blank email problem, we need to validate the form data delivered to the sendemail.php script. This means the form data is submitted from the client web page (sendemail.html) to the server, and the server (sendemail.php) checks to make sure all the data is present. We can add code to sendemail.php that examines the values in the text boxes and checks to make sure they aren’t empty. If everything checks out OK, the script sends out the emails.

image with no caption

The logic behind Send Email validation

Elmer needs to validate the data he gets from the sendemail.html form before he sends any emails. In fact, sending the emails should completely hinge on the data validation. What we really need PHP to do is make a decision based on the validity of the form data received by the sendemail.php script. We need code that says, “if the data is valid, go ahead and send the emails.”

image with no caption

Your code can make decisions with IF

The PHP if statement lets your code make decisions based on whether or not something is true. Consider Elmer’s orders again. Before filling an order, Elmer must get paid, which means charging the customer’s credit card. If the customer gave Elmer the wrong card number, he can’t fill the order. So Elmer performs a kind of real-world validation on every order that goes like this:

If the customer’s credit card checks out, go ahead and fill the order.

We can translate this scenario to PHP code using the if statement, which is designed to handle just this kind of decision making.

The basic if statement has three parts:

  1. The if keyword

    This starts off the statement.

  2. The test condition

    The test condition, or conditional expression, is located in parentheses right after the if keyword. Here’s where you put the statement that you want to determine the validity, or truth, of.

  3. The action

    The action of an if statement directly follows the test condition and is enclosed in curly braces. Here’s where you put the PHP code you want to execute if the condition is, in fact, true.

image with no caption

Testing for truth

The heart of the if statement is its test condition, which is always interpreted as either true or false. The test condition can be a variable, a function call, or a comparison of one thing to another, as a few examples. Elmer’s credit card validation relies on a function call as the test condition, which means the value returned by the function is either true or false.

image with no caption

It’s quite common to use a comparison as a test condition, which typically involves comparing a variable to some value. For example, maybe Elmer wants to give a discount to customers who live in Nevada. He could create an if statement that carries out a comparison on part of the shipping address, like this:

image with no caption

This test condition performs a comparison for equality, which involves two equal signs (==). Equality comparisons aren’t just for variables and strings. You can compare variables to numbers, variables to variables, and even perform calculations.

image with no caption

IF checks for more than just equality

An if statement can check for more than just equality. The test condition in your if statement can also check to see if a value is greater than another one. If it is, the result of the condition is true, and the action code is executed. Here are a few more tests you can use to control the decision of an if statement.

image with no caption
image with no caption

Yes, you can compare strings in if test conditions.

They work based on the alphabet, with a being considered smaller than (less than) z. Using greater than and less than can help you when you need to present information in alphabetical order.

The logic behind Send Email validation

Elmer needs to validate the data he gets from the sendemail.html form before he sends any emails. In fact, sending the emails should completely hinge on the data validation. What we really need PHP to do is make a decision based on the validity of the form data received by the sendemail.php script. We need code that says, “if the data is valid, go ahead and send the emails.”

But first we need to grab the form data and store it in a couple of variables:

image with no caption

This form data is all we need to check and see if there is data in each of the form fields. The logic might look something like this:

IF $subject contains text AND $body contains text

THEN send email

Or we could take the opposite approach and check to see if the form fields are both empty, in which case we could display a warning to the user:

IF $subject is empty AND $body is empty

THEN echo error message

Both of these examples have a problem in that their logic requires us to make two comparisons in a single if statement. One possible solution is to use two if statements...

PHP functions for verifying variables

Using == to check for an empty string works, but there’s a better way that involves built-in PHP functions. The isset() function tests to see if a variable exists, which means that it’s been assigned a value. The empty() function takes things one step further and determines whether a variable contains an empty value, which PHP defines as 0, an empty string ('' or ""), or the values false or NULL. So isset() only returns true if a variable has been assigned a value, while empty() only returns true if a variable has been set to 0, an empty string, false, or NULL.

Let’s take a look at how these functions work:

image with no caption
image with no caption

That’s half right. We’re really just checking to make sure the form data isn’t empty, so empty() is what we need.

The $subject and $text variables are assigned values from the $_POST['subject'] and $_POST['elvismail'] superglobals. If you test these variables with isset(), it will always return true regardless of whether or not they hold any actual text. In other words, isset() doesn’t show you the difference between a blank form field and a filled out one. The empty() function checks to see if a variable is actually empty, which is what we need for form validation.

isset() checks that a variable exists and is set.

empty() checks to see if a variable has any contents.

image with no caption

No, but there’s an easy way to reverse the logic of any test condition... the negation operator.

We know the test condition that controls an if statement always results in a value of true or false. But what if our logic dictates that we need to check for the reverse of what a condition gives us? For example, it would be helpful to know if Elmer’s form fields are not empty before sending a bunch of emails with the form data. Problem is, there is no notempty() function. The solution is the negation operator (!), which turns true into false, or false into true. So !empty() literally calls the empty() function and reverses its result, like this:

image with no caption
image with no caption
image with no caption

Joe: I think you’re right. If we want to make sure all those fields are not empty, we’ll have to nest an if statement for each field.

Frank: As long as we indent each line of code for each if statement, aren’t we OK?

Jill: Technically, yes. I mean, the code will certainly work no matter how many if’s we nest, but I’m worried about it getting hard to understand with so much nesting. Just matching up curly braces accurately could be a problem.

Frank: That’s true. I think it’d also be a pain having to indent the action code so far... let’s see, that’s ten form fields, giving us ten nested ifs with ten levels of indentation. Even if we just indent each if two spaces, that’s 20 spaces before every line of action code. Yuck.

Joe: But if we indent with tabs, it cuts that in half—10 tabs versus 20 spaces isn’t so bad.

Jill: Guys, the issue isn’t really about the specific code used to indent the nested if’s. It’s just not a good coding practice to nest if statements so deep. Think about it like this—we’re really talking about one logical test condition, “are all our form fields non-empty?” The problem is, that test condition involves ten different pieces of data, causing us to have to break it into ten separate if statements.

Frank: Ah, I see. So what we need is a way to test all ten pieces of form data in a single test condition, right?

Jill: Yup.

Joe: Then we could write one big test condition that checks all the form fields at once. Awesome!

Jill: Yeah, but we’re still missing the piece of the puzzle that lets us combine multiple comparisons within a single test condition...

Test multiple conditions with AND and OR

You can build a test condition for an if statement with multiple checks by connecting them with a logical operator. Let’s look at how it works with two familiar conditions, !empty($subject) and !empty($text). This first example involves two expressions joined by the logical AND operator, which is coded using &&.

PHP logic operators make it possible to structure more elegant if statements.

image with no caption

The AND operator takes two true/false values and gives you true only if they are both true; otherwise the result is false. So in this case both form fields must be non-empty in order for the test condition to be true and the action code for the if statement to run.

The logical OR operator, coded as ||, is similar to AND except that it results in true if either of the true/false values is true. Here’s an example:

Logical AND is coded as &&, while logical OR is coded as ||.


That’s not the number eleven, it’s two vertical pipes ||—just above backslash (\) on your keyboard.

image with no caption

So the action code for this if statement is executed if either one of the form fields is not empty. Things get even more interesting if you want to isolate one form field as being empty but the other having data, like this:

image with no caption

Since this test condition uses AND, both expressions inside of the test condition must be true in order for the action code to be run. This means the Subject form field must be empty, but the Body field must have data. You can reverse this check by moving the negation operator (!) to the other empty() function:

image with no caption

The AND (&&) and OR (||) logical operators make it possible to structure much more powerful test conditions that would otherwise require additional, often messy, if statements.

Form users need feedback

Our sendemail.php code does a great job of validating the form data so that no mail gets sent out if either the Subject or Body fields are left blank. But when the validation fails, and no emails are sent out, the script doesn’t tell Elmer what happened. He just gets a blank web page.

image with no caption
image with no caption

The problem is that our code only reacts to a successful validation, in which case it sends the email messages. But if the if statement turns out being false (invalid form data), the code doesn’t do anything, leaving Elmer in the dark about whether any emails were sent or what went wrong. Here’s the abbreviated script code, which reveals the blank page problem:

image with no caption

We need to let Elmer know that there was a problem, ideally telling him what form fields were blank so that he can try entering the message again.

image with no caption

That won’t work because code after the if statement will always be executed.

Placing the echo statement after the if statement just means it runs after the if statement, but it always runs regardless of the outcome of the if. That’s not what we need. We need the echo statement to show an error message only if the test condition of the if statement is false. You could express our logic as this:

IF subject contains text AND body contains text

THEN send email

ELSE echo error message

The if statement offers an optional else clause that runs code in the event that the test condition is false. So our error message echo code can go in an else clause, in which case it only gets run when one of the form fields is left empty. Just place the word else after the if statement, and then stick the action code for it inside curly braces:

image with no caption

The else clause executes code when an if test condition is false.

image with no caption

It’s always a good idea to simplify code whenever possible, especially nested code that gets too deep.

Too many else clauses with nested if statements can make your code hard to follow. Maybe that wouldn’t matter if we never had to look at it again, but that’s unlikely. If we ever needed to change the form and add another field, validating it would be trickier than it needed to be because it would be hard to read the code and figure out where the changes need to go.

image with no caption
image with no caption

Validation in Elmer’s Send Email script is working but it could be a lot more helpful.

When the sendemail.php script detects missing form data, it displays a message that information is missing, but that’s it. There’s no link back to the original form, for example. And even worse, when Elmer navigates back to the original form, the information he did enter is no longer there. He has to retype both the subject and body of his email message.

Brain Power

What would you do to improve the error handling of the Send Email script to make it more helpful?.

image with no caption

Displaying the form would definitely be helpful, as it would save Elmer having to navigate back in his browser.

So in addition to echoing an error message when one of the form fields is empty, we also need to regenerate the HTML form code from PHP by echoing it to the browser. This code shows that PHP is capable of generating some fairly complex HTML code:

image with no caption

If you’re thinking this code looks a bit chaotic, that’s because it is. Just because you can do something in PHP doesn’t mean you should. In this case, the added complexity of echoing all that HTML code is a problem. This is a big enough chunk of code that generating it via PHP with echo is really not a good option...

Ease in and out of PHP as needed

It’s sometimes easy to forget that a PHP script is really just an HTML web page that is capable of holding PHP code. Any code in a PHP script that isn’t enclosed by the <?php and ?> tags is assumed to be HTML. This means you can close a block of PHP code and revert to HTML as needed, and then pick back up with a new block of PHP code. This is an extremely handy technique for outputting a chunk of HTML code that is unwieldy to generate through PHP echo statements... like our Send Email form code.

You can close and open blocks of PHP code to output chunks of HTML code in a PHP script.

image with no caption

Write down anything you think might be limiting about this code. How would you fix it?




Use a flag to avoid duplicate code

The problem with the previous code is that it will have to drop out of PHP and duplicate the form code in three different places (once for each validation error). We can use a true/false variable known as a flag to keep track of whether or not we need to output the form. Let’s call it $output_form. Then we can check the variable later in the code and display the form if the variable is true.

So we need to start out the script with $output_form set to false, and then only change it to true if a form field is empty and we need to show the form:

image with no caption

Code the HTML form only once

Turning the new validation logic into PHP code involves creating and initializing the new $output_form variable, and then making sure to set it throughout the validation code. Most important is the new if statement at the end of the code that only displays the form if $output_form is set to true.

By making HTML code dependent on an IF statement, we avoid duplicate code in our script.

image with no caption
image with no caption

HTML alone can’t preserve form data.

When Elmer submits the Send Email form with an empty field, the sendemail.php script catches the error and generates a new form. But the new form is pure HTML code, which can’t possibly know anything about any data Elmer might have entered earlier. So we’re generating a clean new form as part of the validation, which is wiping out any data Elmer might have entered.

image with no caption

Ack. We can’t get around the fact that a new form will have to be generated in the PHP script. But we need a way to remember any data Elmer might have already entered, and plug it back into the new form so that Elmer can focus solely on filling out the form field that he accidentally left empty...

A form that references itself

How can it be possible to remove sendemail.html from the Send Email form equation? The answer is that we’re not actually eliminating any HTML code, we’re just moving it to the PHP script. This is made possible by the fact that a PHP script can contain HTML code just like a normal web page. So we can structure our script so that it not only processes the form on submission but also displays the form initially, which is all sendemail.html was doing.

The key to the sendemail.php script being able to fill the role left by sendemail.html is the form action. Since the script itself now contains the HTML form, the form action leads back to the script... a self-referencing form.

An HTML form that is part of the PHP script that processes it is known as self-referencing.

image with no caption

To understand what’s going on here, think about the first time Elmer visits the page (script). An empty form is generated as HTML code and displayed. Elmer fills out a field of the form and clicks Submit. The script processes its own form, and displays an error message if any data’s missing. More importantly, the script displays the form again, but this time it includes any data Elmer has already entered. When a form’s smart enough to remember data entered into it in prior submissions, it’s known as a sticky form... the data sticks to it!

Sticky forms remember the data the user has already correctly entered.

Brain Power

How do you think we can tweak Elmer’s application to make the form fields sticky?

Point the form action at the script

As we’ve seen several times, the action attribute of the <form> tag is what connects a form to a PHP script that processes it. Setting the action of Elmer’s form to sendemail.php works just fine in allowing it to process itself, which is the first step toward form stickiness. In fact, the form already has its action attribute set to the script:

image with no caption

This code works, assuming you don’t ever rename the script and forget to update the code. But there’s a better way that works no matter what because it doesn’t rely on a specific script filename. It’s the built-in PHP superglobal variable $_SERVER['PHP_SELF'], which stores the name of the current script. You can replace the script URL in the form action to $_SERVER['PHP_SELF'], and not ever have to worry about updating anything if you ever need to rename the script.

The only catch is that $_SERVER['PHP_SELF'] is PHP code, which means you have to echo its value so that it is output as part of the HTML code, like this:

image with no caption

Granted, using $_SERVER['PHP_SELF'] instead of the script name isn’t an earth shattering improvement but it’s one of the many little things you can do to make your scripts easier to maintain over time.

$_SERVER[‘PHP_SELF’] stores away the name of the current script.

Check to see if the form has been submitted

The problem is that the script can’t distinguish between the form being displayed for the first time and it being submitted with incomplete data. So the script reports missing data the very first time the form is displayed, which is confusing. The question is, how can we check to see if the form is being submitted? If we know that, we can make sure we only validate data on a submission.

Remember how, when a form is submitted using the POST method, its data is stored away in the $_POST array? If the form hasn’t been submitted, then the $_POST array isn’t filled with any data. Or to put it another way, the $_POST array hasn’t been set. Any guess what function we could call to see if the $_POST array’s been set?

image with no caption

The $_POST superglobal allows us to check and see if a form has been submitted.

Since every form has a Submit button, an easy way to check to see if a form has been submitted is to see if there’s $_POST data for the Submit button. The data’s just the label on the button, which isn’t important. What’s important is simply the existence of $_POST['submit'], which tells us that the form has been submitted. Just make sure that 'submit' matches up with the id attribute of the Submit button in the form code.

image with no caption

That’s right. Detecting the form submission is important, but we still need to plug the sticky form data back into the form.

Knowing if the form’s been submitted is an important part of making it sticky, but it isn’t the only part. The part we’re missing is taking any form data that was submitted and plugging it back into the form as the form is being output. You can set an input form field using the value attribute of the HTML <input> tag. For example, this code presets the value of an input field using the value attribute:

image with no caption

But we don’t want to hardcode a specific value. We want to insert a piece of data from a PHP variable. How is that possible? Remember that we’ve used echo to dynamically generate HTML code from PHP in other situations. In this case, we can use echo to generate a value for the value attribute from a PHP variable, like this:

image with no caption

Elmer’s form can then be modified similarly to take advantage of sticky data:

image with no caption
image with no caption

Some users are still disgruntled

Form validation has gone a long way toward dealing with Elmer’s frustrated customers, particularly those who were receiving blank emails. But not everyone is happy. It seems a few people are receiving duplicate emails... remember this guy from earlier in the chapter?

image with no caption

Elmer knows he didn’t send a message more than once, leading him to suspect that maybe some users have accidentally subscribed to his email list more than once. Not a problem, just use the Remove Email page/script from the last chapter to remove the user, right?

Unfortunately, it’s not that simple. Removing Elbert using his email address will completely delete him from the email_list table, causing him to no longer receive any email messages from Elmer. We need a way to only delete Elbert’s extra rows from the table, making sure to leave one.

image with no caption

Brain Power

How can Elmer delete all but one of the multiple rows in his table that have identical email addresses?

image with no caption

Joe: Maybe our Add Email form should check for duplicate email addresses before adding new users. That would fix it, right?

Frank: Excellent idea.

Jill: Yes, that would solve the problem moving forward, but it doesn’t help us deal with duplicate email addresses that are already in the database.

Frank: Right. What if we tried to use a different column in the table to delete the extra rows, like last_name?

Jill: I wondered about that, but using a last name is potentially even worse than an email address. What if we wanted to delete someone named John Smith from our mailing list, and we ran the following SQL code:

DELETE FROM email_list WHERE last_name = 'Smith'

Joe: We wouldn’t just delete John Smith from our table; we’d be deleting Will Smith, Maggie Smith, Emmitt Smith...

Frank: Wow, that wouldn’t be good. Last names are more likely to be common across rows than email addresses, and first names would be even worse than that. We could lose dozens and dozens of rows with one simple query.

Jill: Exactly. We can’t risk using a WHERE clause that will delete rows we need to keep. We need to be certain we can pinpoint just the ones we want to remove.

Joe: So what the heck do we do? We can’t use email, last_name, or first_name in our WHERE clause.

Frank: We’re out of columns in our table to use. Looks like we’re out of luck.

Jill: Not necessarily. What we really need is something to make each row of the table unique—then we could pinpoint rows without any trouble. And just because we don’t currently have a column that has a unique value for each row doesn’t mean we can’t add one.

Joe: A new column? But we’ve already decided on our table structure.

Frank: Yeah, but what we’ve got isn’t meeting our needs. You’re right that it would be better if we had realized this beforehand, so we could have designed our table accordingly, but it’s not too late to fix what we’ve got.

Joe: OK, but what would we call our new column? What data would we put into it?

Jill: Well, since its purpose would be to uniquely identify each row in the table, we could call it identifier, or maybe just id for short.

Frank: Nice, and we can fill the id column with a different ID number for each row, so when we execute our DELETE, we’ll be removing rows based on a unique number, instead of an email address or surname.

Joe: Exactly. It’s really a great idea, isn’t it? I’m so glad I thought of it.

Table rows should be uniquely identifiable

Part of the whole idea of sticking something in a database is that later on you’d like to look it up and do something with it. Knowing this, it’s incredibly important for each row in a table to be uniquely identifiable, meaning that you can specifically access one row (and only that row!). Elmer’s email_list table makes a dangerous assumption that email addresses are unique. That assumption works as long as no one accidentally subscribes to the mailing list twice, but when they do (and they will!), their email address gets stored in the table twice... no more uniqueness!

What Elmer’s table contains now:

image with no caption

When you don’t have a column of truly unique values in a table, you should create one. MySQL gives you a way to add a unique integer column, also called a primary key, for each row in your table.

What Elmer’s table should contain:

image with no caption
image with no caption

It’s true that DROP TABLE would destroy Elmer’s data. But SQL has a another command that lets you make changes to an existing table without losing any data.

It’s called ALTER TABLE, and we can use it to create a new column without having to drop the table and destroy its data. Here’s what the general format of an ALTER TABLE statement looks like for adding a new column to a table:

image with no caption

We can use the ALTER TABLE command to add a new column to the email_list table, which we’ll name id. We’ll give the id column a data type of INT, since integers work great for establishing uniqueness. Some other information is also required, as this code reveals:

image with no caption

This ALTER TABLE statement has a lot going on because primary keys have to be created with very specific features. For example, NOT NULL tells MySQL that there must be a value in the id column—you can never leave it blank. AUTO_INCREMENT further describes the traits of the id column by causing it to automatically get set to a unique numeric value when a new row is inserted. As its name suggests, AUTO_INCREMENT automatically adds one to the last id value used in a row and places this value into the id column when you INSERT a new row into your table. Finally, PRIMARY KEY tells MySQL that each value in the id column is unique, but there’s more to it than just uniqueness...

Primary keys enforce uniqueness

A primary key is a column in a table that distinguishes each row in that table as unique. Unlike normal columns, which could also be designed to be unique, only one column can be made the primary key. This provides a clear choice for what column to use in any queries that need to pinpoint specific rows.

In order to ensure this uniqueness for primary keys, MySQL imposes a bunch of restrictions on the column that has been declared as PRIMARY KEY. You can think of these restrictions as rules to be followed as you work with primary keys:

image with no caption

A primary key is a column in your table that makes each row unique.

The five rules of primary keys:

The data in a primary key can’t be repeated.

Two rows should never have the same data in their primary keys. No exceptions—a primary key should always have unique values within a given table.

A primary key must have a value.

If a primary key was left empty (NULL), then it might not be unique because other rows could potentially also be NULL. Always set your primary keys to unique values!

The primary key must be set when a new row is inserted.

If you could insert a row without a primary key, you would run the risk of ending up with a NULL primary key and duplicate rows in your table, which would defeat the purpose.

A primary key must be as efficient as possible.

A primary key should contain only the information it needs to be unique and nothing more. That’s why integers make good primary keys—they allow for uniqueness without requiring much storage.

The value of a primary key can’t be changed.

If you could change the value of your key, you’d risk accidentally setting it to a value you already used. Remember, it has to remain unique at all costs.

image with no caption
image with no caption

Joe: The problem is that the user needs to pinpoint rows of data using the primary key instead of the email address.

Frank: That’s right! So we just need to change the form so that the user enters the ID of a customer instead of their email address. No problemo!

Jill: Actually, big problemo. The user has no way of knowing the ID of a customer without somehow finding them in the database. In fact, the user doesn’t know anything about the database structure. Maybe what we need is to rethink the form so that it lists out all the names and email addresses in a list with checkboxes next to each one. Here, I’ll sketch it for you.

image with no caption

Frank: Nice sketch, but how does that help Elmer isolate a customer for deletion using their ID?

Joe: Hmm. What if we stored the customer ID in the value of the checkbox. That way it isn’t actually visible, but the script can get to it.

Jill: That’s a great idea. So we could generate the form automatically in a loop by doing a SELECT to get all the data, and then creating each checkbox input field from a row of query data.

Joe: Cool. But what happens when the Submit button is pressed? What does $_POST have in it?

Frank: Hang on, Joe, we’ll get there in a minute. Let’s just start by building this part of the script, the part that displays all the data from the table and writes out those checkboxes...

From checkboxes to customer IDs

The checkbox code generated by the Remove Email script is simple HTML with our primary key (id) stuffed into the value attribute of the <input> tag. There’s one small, but very important change from ordinary checkbox HTML code, though. You might have noticed square brackets ([]) at the end of the checkbox name—they serve a vital purpose.

image with no caption

The square brackets result in the creation of an array within $_POST that stores the contents of the value attribute of every checked checkbox in the form. Since each checkbox’s value attribute contains a primary key, each value in the todelete array is the ID of the row in our table that needs to be deleted. This makes it possible for us to loop through the todelete array and issue an SQL query to delete each customer that is checked in the form.

image with no caption
image with no caption

We could use a while loop but there’s a more elegant solution using a different kind of loop.

The foreach loop is a special kind of loop designed specifically for cycling through values stored in an array. All you need to do is specify the array you’d like to loop through and a variable to store the values in, and PHP will take care of iterating over them one by one... no test condition required!

Write down how you think a foreach loop might loop through an array of Elmer’s customer IDs:



Loop through an array with foreach

The foreach loop takes an array and loops through each element in the array without the need for a test condition or loop counter. As it steps through each element in the array, it temporarily stores the value of that element in a variable. Assuming an array is stored in a variable named $customers, this code steps through each one:

image with no caption

So if we want to loop through the customer IDs stored in the $_POST array in the Remove Email script, we can use the following foreach code:

image with no caption

The $delete_id variable holds the value of each array element as the loop progresses through them one at a time.

image with no caption

With the foreach loop now stepping through each of the checked checkboxes in the Remove Email form, we just need to add code inside of the loop to issue a DELETE query and actually delete each row from the email_list table.

Your PHP & MySQL Toolbox

You bagged quite a few new PHP and MySQL skills while taking Elmer’s web application to a whole new level...

image with no caption

The best content for your career. Discover unlimited learning on demand for around $1/day.