Wish Submission Backend
This is non-sensitive code from a wish submissions backend I wrote using PHP and MySQL.
[edit] 1 Wishes Database SQL
DROP TABLE IF EXISTS `wish_entries`; -- This table holds the submitted wishes. CREATE TABLE `wish_entries` ( `entry_id` INT NOT NULL AUTO_INCREMENT COMMENT 'The entry ID to uniquely identify the entry in the database.', `name` VARCHAR(80) NULL COMMENT 'Name of the wisher.', `wish` VARCHAR(80) NOT NULL COMMENT 'The wish itself.', `email` varchar(255) NOT NULL COMMENT 'Email entered.', `phone_number` varchar(12) NOT NULL COMMENT 'Phone number entry.', `contact_email` TINYINT(1) COMMENT 'Denotes whether they prefer to be contacted by email--1 if yes', `submit_date` DATETIME NOT NULL COMMENT 'The date of the entry\'s submission.', `featured` TINYINT(1) DEFAULT 0 COMMENT 'Marked when the wish should be considered as featured.', PRIMARY KEY (`entry_id`) ) ENGINE = innodb COMMENT 'The database table for all the entries.'; ALTER TABLE `wish_entries` ADD INDEX(`submit_date`); ALTER TABLE `wish_entries` ADD INDEX(`email`, `submit_date`); ALTER TABLE `wish_entries` ADD INDEX(`phone_number`, `submit_date`);
[edit] 2 Spreadsheet Admin Page
A simple page that generates a spreadsheet of submitted wishes.
<?php require_once( "../WishFunctions.php" ); $DB_CONNECTION = connectDB(); if( !$DB_CONNECTION ) { print "No database connection!<br />"; exit(0); } # set these to default be today $start_date = date("Y-m-d"); $end_date = date("Y-m-d"); $title = "Generate Spreadsheet"; $content = <<<HTML <form name="generate_spreadsheet" method="get" action="spreadsheet.php"> <p>Enter dates as YYYY-MM-DD.</p> <label for="start_date">Start date</label>: <input type="text" name="start_date" id="start_date" size="10" value="$start_date" /><br /> <label for="end_date">End date</label>: <input type="text" name="end_date" id="end_date" size="10" value="$end_date" /><br /> <input type="submit" name="submit" value="Submit" /> </form> HTML; # make the spreadsheet if a form has been submitted # with the given start and end dates if( $_GET["submit"] ) { if( $_GET["start_date"] ) { $start_date = $_GET["start_date"]; $start_date = validateDate( $start_date ); } if( $_GET["end_date"] ) { $end_date = $_GET["end_date"]; $end_date = validateDate( $end_date ); } header("Content-Type: text/plain"); makeSpreadsheet( $start_date, $end_date ); } else { servePage( $title, $content ); } closeDB(); ?>
[edit] 3 Backend Functions
The shared functions used to drive the site.
<?php // These are required as libraries to send the email require_once('Mail.php'); require_once('Mail/mime.php'); // The file containing all the settings require_once("Settings.php"); // Database error messages are added to this array $DB_ERRORS = array(); // Is true when there are database errors $DB_ERROR_FLAG = false; // The connection to the database $DB_CONNECTION; // This function must be called in order to get the database // connection handle on any page function connectDB( ) { global $db_username, $db_password, $db_name, $db_host, $DEBUG_MODE; $DB_CONNECTION = mysql_connect($db_host, $db_username, $db_password); if( !$DB_CONNECTION and $DEBUG_MODE ) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($db_name, $DB_CONNECTION); if( !$db_selected and $DEBUG_MODE ) { die("Could not use database $db_name: " . mysql_error()); } return $DB_CONNECTION; } // This function closes the database connection function closeDB( ) { if( $DB_CONNECTION ) { mysql_close($DB_CONNECTION); return true; } return false; } // This function adds a database error to the database function addDBError( $error ) { global $DB_ERRORS; $DB_ERROR_FLAG = true; array_push( $DB_ERRORS, $error ); } function checkDBErrors( ) { return $DB_ERROR_FLAG; } // This function prints out the database errors added to the error array function printDBErrors( ) { global $DB_ERRORS; print "<h2>Database Errors</h2>"; if( is_array($DB_ERRORS) ) { print "<ul>"; foreach( $DB_ERRORS as $error ) { print "<li>" . $error . "</li>"; } print "</ul>"; } } // This function submits the entry to the database // Use checkEntryInvalid before using function submitEntry( $name, $email, $phone_number, $wish, $contact_email ) { global $DB_CONNECTION; if( !$DB_CONNECTION ) { return NULL; } $name = mysql_real_escape_string($name); $email = mysql_real_escape_string($email); $wish = mysql_real_escape_string($wish); $name = strtr($name, "\t\n", " "); $wish = strtr($wish, "\t\n", " "); $statement = "INSERT INTO `wish_entries`"; $statement .= " ( `wish`, `name`, `email`, `phone_number`, `contact_email`, `submit_date`, `featured` )"; $statement .= " VALUES ('$wish', '$name', '$email', '$phone_number', '$contact_email', NOW(), 0)"; $result = runQuery( $statement ); if( $result ) { return true; } return false; } // Uses Mail_MIME to properly send an email with the given information function sendEntryEmail( $name, $wish, $email_address, $phone ) { global $email_subject, $email_message_text, $email_message_html; global $email_sent_from; // Replace message placeholders with entry values $placeholders = array( "#NAME#", "#WISH#", "#EMAIL#", "#PHONE#" ); $replacements = array( $name, $wish, $email_address, $phone ); $text_body = str_replace($placeholders, $replacements, $email_message_text); $text_html = str_replace($placeholders, $replacements, $email_message_html); $email_subject = str_replace($placeholders, $replacements, $email_subject); // Set up the email $mime_email = new Mail_mime(); $extraheaders['Subject'] = $email_subject; // NOTE: "S. Claus <claus@n-pole.com>" apparently doesn't work // because of a bug $extraheaders['From'] = $email_sent_from; $extraheaders['X-Mailer']= 'PHP/' . phpversion(); # Setting the text body -- this is what someone sees when their # client does not support the HTML message $mime_email->setTXTBody($text_body); # Setting the HTML body -- this is what someone sees when their # client does support HTML $mime_email->setHTMLBody($text_html); // Send the email $body = $mime_email->get(); $headers = $mime_email->headers($extraheaders); $mail = Mail::factory('mail'); $result = $mail->send($email_address, $headers, $body); if (PEAR::isError($result)) { return false; } else { return true; } } // Tries to make a valid phone number out of a string // Strips everything but the digits, makes sure there are 10 // And function makeValidPhoneNumber( $phone_number ) { // Strip out everythng that is not a digit $numbers_only = preg_replace( '/[^0-9]*/', '', $phone_number ); // If there is a leading 1, strip it if( strlen($numbers_only) == 11 && substr($phone_number, 0, 1) == "1") { $numbers_only = substr($numbers_only, 1 ); } // Valid phone numbers have 10 digits; // replace with the right format XXX-XXX-XXXX and return if( strlen($numbers_only) != 10 ) { return false; } else { return preg_replace( '/(\d{3})(\d{3})(\d{4})/', '$1-$2-$3', $numbers_only ); } } # Returns false if an entry has any disqualifications function checkEntryInvalid( $name, $email, $phone_number, $wish ) { global $DB_CONNECTION, $max_wish_length; global $message_invalid_email, $message_invalid_phone; global $message_too_long, $message_limit_today, $message_error_no_wish; if( !$DB_CONNECTION ) { return NULL; } if( strlen($wish) == 0 ) { return $message_error_no_wish; } # check that phone number is valid if( !preg_match("/^[0-9]{3}\-[0-9]{3}\-[0-9]{4}$/", $phone_number)) { return $message_invalid_phone; } # check that email is valid if( !preg_match("/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i", $email)) { return $message_invalid_email; } if( strlen( $wish ) > $max_wish_length ) { return $message_limit_today; } $email = mysql_real_escape_string($email); $phone = mysql_real_escape_string($phone_number); # check with the database -- email and phone number # should not exist for the past day $statement = "SELECT `entry_id` FROM `wish_entries` WHERE"; $statement .= " `submit_date` >= CURDATE() AND"; $statement .= " (`email` = '$email' OR `phone_number` = '$phone_number')"; $statement .= " LIMIT 1;"; $result = runQuery( $statement ); $num_results = mysql_num_rows($result); if( $num_results > 0 ) { return $message_limit_today; } return false; } // Gets wishes marked as featured. Currently not in used. function getFeaturedWishes($num_wishes) { global $DB_CONNECTION; if( !$DB_CONNECTION ) { return NULL; } if( !$num_wishes ) { $num_wishes = 20; } // make sure input valid if( !is_numeric($num_wishes) ) { return false; } $statement = "SELECT `wish` FROM `wish_entries`"; $statement .= " WHERE `featured` = 1"; $statement .= " ORDER BY `submit_date` ASC LIMIT $num_wishes"; $result = runQuery( $statement ); while( $row = mysql_fetch_row($result )) { $featured_wishes[] = $row[0]; } return $featured_wishes; } /* Counts the total number of wishes */ function getWishCount() { global $DB_CONNECTION, $hour_offset; if( !$DB_CONNECTION ) { return NULL; } $statement = "SELECT COUNT(*) FROM `wish_entries`"; $statement .= " WHERE DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) >= '2008-12-01'"; $statement .= " AND DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) < '2009-01-01'"; $result = runQuery( $statement ); $row = mysql_fetch_row($result); $num_wishes = $row[0]; return $num_wishes; } /* Gets the total number of wishes wanting to be contacted by email */ function getWishEmailCount() { global $DB_CONNECTION, $hour_offset; if( !$DB_CONNECTION ) { return NULL; } $statement = "SELECT COUNT(*) FROM `wish_entries`"; $statement .= " WHERE DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) >= '2008-12-01'"; $statement .= " AND DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) < '2009-01-01'"; $statement .= " AND `contact_email` = 1"; $result = runQuery( $statement ); $row = mysql_fetch_row($result); $num_wishes = $row[0]; return $num_wishes; } // Gets wishes only, based on most recent function getWishes( $limit, $start, $featured ) { global $DB_CONNECTION, $hour_offset; if( !$DB_CONNECTION ) { return NULL; } if( !$limit ) { $limit = 20; } if( !$start ) { $start = 0; } // make sure all input valid if( !is_numeric($limit) or !is_numeric($start) or !is_numeric($featured) ) { return false; } $statement = "SELECT `entry_id`, `name`, `wish`, `email`, `phone_number`,"; $statement .= " `contact_email`, DATE_ADD(`submit_date`, INTERVAL $hour_offset HOUR) AS `display_date`"; $statement .= " FROM `wish_entries`"; if( $featured ) { $statement .= " WHERE `featured` = 1"; } $statement .= " ORDER BY `submit_date` DESC"; $statement .= " LIMIT $limit OFFSET $start "; $result = runQuery( $statement ); return $result; } // Makes sure a date is formatted in YYYY-MM-DD compatible form. // Most basic error checking only -- needs eight digets and then formatted // appropriately. function validateDate( $date ) { // Strip out everything that is not a digit $numbers_only = preg_replace( '/[^0-9]/', '', $date ); // Make into the proper date format if( strlen($numbers_only) != 8 ) { return false; } else { return preg_replace( '/(\d{4})(\d{2})(\d{2})/', '$1-$2-$3', $numbers_only ); } } // Makes a spreadsheet of wishes between the start date and end date // If one date is missing, just gets everything before the end // or after the start // If both are missing, attempts to give you the entire wish database function makeSpreadsheet( $start_date, $end_date ) { global $DB_CONNECTION, $wish_spreadsheet_num, $hour_offset; if( !$DB_CONNECTION ) { return NULL; } $limit = $wish_spreadsheet_num; $offset = 0; $still_results = true; $filename = "wish_entries"; $start_date = validateDate( $start_date ); $end_date = validateDate( $end_date ); if( $start_date ) { $filename .= "_start_$start_date"; } if( $end_date && $end_date != $start_date) { $filename .= "_end_$end_date"; } $filename .= ".txt"; // print Apache headers header("Content-Disposition: attachment; filename=" . urlencode($filename)); header("Content-Type: text/plain "); header("ContentType: application/ms-excel"); header("Content-Description: File Transfer"); // print table headers print join("\t", array('entry_id', 'name', 'wish', 'email', 'phone_number', 'contact_email', 'display_date')) . "\n"; $start_date = mysql_real_escape_string($start_date); $end_date = mysql_real_escape_string($end_date); // Cycle through in chunks so as not to overload the database while( $still_results ) { $statement = "SELECT `entry_id`, `name`, `wish`, `email`, `phone_number`,"; $statement .= " `contact_email`, DATE_FORMAT(ADDDATE(`submit_date`, INTERVAL $hour_offset HOUR), '%Y-%m-%d %h:%i:%s %p') as `display_date`"; $statement .= " FROM `wish_entries`"; if( $start_date or $end_date ) { $statement .= " WHERE"; if( $start_date ) { $statement .= " DATE_FORMAT(ADDDATE(`submit_date`, INTERVAL $hour_offset HOUR), '%Y-%m-%d') >= '$start_date'"; } if( $start_date and $end_date ) { $statement .= " AND"; } if( $end_date ) { $statement .= " DATE_FORMAT(ADDDATE(`submit_date`, INTERVAL $hour_offset HOUR), '%Y-%m-%d') <= '$end_date'"; } } $statement .= " ORDER BY `submit_date` DESC"; $statement .= " LIMIT $limit OFFSET $offset"; $result = runQuery( $statement ); if( $result ) { $num_results = mysql_num_rows($result); while( $row = mysql_fetch_row($result) ) { print stripslashes(join( "\t", $row )) . "\n"; } if( $num_results < $limit ) { $still_results = false; } else { $offset += $limit; } mysql_free_result($result); } else { $still_results = false; } } } // Makes a spreadsheet of number statistics function makeStatsSpreadsheet( $type ) { $filename = "numsubmissions_spreadsheet"; if( $type == "days" ) { $timedisplay = "%Y-%m-%d"; $headers = "Date\tCount\n"; $filename = $filename . "_days"; } else { $timedisplay = "%Y-%m-%d %h:00 %p"; $headers = "Hour\tCount\n"; $filename = $filename . "_hours"; } $filename = $filename . ".txt"; header("Content-Disposition: attachment; filename=" . urlencode($filename)); header("Content-Type: text/plain "); header("ContentType: application/ms-excel"); header("Content-Description: File Transfer"); print $headers; $result = statsTableQuery( $timedisplay ); if( $result ) { while( $row = mysql_fetch_row($result) ) { print join( "\t", $row ) . "\n"; } mysql_free_result( $result ); } else { return false; } } // Prints out wish submission statistics function displayStatsTable( $type ) { if( $type == "days" ) { $timedisplay = "%Y-%m-%d"; print "<h3>Table of Submissions per Day</h3>"; print "<table border='1' cellspacing='0' cellpadding='3'>\n"; print "<tr><th>Date</th><th>Count</th></tr>\n"; } else { $timedisplay = "%Y-%m-%d %h:00 %p"; print "<h3>Table of Submissions per Hour</h3>"; print "<table border='1' cellspacing='0' cellpadding='3'>\n"; print "<tr><th>Hour</th><th>Count</th></tr>\n"; } $result = statsTableQuery( $timedisplay ); if( $result ) { while( $row = mysql_fetch_row($result) ) { print "<tr>"; foreach ($row as $field) { print "<td>" . $field . "</td>"; } print "</tr>\n"; } print "</table>"; mysql_free_result( $result ); } else { return false; } } // Gets number of wishes submitted based on a given time display format // So, for a day it would want %Y-%m-%d // Per hour it would want %Y-%m-%d %h:00 %p function statsTableQuery( $timedisplay ) { global $hour_offset; $statement = "SELECT DATE_FORMAT(DATE_ADD(submit_date, INTERVAL $hour_offset HOUR), '$timedisplay') AS date, COUNT(*) AS count"; $statement .= " FROM `wish_entries`"; $statement .= " WHERE DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) >= '2008-12-01'"; $statement .= " AND DATE_ADD(submit_date, INTERVAL $hour_offset HOUR) < '2009-01-01'"; $statement .= " GROUP BY date ORDER BY `submit_date` DESC"; $result = runQuery( $statement ); return $result; } // Deletes all entries with a given email function deleteByEmail( $email ) { $email = mysql_real_escape_string($email); $statement = "DELETE FROM `wish_entries` where email='$email'"; return runQuery($statement); } // Deletes all entries with a given name function deleteByPhone( $phone ) { $phone = mysql_real_escape_string($phone); $statement = "DELETE FROM `wish_entries` where phone_number='$phone'"; return runQuery($statement); } /** Runs a query or array of queries on the database. All errors added to $DB_ERRORS array for later perusal. If the query is an array, will run each query and put the result into an array. If the result fails, then will put NULL into that spot instead. If the query is a single, will run it. If the query contains "SELECT" and "LIMIT 1", will return the associative array of the row result. Otherwise, returns the result. */ function runQuery( $query ) { $results = array(); /* If the query is an array, run each member. */ if( is_array( $query )) { foreach( $query as $q ) { #print "<p>Running query: $q</p>"; $result = mysql_query( $q ); /* Record the error and push null. */ if( !$result ) { addDBError( "MYSQL ERROR " . mysql_errno() . ": " . mysql_error() . " (SQL: $q )" ); array_push( $results, NULL ); } else { array_push( $results, $result ); } } return $results; } /* Run if the query is a string. */ else { $result = mysql_query( $query ); if( !$result ) { addDBError( "MYSQL ERROR " . mysql_errno() . ": " . mysql_error() . " (SQL: $query )" ); return NULL; } else { /* If the LIMIT 1 clause exists in a SELECT query, return the associative array of the result. */ if( strstr($query, "SELECT") && strstr( $query, "LIMIT 1 " ) ) { $array = mysql_fetch_assoc( $result ); mysql_free_result( $result ); return $array; } /* Otherwise return the entire result. */ else { return $result; } } } } // Very very simple page server for simple admin pages function servePage( $title, $content ) { print "<head><title>$title</title></head><html><h2>$title</h2><p>$content</p></html>"; exit(0); } ?>