Dynamic Web Development at ITP

Class 6 - Basic PHP Tricks and Bulk Data Population

Contents:

Redirection
Cookies
Session Management
Login and Password Protected Pages
Sending E-mail
Image Management
Miscellaneous Tricks
Data Population Techniques
Related Resources

Redirection [top]
Before we look at some of the sexier stuff, let's cover a simple but extremely useful function - the ability to redirect the browser from one webpage to another. Suppose someone tries to access a protected area of a site without logging in. We can detect this, and then send them straight to the login page without ever showing any part of the protected page. To do this, we simply use the PHP header() function and the "Location" parameter that is part of the HTTP header specification:

if (!$isLoggedIn)
{ header("Location: class06-login.php?err=notloggedin");
  exit;
}

Note that this should precede any HTML being output to the browser, so if you need to redirect, detect this at the beginning of the script, write the header, and then immediately exit.

PHP Header Function Reference

Cookies [top]
Cookies are sets of name and value pairs that can be stored locally on the user's hard drive using their web browser. This is great for remembering someone's username, account ID, settings, etc. One of PHP's many Superglobal arrays is $_COOKIES, which we can use to retrieve this info and a built-in function, setcookie(), which can store it:

# Get a value from a form
$usernameValue = $_POST["un"];

# Compute a date for cookie to expire in seconds
$expirationDate = time()+3600*24*90;  # This is 90 days, your mileage may vary
  
# This is how you SET a new cookie value
setcookie("username", $usernameValue, $expirationDate);

# This is how you RETRIEVE a previously set cookie value whose name was 'username'
$username = $_COOKIE["username"];

Using Cookies | Source Code

Session Management [top]
Ever wonder how a site knows if you're logged in or not, and if you are, they know that it's you? This type of persistence is achieved with session management -- server-side session management to be specific. Essentially, an event needs to take place that uniquely identifies you (like a login), and this information must stay persistent and available throughout your "session". It's a more flexible approach to user persistance than trying to achieve this through cookies. Again, one of PHP many Superglobal arrays is $_SESSION, which we can use to control the data that's stored in the session.

Sessions ultimately exists as a function of domain, such that they exist domain-wide, but not outside the domain. Conversely, if several sites appear on the same domain, they can share session data, which is not good from a security standpoint. Since the Stage server has multiple accounts on the same domain, each user needs to distinguish sessions related to their pages from another Stage user's account. To do this, you can simply give your session a name. Since many pages may be sharing the same session, I've put my session name in an include file called "info_session.php" and it looks something like this:

<?php
/*
info_session.php
*/

# Define session parameters
$myNetId = "cs220"; # Put your net ID here for reference
$myAppName = "app"; # Put the name of your application here (no spaces)

# Create unique name for the sessions that your application will be using
$mySessionName = $myNetId."_".$myAppName;

IMPORTANT: Notice how there was no trailing "?>" at the end of this file. For this type of file, you will fare better in the long run if you keep the end of the file in PHP. There is no rule that a PHP file needs a trailing ?>. This is only useful if you are exiting PHP within the script to write HTML.

After creating this file, you can reference this information at any time by issuing the statement:

# Get our DB info
require "info_session.php";

All sessions should first be initialized by indicating the name of the session and then using the session_start() function, which will either create a new session or find an existing session if one had previously been started. Sessions persist until either the browser is closed, or the user is inactive for a given period of time. On Stage, the default timeout is 1440 sec or 24 min.

# Change the timeout to 30 min
ini_set('session.gc_maxlifetime',1800);

# Initialize a new session or obtain old one if possible
require "info_session.php";

session_name($mySessionName); # $mySessionName is defined in info_session.php
session_start();

# This is how you obtain an old session value
$myOldUsername = $_SESSION["username"];

# Get a value from a submitted HTML form
$usernameValue = $_POST["un"];

# Store this value in the $_SESSION array
$_SESSION["username"] = $usernameValue;

# Whenever session data is changed, it's a good idea to call this
session_write_close();

Our First Session | Source Code

Common Problems with Sessions, Redirection and the header() Function
If you are interested in using sessions or redirection or any other type of behavior that uses the header() function, please note the warning at top of:

http://us.php.net/manual/en/function.header.php

-- start snippet ---

Remember that header() [or session_start() - ed. note] must be called before any actual output is sent, either by normal HTML tags, blank lines in a file, or from PHP. It is a very common error to read code with include(), or require(), functions, or another file access function, and have spaces or empty lines that are output before header() is called. The same problem exists when using a single PHP/HTML file:

