A Charity Donation Recognition System Using PHP, MySQL, JavaScript And DOM

Recently asked on Yahoo! Answers:

Divide photo in 5,000 pieces with rollover text?
we are raising money and selling support tickets. To show our progress we are going to start with a black and white image split in 5,000 pieces then as each piece is sold we want to turn it into color.

When you roll over each piece the person who purchased that support ticket will have their name show up as well as a personal message.

any opinions on how this should be built? The admin section will all be managed manually, so we’ll just need a backend for the admin person to go in and make the changes manually.

This seemed an interesting challenge to me; not difficult, but with multiple steps that no beginner could hope to properly tackle on his own without some help.

This project calls on almost all the basic skills a competent Web developer should possess: Image editing; database design and simple queries; DOM manipulation; security and back-end systems, and therefore, it gives me my first real opportunity in quite some time to address a project in full scope.

As always, I’ll have a link to a working demo and code you can download at the end of this entry.

Step 1: Make The Images

Five thousand slices would be 50 columns x 100 rows; and about the smallest one of those squares could be and still be selectable by mouse is 10 pixels x 10 pixels. That means the image would have to be 500 pixels wide by 1,000 pixels tall, which is a really long image, and people might be discouraged to try to see it all.

However, because the same two small images can be used over and over again, load time wouldn’t be affected. So, all we really need to do here is make two images: a 10 pixel x 10 pixel image for “unsold” squares, and a different 10 pixel x 10 pixel image for “sold” squares.

Any old image-editing application can make a small GIF or JPG, so I won’t detail doing that here. I will, however, make one important note: The sold squares’ image name should be something appended to the unsold squares’ image name. For example, if you name the unsold image “square.gif,” the sold squares’ image name should be something like “square_sold.gif.”

Step 2: Database Design

We’re going to store the donor information in a MySQL database. We could use XML or even a text file to store the data, but because we need a server-side language, one way or another, to store this information; because PHP works very well with MySQL; and because most PHP-enabled hosts also provide MySQL, my feeling is that a database makes the most sense.

We’ll have two tables. One is going to store which squares have been sold, by using X and Y coordinates; the other is going to store donor information.