<html>
<?php
/* This will give an error. Note the output
 * above, which is before the header() call */
header('Location: http://www.example.com/');
?>

-- end snippet ---

This means that if you have extra spaces or carriage returns in your info.php or info_session.php files either before the starting <? or after the ending ?>, and you call session_start() or header() after you include info.php, you'll get an error msg like:

Warning: session_start(): Cannot send session cache limiter - headers already sent
(output started at /home/[netid]/public_html/dwd/test/info.php:18)
in /home/[netid]public_html/dwd/test/login.php on line 20

Warning: Cannot modify header information - headers already sent by
(output started at /home/[netid]/public_html/dwd/test/info.php:18)
in /home/[netid]/public_html/dwd/test/login.php on line 154

Thus, an info.php or info_session.php file should start at line 1, char 1:

<?
...
?>

and end without spaces or a carriage return on the end. Similarly, in any of your scripts that use session management, it should start at line 1, char 1, and you should not print, echo or have any output to the browser (even a space) before you call session_start() or header().

Login and Password Protected Pages [top]
Using the concepts above, we now have the basis for a password protected site. We simply make a login page where we ask for username and password, and then do a SELECT on our member table to see if there are any entries that match these parameters. If we do, then we set $_SESSION vars that we can use to remember if they're logged in:

# Get these values from a submitted HTML form
$usernameValueDB = str_replace("'", "''", $usernameValue);
$passwordValueDB = str_replace("'", "''", $passwordValue);

# Look for this information in the DB
$SqlStatement = "SELECT id, firstname, lastname, email, last_login FROM class06_member
  WHERE username='$usernameValueDB' AND password='$passwordValueDB' ";
  
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
  die("Error " . mysql_errno() . " : " . mysql_error());
  
if ($row = mysql_fetch_assoc($result))
{  
  # Successful login so set our logged in session parameters
  $_SESSION["userid"] = $row['id'];
  $_SESSION["firstname"] = $row['firstname'];
  $_SESSION["lastname"] = $row['lastname'];
  $_SESSION["email"] = $row['email'];
  $_SESSION["last_login"] = $row['last_login'];
  $_SESSION["logged-in"] = 1;
  unset($_SESSION["login-trials"]);
  
  # Always the last thing you do before exiting your script
  mysql_close($connection);

  # Make sure the session data gets written
  session_write_close();
      
  # Now go to the member home page
  header("Location: class06-home.php");
  exit;
}
else
{ # Bad login
  $trials = $_SESSION["login-trials"];
  $trials++;
  $_SESSION["login-trials"] = $trials;
  
  # Make sure the session data gets written
  session_write_close();
}

Whenever the $_SESSION array gets changed, I like to call session_write_close() which forces the changes to be written to the server.

Then to password-protect a specific page, we put a simple check at the top of the script that re-directs to the login page if they've never logged in:

# Go to login page if not logged in
if ($_SESSION["logged-in"]!=1 || $_SESSION["userid"]<1)
{ header("Location: class06-login.php?err=notloggedin");
  exit;
}

Similarly, we can do a reverse check on the login page to see if they're already logged in. If they are, then we just send them to the home page since they're logged in.

# Go to home page if already logged in
if ($_SESSION["logged-in"] && $_SESSION["userid"]>0)
{ header("Location: class06-home.php");
  exit;
}

class06_member DB Table Structure

PHP Session Handling Functions

Sending E-mail [top]
One of the great ways to make a site sticky is to tie it to e-mail. In the UNIX environment, PHP has a built-in function called mail() that can interface with the "Sendmail" program to send out e-mails via SMTP to whomever based upon whatever we want. We can use the following code to have our web page send out e-mails:

# Syntax: mail(string to, string subject, string message [, string additional_headers ])
mail("$toNameValue <$toEmailValue>", 
  $subjectValue, $textareaWidgetValue,
  "From: $fromNameValue <$fromEmailValue>\nX-Mailer: PHP 4.x");

We can populate the email by simply substituting values from a submitted HTML form into the mail() arguments.

PHP E-mail Example | Source Code

PHP Mail Function | Emailing in PHP - Reference

We can do the same thing but with pre-formatted e-mails, like when you need a copy of your login information. Simply get the user's e-mail address, and use that to lookup their account information. Then use mail() to send it to them:

# Get the user's email from HTML form
$fromEmailValueDB = str_replace("'", "''", $fromEmailValue);

# Look for this information in the DB
$SqlStatement = "SELECT firstname, lastname, username, password FROM class06_member
  WHERE email='$fromEmailValueDB' ";
  
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
  die("Error " . mysql_errno() . " : " . mysql_error());
  
if ($row = mysql_fetch_assoc($result))
{
  # Found the user's info
  $firstname = $row['firstname'];
  $lastname = $row['lastname'];
  $username = $row['username'];
  $password = $row['password'];
  
  $subject = "Your login info";
  $message = "Dear $firstname,

Your login info is:

UN: $username
PW: $password

Please make a note of it.

Sincerely,
The Management
";

  # Send the mail
  mail("$firstname $lastname <$fromEmailValue>", 
    $subject, $message,
    "From: DB Class \nX-Mailer: PHP 4.x");
  
  $statusMsg = "Your information has been sent to $fromEmailValue!";
}

E-mail Login Info Example | Source Code

Image Management [top]
Another way to make a site sticky is to let users upload and manage image files.

PHP actually has a built-in superglobal array $_FILES, which hold the binary data for any uploaded image files. We can also use the HTML form widget of type "file" to let the user browse to the image file of their choice before uploading. Here's how it goes:

$uploadFileName = "file";

$submitName = "DataAction";
$submitValue = "Upload Image";

$upload_dir = "/home/netId/public_html/images/tank/";

$goodFile = 0;

if ($_POST[$submitName]==$submitValue)
{ # Someone wants to upload an image

  # Use basename() function to get just the filename without the path
  $filename = basename($_FILES[$uploadFileName]['name']);
  
  # Remove spaces and go to lowercase to circumvent weirdness
  $filename = str_replace(" ","",$filename);
  $filename = strtolower($filename);
  $fullFilename = $upload_dir.$filename;
  
  # Use a regular expression to make sure this has an image file extension
  $pattern = ".(gif|jpg|jpeg|png)$"; # A reg exp that looks for familiar file endings
  $goodFile = eregi($pattern,$filename);
  
  # Use move_uploaded_file() to move file from tmp dir to our upload dir
  if (!$goodFile)
  { $statusMsg = "Please upload an image file (GIF, JPG or PNG).";
  }
  elseif (move_uploaded_file($_FILES[$uploadFileName]['tmp_name'], $fullFilename))
  { # Get filesize in KB
    $filesizeb = filesize($fullFilename);
    $filesize = sprintf("%.1f", $filesizeb/1024);

    $statusMsg = "The file $filename was uploaded and is of size $filesize KB.";
  }
}

Once the file is on the server, we can update things in our database, like recording its filename, filesize, and most importantly, the person who uploaded it.

class06_image DB Table Structure

PHP Image Upload Example | Source Code

PHP File Upload Reference

Two important notes when it comes to image uploading:

The form encoding used by your HTML is different when uploading data. The correct encoding is multipart/form-data such that your form tag should look something like:
```
<form action="<?=$scriptName?>" method="POST" enctype="multipart/form-data"> 
```
It's a good idea to keep your image directory consistent and available to all scripts, so in my "info.php", I define both the physical directory on the file system where my website lives, and also its virtual path, such that if I ever change servers, I need only change the info in this file:
```
# In "info.php"

# File document root
$myServerFileDir = "/home/netid/public_html/";

# Web document root
$myWebFileDir = "http://itp.nyu.edu/~netid/";
```
Then in my image upload script, I can reference them thusly:
```
# In the image upload script

$upload_dir = $myServerFileDir."images/tank/"; 
$image_dir = $myServerFileDir."images/"; 
$web_dir = $myWebFileDir."images/"; 
```

Miscellaneous Tricks [top]

Communicating via the URL String
Often, as a user moves from one page to another, it might be advantageous to give them some feedback, especially if a redirect is involved. For example, if someone tries to access a protected page and is not logged in, we might redirect them to the login page. However, the reasons for the redirect are more obvious to the user if the login page knows the reason why they arrived there. Instead of storing the reason in the session, we can simply pass it along the URL string. For example, in our protected page, we might see:

if (!$isLoggedIn)
{ print "Location: login.php\n\n";
  exit;
}

However, this doesn't tell our "login.php" page anything. A user could have arrived at the same place by pressing a "Login" link and our script wouldn't know the difference. Thus we can pass a name-value that lets us know that the user tried to access a protected page but wasn't logged in by doing something like this:

if (!$isLoggedIn)
{ print "Location: login.php?err=notloggedin\n\n";
  exit;
}

In our login page, we can catch this case and put up an informative message in our HTML like such:

if ($_GET["err"]=="notloggedin")
{ print qq^<font color="#990000"><b>Please login to access this page</b></font> <p>^;
}

This is better than someone clicking on a protected link and going, "why am I at a login page?"

Storing the Most Important Data in the Session Object
For any site that has a membership, the most important piece of data is a member's user ID, which is usually their ID number (i.e. row number in the DB) in whatever table is used to store member information. This, at the very least, should be stored in the session object, because we can use it at any time to query the database to get any or all information about that user. However, if some applications on our site use other account information regularly, then it's often better to just grab this info at login and store it in our session object. That way, every time our script needs to access this information, we don't have to re-query the database to get it. So, during the login process, we can obtain any desired info during our check to see if this is a successful login. A successful login then allows us to store the desired info in the session object:

# Look for this information in the DB
$SqlStatement = "SELECT id, firstname, lastname, email, last_login FROM class06_member
  WHERE username='$usernameValueDB' AND password='$passwordValueDB' ";
  
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
  die("Error " . mysql_errno() . " : " . mysql_error());

if ($row = mysql_fetch_assoc($result))
{  
  # Successful login so set our logged in session parameters
  $_SESSION["userid"] = $row['id'];
  $_SESSION["firstname"] = $row['firstname'];
  $_SESSION["lastname"] = $row['lastname'];
  $_SESSION["email"] = $row['email'];
  $_SESSION["last_login"] = $row['last_login'];
  $_SESSION["logged-in"] = 1;
}

For example, if we look at our "Send an E-mail" example, we see that because the user is logged in, we can determine the name and e-mail of the user, and thus, can pre-populate certain values with this information. We can even hardcode these values if we like, such that the user must use the firstname, lastname, and email values that they submitted during registration for any e-mail correspondence through our site. In either case, we can take care of setting these values for them without having to go to the database because we have stored this account information in the session object.

PHP E-mail Example | Source Code

Using Last Login to Determine New Content
When a user logs in, we can get the time of their last login, store it in the session, and then update the value of their last login to be the current time. If we timestamp all content that's contributed to the site, then we can use their last login (i.e. the real one - the one stored in the session) and compare that to the timestamp of any new content to show the user what's changed on the site. For example, when they log in:

# Set our logged in session parameters
$_SESSION["last_login"] = $last_login;
      
# Update the last_login field
$SqlStatement = "UPDATE class06_member SET last_login=NOW() WHERE id=$userid ";
$result = mysql_query($SqlStatement,$connection);

We've taken care of updating our database, and now we can use the "last_login" variable in our session object to see what's new:

# Any new images?
$myLastLogin = $_SESSION["last_login"];
$SqlStatement = "SELECT id, filename FROM class06_image
  WHERE last_modified>='$myLastLogin' ORDER BY last_modified asc ";
$result = mysql_query($SqlStatement,$connection);

This can be done for any type of content that's timestamped upon its entry into the database (ie. upon INSERT), or upon modification (i.e. upon UPDATE).

Data Population Techniques [top]
Once you have a basic database structure in place, along with scripts that can display and manipulate this data, and control its interaction with the user, you may find yourself wondering how to actually get large quantities of data into your DB. At this point, the main method for INSERTing new data is either by creating a web page to input it by hand (laborious), or using phpMyAdmin (better).

While phpMyAdmin does have an "Import" tab that can theoretically take data files in CSV format and insert this data into a database table, this functionality is both poorly documented and poorly implemented, often leading to undesirable results in the imported data.

The easiest solution is to formulate mass quantities of SQL INSERT statements in bulk using a spreadsheet program such as Excel, and then executing them using phpMyAdmin "SQL" tab. If you look at the following Excel file, and screenshots of data, we can see how we might populate info for the US States:

class06-states.xls

Raw Data

Modified for SQL Input

So to implement data population in this fashion:

Gather rows of data in Excel
Find and replace any single quotes in the data with two single quotes
Add columns within the data to mimic the SQL INSERT syntax (as shown in the image above)
Copy and paste all Excel data into your favorite text editor
Find and replace all tab characters with nothing
Copy and paste into the SQL pane of phpMyAdmin and hit 'Go'

Alternatively, you can save these statements into a file, and use phpMyAdmin's Import tab to run these statements in the same fashion:

phpMyAdmin SQL Import Functionality

Related Resources [top]

PHP Header Function Reference

PHP Session Handling Functions

PHP Mail Function | Emailing in PHP - Reference

PHP File Upload Reference