CREATE TABLE IF NOT EXISTS `php_charity_rollover` (
  `rollover_id` int(10) unsigned NOT NULL auto_increment,
  `rollover_x` int(10) unsigned NOT NULL,
  `rollover_y` int(10) unsigned NOT NULL,
  `donor_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`rollover_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `php_charity_donors` (
  `donor_id` int(10) unsigned NOT NULL auto_increment,
  `donor_name` varchar(150) NOT NULL,
  `donor_amount` float(6,2) unsigned NOT NULL,
  `donor_message` varchar(255) NOT NULL,
  PRIMARY KEY  (`donor_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

Notice that both tables have a donor_id column. This will act as a foreign key for the squares table, and we’ll join information about each sold square wherever there is a sold one.

I could have combined the two tables into one but that is bad design. The donor and the squares are interrelated, but they aren’t the same thing, and a database table should never describe more than one thing. For more information about decent database design and its importance, read “The Value Of Relational Databases: A Case Study.”

Step 3: The Display PHP

We’re going to start on the display page by defining two constants: The number of rows and columns in the image table. Then, we’re going to connect to the database server and select the database that has our tables.

define('IMG_ROWS', 5);
define('IMG_COLS', 5);

$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "databasename";

$link = mysql_connect($dbhost, $dbuser, $dbpass) or die('Cannot connect to database server');
mysql_select_db($dbname) or die('Cannot select database');

Our SELECT statement will get records for the sold squares and the donors who purchased them. We’ll do that via a LEFT JOIN. MySQL didn’t support INNER JOINs until version 4, and LEFT JOINs are far more efficient in most RDBMS, including MySQL.

$sql = "SELECT r.rollover_id, r.rollover_x, r.rollover_y, d.donor_id, d.donor_name, d.donor_amount, d.donor_message FROM php_charity_rollover AS r LEFT JOIN php_charity_donors d ON r.donor_id = d.donor_id ORDER BY r.rollover_x, r.rollover_y";
$rs = mysql_query($sql) or die('Cannot execute query');

Next, I want to read all the records into an array. I am going to be going through these records for every single square in the image, and because I don’t want to continuously work with the results set I got from the database server, it makes sense to simply go through the records once and add them to a static array.

$items = array();
while($row = mysql_fetch_assoc($rs)) {
	$items[] = $row;
}

We’re ready to start outputting data. We’re going to begin by declaring a string variable that contains an opening TABLE tag.

$table = "<table id="rollover" cellspacing="0" cellpadding="1">n";

Now, the tricky part. We’re going to have a pair of nested for loops and a nested foreach loop do all the work for us.

  • The first for loop iterates through the rows (y axis) of the table. Each time it does that, we will output opening and closing TR tags.
  • The second for loop iterates through the columns (x axis) of the table. Each time it does that, it will output opening and closing TD tags, and anticipate that the square is unsold by outputting the unsold square image as the source of an IMG tag inside the current table cell.
  • We will then go through a foreach statement that iterates through all the sold squares we got from the database.
    • If the X and Y coordinates of the sold square match the X and Y values of the for loops, we know the square is sold, so we append, to the IMG source tag, the additional text that is part of the sold square’s image name.
    • We also output a mouseover event to a function named displayDetails, which takes as its arguments the name of the donor, the amount he donated and the message he wants to send.
    • We also output a mouseout event that clears the detail display when someone moves off the sold square.
for($y = 1; $y <= IMG_ROWS; $y++) {
	$table .= "t<tr>n";
	for($x = 1; $x <= IMG_COLS; $x++) {
		$detail = "";
		$table .= "tt<td><img id="r" . $y . "_c" . $x . "" src="block";
		foreach($items as $item) {
			if($item['rollover_y'] == $y && $item['rollover_x'] == $x) {
				$table .= "_reverse";
				$detail = " onmouseover="displayDetail('" . str_replace("'", "'", $item['donor_name']) . "', '" . number_format($item['donor_amount'], 2) . "', '" . str_replace("'", "'", nl2br($item['donor_message'])) . "')" onmouseout="HideContent('donationDetail')"";
				break;
			}
		}
		$table .= ".jpg"";
		$table .= $detail;
		$table .= " alt="" /></td>n";
	}
	$table .= "t</tr>n";
}

To complete the work, we close the TABLE tag:

$table .= "</table>n";

Step 4: The XHTML

Our XHTML is very simple. We need only to echo out the $table variable wherever we want the table to appear. We also need to declare the DIV that will show donor detail information in the mouseover.

<?php echo $table; ?>
<div id="donationDetail"></div>

Step 5: The CSS

We need some CSS to make things appear properly, especially the donor details DIV. Specifically, we need to set the position of the DIV to be absolute, which will allow us to position the DIV at a specific place on the page, over other elements; we need to give it a background, so the items behind it won’t show through; and we need to hide it until it’s ready for use.

I’m also going to make sure that none of the tables, cells or images have borders, which will prevent them from looking bad.

table, td, img {
	border: none;
}

div#donationDetail {
	display: none;
	position: absolute;
	width: 200px;
	background: #fff;
	color: #000;
	font: normal normal normal 10px verdana,arial,helvetica,sans-serif;
	padding: 5px;
	border: 1px solid #000;
	margin: 10px;
}

Step 6: The ToolTip JavaScript

DIVs that appear when you mouse over an object are commonly called ToolTips. I’m going to use someone else’s ToolTip display code, because I wrote this solution late at night, and thus was feeling lazy; the code I got works fine; and I see no need to reinvent the wheel.

The code I am lifting comes from the article “Floating Layer At Cursor Position” on willmaster.com. I simply copied the code to a separate JavaScript file and made reference to it from my PHP page.

<script type="text/javascript" src="displaydiv.js"></script>

Even though we have someone else’s way of showing the detail DIV, we still need to populate it with the relevant information. That’s the purpose behind the displayDetail() function we will now write, which takes, as arguments, the donor’s name, the amount he donated and the message he wants.

We simply build an HTML string, assign that string to the donationDetail DIV we declared in our page, and then call the ShowContent function that is part of the ToolTip code we’ve lifted.

function displayDetail(name, amount, message) {
	var theText = '<strong>Donor Name:</strong> ' + name + '<br />';
	theText += '<strong>Amount:</strong> $' + amount + '<br />';
	theText += '<strong>Donor Message:</strong> ' + message;
	document.getElementById('donationDetail').innerHTML = theText;
	ShowContent('donationDetail');
}

And it’s that simple. You can see a working demo here:

http://www.dougv.com/demo/php_charity_rollover/twocolor.php

An Alternative Approach: Using A Photograph

I confess to not having read this question closely enough when I started — then again, neither did the other people who answered it — and starting to design this using an actual photograph, rather than colored squares. So, since I’ve put the work in, I might as well discuss the minor differences between that approach and the colored squares approach, above.

First, you need a program to slice the image. You can do that with Photoshop and other image-editing programs, but I used Fireworks, which is specifically designed for this kind of work.

You can download a trial version of Fireworks for free from Adobe. The Help section built into it will explain how to make slices, and I have included both the PNG file I made in Fireworks, plus the slice output HTML and images Fireworks generated, in the download code.

However you manage to slice you image, it’s important that two things are true of each: The image name must clearly indicate the row (y axis) and column (x axis) position of each slice, and the sold image must share the same syntax as the unsold image, but with an add-on phrase, as I noted earlier.

For example, my slices are named rollover_r1_c1.jpg for an unsold upper-left-hand corner, and rollover_r1_c1_f2.jpg for a sold upper-left-hand corner.

With those images on hand, we need only modify the PHP for loops to use the new naming convention. Otherwise, the code is exactly the same.

for($y = 1; $y <= IMG_ROWS; $y++) {
	$table .= "t<tr>n";
	for($x = 1; $x <= IMG_COLS; $x++) {
		$detail = "";
		$table .= "tt<td><img id="rollover_r" . $y . "_c" . $x . "" src="rollover_r" . $y . "_c" . $x;
		foreach($items as $item) {
			if($item['rollover_y'] == $y && $item['rollover_x'] == $x) {
				$table .= "_f2";
				$detail = " onmouseover="displayDetail('" . str_replace("'", "'", $item['donor_name']) . "', '" . number_format($item['donor_amount'], 2) . "', '" . str_replace("'", "'", nl2br($item['donor_message'])) . "')" onmouseout="HideContent('donationDetail')"";
				break;
			}
		}
		$table .= ".jpg"";
		$table .= $detail;
		$table .= " alt="" /></td>n";
	}
	$table .= "t</tr>n";
}

You can see a working demo of this approach here:

http://www.dougv.com/demo/php_charity_rollover/index.php

The primary benefit of this approach is that it’s aesthetic. The primary drawback is that it loads much slower, because each image is distinct; as such, for very large numbers of squares, this isn’t a good approach.

Step 7: The Administration Login Page

There needs to be a basic back-end for this application, which allows the Webmaster to enter sold squares as they are sold. And because we don’t want this system to be exploited, we need a basic login system that prevents unauthorized people from entering sold squares.

The login system is simple. We’re going to open a PHP session and hard-code in a user name and password.

We check the input of a form on the same page, seeing if the user name and password supplied match our hard-coded values. If it does, we set a session variable to true, then we redirect to the page that enters sold squares. If it does not, we echo out the appropriate error message.

PHP sessions aren’t very secure and this system does not attempt to time out on multiple submissions. Overall, on a scale of 1 to 10 for security, it’s about a 6. It should be enough if you run a small Web site and you do not openly publicize the URL of this script.

session_start();

$username = "banana";
$password = "12orange34";

if(isset($_POST['submit'])) {
	if(trim($_POST['username']) == "") {
		$message = "<p><strong>Please provide a user name!</strong></p>n";
	}
	else if(trim($_POST['password']) == "") {
		$message = "<p><strong>Please provide a password!</strong></p>n";
	}
	else {
		if($_POST['username'] == $username && $_POST['password'] == $password) {
			$_SESSION['login'] = 'true';
			header("Location: admin.php");
			exit;
		}
		else {
			$message = "<p><strong>Sorry, the user name and password you entered were incorrect.</strong> Please try again. Note that your user name and password are case-sensitive.</p>n";
		}
	}
}

The form itself is very simple:

<form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<p>
		<label for="username">User Name: </label>
		<input name="username" type="text" id="username" size="20" maxlength="20" value="<?php echo $_POST['username']; ?>" />
	</p>
	<p>
		<label for="password">Password: </label>
		<input name="password" type="password" id="password" size="20" maxlength="20" />
	</p>
	<p>
		<input name="submit" type="submit" id="submit" value="Submit" />
	</p>
</form>

You can see a working example of the login here:

http://www.dougv.com/demo/php_charity_rollover/login.php

Step 8: The Sold Square Insert Page

The sold square insert page first checks to make sure there is a session variable set and that it contains the proper value. If not, the user is sent back to the login page; if so, the form is shown.

session_start();
if(!isset($_SESSION['login']) || $_SESSION['login'] != 'true') {
	header("Location: login.php");
}

The form itself is straighforward. We ask for the X and Y coordinates of the sold square, the name of the donor, the amount he donated and the message he wants to send.

<form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" onsubmit="return checkForm()">
	<p>
		<label for="x">Image Column: </label>
		<input name="x" type="text" id="x" value="<?php echo $_POST['x']; ?>" size="5" maxlength="5" />
	</p>
	<p>
		<label for="y">Image Row: </label>
		<input name="y" type="text" id="y" value="<?php echo $_POST['y']; ?>" size="5" maxlength="5" />
	</p>
	<p>
		<label for="name">Donor Name: </label>
		<input name="name" type="text" id="name" value="<?php echo $_POST['name']; ?>" size="50" maxlength="150" />
	</p>
	<p>
		<label for="amount">Donation Amount: </label>
		<input name="amount" type="text" id="amount" value="<?php echo $_POST['amount']; ?>" size="7" maxlength="6" />
	</p>
	<p>
		<label for="message">Donor Message: </label>
		<pre name="message" cols="40" rows="5" id="message"><?php echo $_POST['message']; ?>
	</p>
	<p>
		<input name="submit" type="submit" id="submit" value="Submit" />
	</p>
</form>

We treat all fields, except for the message, as required. I have a small JavaScript form validation script to help the person submitting the form:

function checkForm() {
	if(document.form1.x.value == "" || isNaN(document.form1.x.value)) {
		alert('Please enter a proper image column value!');
		document.form1.x.focus();
		return false;
	}

	if(document.form1.y.value == "" || isNaN(document.form1.y.value)) {
		alert('Please enter a proper image row value!');
		document.form1.y.focus();
		return false;
	}

	if(document.form1.name.value == "") {
		alert('Please enter a donor name!');
		document.form1.name.focus();
		return false;
	}

	if(document.form1.amount.value == "" || isNaN(document.form1.amount.value)) {
		alert('Please enter a proper donation amount!');
		document.form1.amount.focus();
		return false;
	}

	return true;
}

The script to process the input is simple. It first checks to ensure that all required fields are properly formatted.

If so, the script first inserts the donor info, then gets from the donor info table the record id of the inserted record. Then, it inserts into the squares table the location and donor id of the sold square.

Note that this is very basic, and does not ensure that the square has not been previously sold. If a square has been sold more than once, the square that was least recently sold is the one that will appear on the page.

if(isset($_POST['submit'])) {
	if(trim($_POST['name']) == "") {
		$message = "<p><strong>Please enter a donor name!</strong></p>n";
	}
	else if(!preg_match('/^[0-9.]{1,6}$/', $_POST['amount'])) {
		$message = "<p><strong>Please enter a properly formatted donation amount</strong></p>n";
	}
	else if(!preg_match('/^[0-9]{1,5}$/', $_POST['x'])) {
		$message = "<p><strong>Please enter a properly formatted image column!</strong></p>n";
	}
	else if(!preg_match('/^[0-9]{1,5}$/', $_POST['y'])) {
		$message = "<p><strong>Please enter a properly formatted image row!</strong></p>n";
	}
	else {
		$name = mysql_escape_string($_POST['name']);
		$message = mysql_escape_string($_POST['message']);

		$dbhost = "localhost";
		$dbuser = "username";
		$dbpass = "password";
		$dbname = "databasename";

		$link = mysql_connect($dbhost, $dbuser, $dbpass) or die('Cannot connect to database server');
		mysql_select_db($dbname) or die('Cannot select database');

		$sql = "INSERT INTO php_charity_donors (donor_name, donor_amount, donor_message) VALUES ('$name', $_POST[amount], '$message')";
		$rs = mysql_query($sql) or die('Cannot insert donor information' . mysql_error());
		$id = mysql_insert_id();

		$sql = "INSERT INTO php_charity_rollover(rollover_x, rollover_y, donor_id) VALUES($_POST[x], $_POST[y], $id)";
		$rs = mysql_query($sql) or die('Cannot insert image donation square info' . mysql_error());

		$message = "<p><strong>Donation added.</strong></p>n";
	}
}

And that’s all there is to it. You can download all the code associated with this project, including images and SQL, here:

A Charity Donation Recognition System Using PHP, MySQL, JavaScript And DOM Demo Code

I distribute all code under the GNU GPL version 3.

3 thoughts on “A Charity Donation Recognition System Using PHP, MySQL, JavaScript And DOM

  1. Jesse

    Doug you’re a legend!

    Thank you so much for tackling this. Could you contact me i’d like to hire you to build this on our server for us.

    Thanks again,

    Jesse

  2. anup

    this is awesome but i need a simple charity system in donor donate and as admin i see how many people donated money

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *