Building a Results Table with Export to CSV Option

Posted on June 27, 2011 by admin

This is an extension to this post:
Dynamic Image Loading with jQuery & PHP

Ok, so the problem I needed to solve for this one was to build a dynamic results table from SQL and display the results in an ordered html table with an option to export that data into a CSV file format.

There are some “business” rules in the javascript that we use to determine how the medals are awarded. The code automatically determines medal placement based on what place the athlete finished in and whether or not the athlete is from Arkansas (In State).

There are four sections to this solution. The HTML page, the Javascript code, the CSS styling and finally, the server side PHP code to query the DB.

Here is the HTML:




	
		
		







        
        
        
        
        
        
        
0)
{
?>





	












View Results




Pick Event

























Event
Name
Bib
Age Group
In State?
Start
End
Elapsed
Avg
Place
Medal

So the HTML page has some links to other javascript files. The notable one here is the results.js file which handles all of the building of the html table and also has some rules for how the table will be populated and displayed. The button for the Export to CSV has a Submit action and POSTS to a PHP page (getCSV.php), which will take the generated CSV structure and render to the browser (which causes the Open/Save dialog).

Here is the javascript code for the results.js file

// Server Side Ajax Path
var ajax_url = "ajax/finalize_ajax.php";

function init(eventid)
{
	// Event Binding
	bindEvents();

	// Populate the active events
	// Located in common.js
	populateEvents();

	// Hide the Table Div Initially
	$("#results_display").hide();

	// Disable the Export Button
	$("#btnExport").attr('disabled', 'disabled');

	if (eventid != "0")
	{
		$("#cmb_events").val(eventid);
		populateAgeGroupedResults();
	}

}

function bindEvents()
{
	$("#btnViewAgeGrouped").bind("click",function() {
		populateAgeGroupedResults();
	});

	$("#btnViewOverall").bind("click",function() {
		populateOverallResults();
	});
}

function populateAgeGroupedResults()
{
	// Fade out the Table Div for effect
    //$("#results_display").fadeOut("fast");

	var selevent = $("#cmb_events").val();

	$("#tblResults").find("tr:gt(0)").remove();

	var curevent = $('#cmb_events').val();
	mycmd = '?cmd=getresults&eventid=' + selevent;
	var markup = "";

	$.ajax({
		   url:		results_ajax_url + mycmd,
		   success:	function(data)
		   {
				var mydata = eval(data);
				var curevent;
				var lastevent;
				var curagegroup;
				var lastagegroup;
				var grouppos = 1;
				var medal;
				var instatecount = 0;
				var instate = false;
				var elapsedhour;
				var elapsedminute;
				var elapsedsecond;
				var avgspeed;

				var distancearray;
				var distance;

				for (var i=0;i

";
						markup += "
" + mydata[i].AthleteName + "

";
						markup += "
" + mydata[i].BibNumber + "

";
						markup += "
" + mydata[i].AgeGroup + "

";
						markup += "
" + mydata[i].InState + "

";
						markup += "
" + mydata[i].StartTime + "

";
						markup += "
" + mydata[i].FinishTime + "

";
						markup += "
" + mydata[i].ElapsedTime + "

";
						markup += "
" + avgspeed + "

";
						markup += "
" + grouppos + "

";

						medal = " ";

						// We only care about the first 3 in-state competitors
						// as far as medals go
						if (instatecount < 4)
						{
						  /*
						  The grouppos is used for the overall placement of the athlete
						  The rule is that the top 3 in-state athletes get Gold, Silver, Bronze
						  Out of state athletes can only get a medal if they finish in
                                                  the top 3, but cannot take a medal placement from an
                                                  in-state athlete
					         */

							switch (instatecount)
							{
								case 0:
									if (grouppos == 1)
									{
										medal = "Gold";
									}
									else if (grouppos == 2)
									{
										medal = "Silver";
									}
									else if (grouppos == 3)
									{
										medal = "Bronze";
									}
								break;
								case 1:
									if (instate)
									{
										// 1st Instate always gets Gold
										medal = "Gold";
									}
									else if (grouppos == 1)
									{
										medal = "Gold";
									}
									else if (grouppos == 2)
									{
										medal = "Silver";
									}
									else if (grouppos == 3)
									{
										medal = "Bronze";
									}
								break;
								case 2:

									if (instate)
									{
										// 2nd Instate always gets Silver
										medal = "Silver";
									}
									else if (grouppos == 1)
									{
										medal = "Gold";
									}
									else if (grouppos == 2)
									{
										medal = "Silver";
									}
									else if (grouppos == 3)
									{
										medal = "Bronze";
									}
								break;
								case 3:
									if (instate)
									{
										// 3rd Instate always gets Bronze
										medal = "Bronze";
									}
									else if (grouppos == 1)
									{
										medal = "Gold";
									}
									else if (grouppos == 2)
									{
										medal = "Silver";
									}
									else if (grouppos == 3)
									{
										medal = "Bronze";
									}
								break;
							}
						}
						else
						{
							medal = " "; // No Medal
						}

						markup += "
" + medal + "

";
						markup += "

";

						grouppos ++;
						lastagegroup = curagegroup;

				}

				// Empty our container (the table body)
				$("#tbodyResults").empty();

                // Append the HTML rows
				$('#tbodyResults').append(markup);

				// Show the medal column in case we hid it
				$(".medal").removeClass("hideMedalColumn");

				// Show the table
                $("#results_display").fadeIn("slow");

                // Enable the Export Button
                $("#btnExport").removeAttr('disabled');

		   },
		   async:	false
		 });
}

function populateOverallResults()
{
	var selevent = $("#cmb_events").val();

	$("#tblResults").find("tr:gt(0)").remove();

	var curevent = $('#cmb_events').val();
	mycmd = '?cmd=getoverallresults&eventid=' + selevent;
	var markup = "";

	$.ajax({
		   url:		results_ajax_url + mycmd,
		   success:	function(data)
		   {
				var mydata = eval(data);
				var curevent;
				var lastevent;
				var curagegroup;
				var lastagegroup;
				var grouppos = 1;
				var medal;
				var instatecount = 0;
				var instate = false;

				for (var i=0;i

";
						markup += "
" + mydata[i].AthleteName + "

";
						markup += "
" + mydata[i].BibNumber + "

";
						markup += "
" + mydata[i].AgeGroup + "

";
						markup += "
" + mydata[i].InState + "

";
						markup += "
" + mydata[i].StartTime + "

";
						markup += "
" + mydata[i].FinishTime + "

";
						markup += "
" + mydata[i].ElapsedTime + "

";
						markup += "
" + avgspeed + "

";
						markup += "
" + grouppos + "

";
						markup += "
&nbsp

";
						markup += "

";

						grouppos ++;

				}

				// Empty our container (table body)
				$("#tbodyResults").empty();

                // Append the HTML rows
				$('#tbodyResults').append(markup);

				// Hide the Medal column
				$(".medal").addClass("hideMedalColumn");

				// Show the table
                $("#results_display").fadeIn("slow");

                // Enable the Export Button
                $("#btnExport").removeAttr('disabled');

		   },
		   async:	false
		 });
}

function calculatespeed(hour,minute,second, distance) {
	//  convert time to seconds
	var elapsedtime = ((parseFloat(hour) * 3600) + (parseFloat(minute) * 60) + parseFloat(second));

	//  calculate speed
	var returnval = ((distance * 1852)  / (elapsedtime * 0.44704)); 

	// Return a 10th precision decimal value
	return returnval.toFixed(1);
}

function getCSVData(){
	// This uses a jQuery plugin that builds the table html into a csv structure
	var csv_value=$('#tblResults').table2CSV({delivery:'value'});
	$("#csv_text").val(csv_value);
}

So the javascript does the html generation and uses a jQuery plugin called table2CSV that will parse out the html table into a CSV format. The data comes in from an Ajax call to results_ajax.php in a JSON format.

On to the Server Side PHP file (results_ajax.php)

/*

Filename:	results_ajax.php
Purpose:	This script gets all the data for the Results Page32
Created:	2010-10-07
Created by:	LJ Wilson

History:	Date			Initials	Comments
			10/07/10		LJW			Created

*/

// Always store your database connection classes outside the Web Root
include("/home/content/21/6193721/includes/clsDatabase_evtmanager.php");
include("/home/content/21/6193721/includes/clsDebug.php");
include("../functions.php");

$db  = new Database;
$bug = new Debug;
$cur_date = date("Y-m-d");
$cur_year = date("Y");
$cur_time = date("H:i:s");
$dayofweek = date("l");

$ip=$_SERVER['REMOTE_ADDR'];

if ($db)
{
	switch ($_GET['cmd'])
	{
		case "getresults":
			$i = 0;
			$v = array();
			$eventid = $_GET['eventid'];

			$sql = "select e.EventName, CONCAT(a.FName, ' ', a.LName) AS AthleteName, " .
				"t.BibNumber, r.AgeGroup, case a.State WHEN 'AR' Then 'Y' ELSE 'N' END AS InState, " .
				"t.StartTime, t.FinishTime, " .
				"TIMEDIFF(t.FinishTime,t.StartTime) as ElapsedTime, " .
				"HOUR(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedHour, " .
				"MINUTE(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedMinute, " .
				"SECOND(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedSecond " .
				"FROM TimeKeeper t " .
				"INNER JOIN Athletes a on a.AthleteID = t.AthleteID " .
				"INNER JOIN Events e on e.EventID = t.EventID " .
				"INNER JOIN Roster r on r.AthleteID = a.AthleteID " .
				"and r.EventID = t.EventID " .
				"WHERE t.EventID = " . $eventid . " " .
				"ORDER BY r.AgeGroup, TIMEDIFF(t.FinishTime,t.StartTime) ASC ";

			$rs=$db->query($sql);
			if ( $db->RecordCount($rs) >0 ) // Make sure we returned something
			{
				while ( $row = $db->fetch_array($rs) )
				{
					$eventname = $row["EventName"];
					$athletename = $row["AthleteName"];
					$bibnumber = $row["BibNumber"];
					$agegroup = $row["AgeGroup"];
					$instate = $row["InState"];
					$starttime = $row["StartTime"];
					$finishtime = $row["FinishTime"];
					$elapsedtime = $row["ElapsedTime"];
					$elapsedhour = $row["ElapsedHour"];
					$elapsedminute = $row["ElapsedMinute"];
					$elapsedsecond = $row["ElapsedSecond"];

					$agegrouparray1 = explode(" ", $agegroup);
					$agegrouparray2 = explode("-", $agegrouparray1[1]);

					$agegrouplookup = $agegrouparray1[0] . "s " . $eventname;
					$agegrouplookup_age = $agegrouparray2[0];

					$recordsql = "SELECT COALESCE(RecordTime,'23:59:59') as RecordTime FROM StateRecords WHERE " .
								"Event = '" . $agegrouplookup . "' AND Age = '" . $agegrouplookup_age . "'";

					$recordrs=$db->query($recordsql);
					$recordrow = $db->fetch_array($recordrs);
					$RecordTime = $recordrow["RecordTime"];

					$athtime = strtotime($elapsedtime);
					$rectime = strtotime($RecordTime);

					$diff = ($rectime - $athtime) / 60;

					if ($diff >=0 )
					{
						$v[$i]["ElapsedTime"] = $elapsedtime . " **";
					}
					else
					{
						$v[$i]["ElapsedTime"] = $elapsedtime;
					}

					$v[$i]["EventName"] = $eventname;
					$v[$i]["AthleteName"] = $athletename;
					$v[$i]["BibNumber"] = $bibnumber;
					$v[$i]["AgeGroup"] = $agegroup;
					$v[$i]["InState"] = $instate;
					$v[$i]["StartTime"] = $starttime;
					$v[$i]["FinishTime"] = $finishtime;

					$v[$i]["ElapsedHour"] = $elapsedhour;
					$v[$i]["ElapsedMinute"] = $elapsedminute;
					$v[$i]["ElapsedSecond"] = $elapsedsecond;
					//$v[$i]["RecordTime"] = $RecordTime;
					//$v[$i]["TimeDiff"] = $diff;
					//$v[$i]["RecordSQL"] = $recordsql;
					$i++;
				}
			}
				// Send out the response object
				echo json_encode($v);
		break;

		case "getoverallresults":
			$i = 0;
			$v = array();
			$eventid = $_GET['eventid'];

			$sql = "select e.EventName, CONCAT(a.FName, ' ', a.LName) AS AthleteName, " .
				"t.BibNumber, r.AgeGroup, case a.State WHEN 'AR' Then 'Y' ELSE 'N' END AS InState, " .
				"t.StartTime, t.FinishTime, " .
				"TIMEDIFF(t.FinishTime,t.StartTime) as ElapsedTime, " .
				"HOUR(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedHour, " .
				"MINUTE(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedMinute, " .
				"SECOND(TIMEDIFF(t.FinishTime,t.StartTime)) as ElapsedSecond " .
				"FROM TimeKeeper t " .
				"INNER JOIN Athletes a on a.AthleteID = t.AthleteID " .
				"INNER JOIN Events e on e.EventID = t.EventID " .
				"INNER JOIN Roster r on r.AthleteID = a.AthleteID " .
				"and r.EventID = t.EventID " .
				"WHERE t.EventID = " . $eventid . " " .
				"ORDER BY TIMEDIFF(t.FinishTime,t.StartTime) ASC ";

			$rs=$db->query($sql);
			if ( $db->RecordCount($rs) >0 ) // Make sure we returned something
			{
				while ( $row = $db->fetch_array($rs) )
				{
					$eventname = $row["EventName"];
					$athletename = $row["AthleteName"];
					$bibnumber = $row["BibNumber"];
					$agegroup = $row["AgeGroup"];
					$instate = $row["InState"];
					$starttime = $row["StartTime"];
					$finishtime = $row["FinishTime"];
					$elapsedtime = $row["ElapsedTime"];
					$elapsedhour = $row["ElapsedHour"];
					$elapsedminute = $row["ElapsedMinute"];
					$elapsedsecond = $row["ElapsedSecond"];

					$v[$i]["EventName"] = $eventname;
					$v[$i]["AthleteName"] = $athletename;
					$v[$i]["BibNumber"] = $bibnumber;
					$v[$i]["AgeGroup"] = $agegroup;
					$v[$i]["InState"] = $instate;
					$v[$i]["StartTime"] = $starttime;
					$v[$i]["FinishTime"] = $finishtime;
					$v[$i]["ElapsedTime"] = $elapsedtime;
					$v[$i]["ElapsedHour"] = $elapsedhour;
					$v[$i]["ElapsedMinute"] = $elapsedminute;
					$v[$i]["ElapsedSecond"] = $elapsedsecond;
					$i++;
				}
			}
				// Send out the response object
				echo json_encode($v);
		break;

	}

}

This Ajax file only has two sections (getresults and getoverallresults). Also note that the includes for the database and debug classes are outside the webroot. I always make it a habit to store my DB connection strings and my debug log paths outside the webroot. Even though PHP code is protected and is not readable from the browser in a “normal” situation, IF the PHP engine breaks on the server, guess what happens to all the PHP code that is normally protected? It gets rendered to the screen! If the sensitive stuff (usernames and passwords to the DB) are stored outside of the area that a web user can get, then all is still safe even if the PHP engine shuts down. This is a good practice to get into and highly recommended.

Next up is the PHP script needed to output our CSV formatted file (getCSV.php):

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"my-data.csv\"");
$data=stripcslashes($_REQUEST['csv_text']);
echo $data;

All this does is take the string of formatted text from the Ajax call we made in results.js and build a file for output with it.

And lastly, the CSS needed to make it look somewhat pretty:

@charset "UTF-8";
/* CSS Document */

.hideMedalColumn { display: none; }

#btnExport
{
	background-color:orange;
	color:black;
	font-weight:bold;
	position:relative;
	left:85px;
}

#btnViewAgeGrouped
{
	background-color:purple;
	color:white;
	position:relative;
	left:20px;
}

#btnViewOverall
{
	background-color:red;
	color:white;
	position:relative;
	left:40px;
}

#results_display
{
    position:relative;
    top:-150px;
    left:400px;
    -moz-border-radius:5px 5px;
}

#resultstable
{
	font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
	font-size: 12px;
	margin: 10px;
	width: 650px;
	text-align: left;
	border-collapse: collapse;
}

#resultstable th
{
	font-size: 13px;
	font-weight: normal;
	padding: 8px;
	background: #FFFFFF;
	border-top: 4px solid #aabcfe;
	border-bottom: 3px solid #aabcfe;
	color: #039;
}

#resultstable td
{
	padding: 8px;
	background: #e8edff;
	border-bottom: 1px solid #fff;
	color: #669;
	border-top: 1px solid transparent;
}

#resultstable tr:hover td
{
	background: #d0dafd;
	color: #339;
}

#resultstable-table-b
{
	font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
	font-size: 12px;
	margin: 45px;
	width: 480px;
	text-align: center;
	border-collapse: collapse;
	border-top: 7px solid #9baff1;
	border-bottom: 7px solid #9baff1;
}

#resultstable-table-b th
{
	font-size: 13px;
	font-weight: normal;
	padding: 8px;
	background: #e8edff;
	border-right: 1px solid #9baff1;
	border-left: 1px solid #9baff1;
	color: #039;
}

#resultstable-table-b td
{
	padding: 8px;
	background: #e8edff;
	border-right: 1px solid #aabcfe;
	border-left: 1px solid #aabcfe;
	color: #669;
}
Posted in Uncategorized | Leave a comment

Using jQuery’s Cool Predicate Selectors

Posted on November 20, 2010 by admin

jQuery has awesome selectors. Selection by id, name, class, type, position, etc are all awesome, but what about objects that have dynamic id’s, names or classes? Dot Net developers in particular have to deal with automatic naming of objects. ASP html object id’s are modified automatically. jQuery comes to the rescue!

When needing to select objects that have dynamic id’s, names, classes, etc, just use the predicate selectors.

As always the best resource for syntax and rules is the jQuery API site: http://api.jquery.com

Here is a real world example of this in action.  In my case, I have several textboxes that are dynamically named and id’d in a classic ASP app.  An example is:

and so on. There are also other dynamically created objects that are grouped together, such as:

So here is how I accomplished the goal of being able to select these objects and their corresponding siblings.

First up is our event binding (done is the document.ready section)

$(document).ready(function () {
    // Bind the department select box
    $("#secondChoice").bind("change", function () {
        CheckBudget();
    });

    // Bind our price textboxes
    $(input[id ^= "txtUnitPrice_"]).bind("change", function () {
        CheckBudget();
    });

    // Bind our quantity textboxes
    $(input[id ^= "txtQuantity_"]).bind("change", function () {
        CheckBudget();
    });

});

A quick note about jQuery chaining. The section above could be coded in a more streamlined fashion by chaining the individual statements together (see the jQuery API site), I choose not to do this in many cases in order to make the code more readable (at least to me).

Now for the fun part:

function CheckBudget()
{
    // Set the values for the Research Department Criteria
    var Dept = $("#firstChoice").val();
    var EOC = $("#secondChoice").val();

    if (EOC=="Select an EOC" || EOC=="X")
    {
        alert("Please Select an EOC")
        return false;
    }

    // Check to make sure we have values in Qty and Price fields
    // Only if we came here from the onChange event for the details
    if (idx > 0) {
        if ($("#txtQuantity_" + idx).val() == "")
            return false;
        if ($("#txtUnitPrice_" + idx).val() == "")
            return false;
    }

    if ( IsResearchDept(Dept) )
    {
        var quantity=0;
        var unitprice=0;
        var total=0;
        var status = $('#BudgetStatus').val();

        // This will get all the txtUnitPrice_* inputs and then loop through the collection
        $('input[id^="txtUnitPrice_"]').each(function () {
            var myid = this.id;

            // Get the index by splitting the ID
            var myidxarray = myid.split("_");
            var myidx = myidxarray[1];

            // Make sure the txtQuantity_XCOUNT exists on the page.  If not, set to 1
            if ($("#txtQuantity_" + myidx).size() > 0) {
                quantity = $("#txtQuantity_" + myidx).val();
            }
            else {
                quantity = 1;
            }

            unitprice = $("#" + myid).val();

            // Make sure user has entered values in the current line
            if (quantity && unitprice)
                total += unitprice * quantity;

        });

	    // Fire off the request and get something back
        $.ajax({
            type: "GET",
            url: "ajax/ajax.asp",
            data: "cmd=getresearchbudgetdata&account=" + Dept + "&EOC=" + EOC,
            dataType: "json",
            cache: false,
            async: false,
            timeout: 10000,
            success: function (budgetdata) {
                try {
                    var cur_balance = budgetdata.balance;
                    var real_balance = cur_balance - total;

                    if (real_balance < 0) {
                        // Set status to Overdrawn and notify user
                        status = "Overdrawn";
                        alert("Insufficient funds - Available Balance = " + cur_balance);
                    }
                    else if (real_balance > 0) {
                        status = "UnderBudget";
                    }
                }
                catch (err) {
                    // This means no records were returned.  No budget in place.
                    status = "NoBudget";
                }
            },
            failure: function () {
                status = "Dataset_Error";
            } //  // failure
        });        // $.ajax

        // Set the value of our hidden field.
        $('#BudgetStatus').val(status);

    } // if (isResearchDept(Dept))

}  // end function

So what we do is to get the value of the selected department and account (EOC). We then send that off to a server side routine that runs a stored procedure and returns a value of the budget that is available.

We then get the total of the individual txtUnitPrice textboxes and the corresponding txtQuantity and add all those up for a total of the entire PO request.

Once we have the budget available and the total of the requested PO, we can make our business decision and decide how to proceed.

A couple of quick notes on other things we are doing. This section:

// Make sure the txtQuantity_XCOUNT exists on the page.  If not, set to 1 (for CR)
if ($("#txtQuantity_" + myidx).size() > 0) {
     quantity = $("#txtQuantity_" + myidx).val();
}
else {
     quantity = 1;
}

checks to make sure that there is an object with the id txtQuantity_X (X is dynamic). For some of the types of requests, there was not a quantity field. Instead of writing separate functions for these, I just check to make sure that the object exists and if it doesn’t an implied quantity of 1 is used. To check to see if something exists on the page, using jQuery’s .size attribute is great. What you get back is the count of matched elements. Once again, refer to the jQuery API site for syntax and more info.

Another thing we use here is a hidden object on the page with the id of BudgetStatus. We use that hidden field to store the status assigned by our business rules. This value is used on the page that the form POSTs to to decide whether to allow the person to submit the request or not. Here is that assignment:

// Set the value of our hidden field.
$('#BudgetStatus').val(status);

So that is it. We can easily select dynamically id’d objects and then do something with those selections (event binding, looping through the object collection, etc). All this compliments of jQuery!

Posted in Code Snippets | Leave a comment

Dynamic Image Loading with jQuery & PHP

Posted on October 14, 2010 by admin

First thing we need to do is to add our references to the code behind files we need (css and js):

Second thing we need to do is call the init function when the page is finished loading

That is added to the end of the head section of our php document. This causes the calling of the init function which is defined in our code behind js file.

Here is the init function in timesheet.js:

function init()
{
	bindEvents();
	populateEvents();
}

Here is the body of the page:

The bibtable html table is used to display our enhanced racer icons (85X110 png images)

First things … some page variables and our event binding. I almost always do the event binding using jQuery. Years ago, this would have been done in the html directly (onChange=’someFunction();’), but now – it is much cleaner, easier and more scalable to do this in our code behind javascript file.

// Page scope variables:
var qtip_x = -100;	// Horizontal
var qtip_y = -150; // Vertical
var qtip_width = 175;

function bindEvents()
{
        // Bind our select box change event (the event picker) to the function
        // that builds our page
	$('#cmb_events').bind('change', function() {
		populateTimesheet();
	});
}

Next up is building the jQuery code behind that will dynamically create our icons and build the html table for us.
The main engine of this code behind file (timesheet.js) is the populateTimesheet function:

function populateTimesheet()
{
	// Initialize the counter for the row count
	var counter = 0;

	// Set the variable for the number of td's per tr
	var maxcols = 10;

	// Init elapsedtime
	var elapsedtime = "";

	// Initialize the variable that will hold our tr and td html
	var myrow="
";

	// Clear out the existing table
	$("#bibtable").find("tr").remove();

	// Get the selected event and build the table
	var curevent = $('#cmb_events').val();
	mycmd = '?command=buildfinishscreen&eventid=' + curevent;

	// The meat of the table creation process
	$.ajax({
		   url:		evtmanager_ajax_url + mycmd,
		   success:	function(data)
		   {
				mydata = eval(data);

				for (var i=0;i -1)
						sex = "F";
					else
						sex = "M";

					// Build the row and set the the image attributes
					myrow +="


";

					// We have either reached the number of td's we want or we
					// have reached the end of the data
					if (counter == maxcols || i == (mydata.length -1))
					{
						myrow += "
";
						counter = 0;
					}
				}

				// Add the created row to the table
				$("#bibtable > tbody").append(myrow);
		   },
		   async:	false
		 });

        // Now we dynamically create a tooltip using the q-tip jQuery plugin
	$('.athlete').each(function(index) {

		// Get the id tag of the current object
		var myid = this.id;

		// We store the athletes name in the name attribute of the element
		// We store it in two parts (First_Last), so we have to split it
		// to add a space in place of the underscore
		var myArray = this.name.split("_");

		var cnt = myArray.length;

		if (cnt == 2)
			var athletename = myArray[0] + " " + myArray[1];
		else
    		        var athletename = myArray[0] + " " + myArray[1] + "-" + myArray[2];

		// Now set the value of the tooltip and call the tooltip plugin
		$("#" + myid).qtip({
			content: athletename,
			position: { adjust: { x: qtip_x, y: qtip_y } },
			style: { width: { max: qtip_width, min:qtip_width } }
		});		

  	});

}

Lastly, we need to be able to handle the onClick event for each image. Now, I just told you that event binding using jQuery is much better than doing it in html, but this is an exception. These images aren’t really images and since they will be created dynamically and change often, we would be forced to rebind. Since all we will ever need to capture is the onClick event, it is fine and better to do this event binding in the html.

function score(obj)
{
	var myArray = obj.id.split("_");
	var nameArray = obj.name.split("_");
	var athleteid = myArray[0];
	var bibnumber = myArray[1];
	var eventid = myArray[2];
	var sex = myArray[3];
	var elapsedtime = "";

	var athletename = nameArray[0] + " " + myArray[1];

	mycmd = '?command=addTime&eventid=' + eventid + "&athleteid=" + athleteid + "&bibnumber=" + bibnumber;

	$.ajax({
		   url:		evtmanager_ajax_url + mycmd,
		   success:	function(data)
		   {
				mydata = eval(data);
				currentstatus =  mydata[0].CurrentStatus;

				try {
					elapsedtime = mydata[0].ElapsedTime;
				}
				catch(err) {
					// Do nothing
				}

				try {

					if (elapsedtime.length > 0)
					{
						// Now set the value of the tooltip and call the tooltip plugin
						$("#" + obj.id).qtip({
							content: athletename + " - " + elapsedtime,
							position: { adjust: { x: qtip_x, y: qtip_y } },
							style: { width: { max: qtip_width, min:qtip_width } }
						});
					}
				}
				catch(err)
				{
					// o nothing
				}

				var newimgsrc = "imagecontrol.php?bibnumber=" + bibnumber + "&sex=" + sex + "&currentstatus=" + currentstatus;

				$("#" + obj.id).attr({
          			src: newimgsrc
		   		});
		   },
		   async:	false
		 });
}

This is our php script that generates our dynamic images.
A side note here is to not ever store DB connection info inside your web root. Always keep those files outside of the web root for maximum security. That way, if php crashes – that precious code won’t get rendered out.

// Include the database and debug classes
include("/outsidewebroot/includes/clsDatabase_evtmanager.php");
include("/outsidewebroot/includes/clsDebug.php");

$db  = new Database;
$bug = new Debug;
$cur_date = date("Y-m-d");
$cur_year = date("Y");
$cur_time = date("H:i:s");
$dayofweek = date("l");

$maleimg = "custom/images/racer_m";
$femaleimg = "custom/images/racer_f";

$imgext = ".gif";

$BibNumber = $_GET['bibnumber'];
$Sex =  $_GET['sex'];
$CurrentStatus = $_GET['currentstatus'];

if ($CurrentStatus <> "INVALID" && $CurrentStatus <> "")
{
	if ($Sex=="M")
		$myimage = $maleimg . "_" . $CurrentStatus . $imgext;
	else
		$myimage = $femaleimg . "_" . $CurrentStatus . $imgext;
}
else
{
	if ($Sex=="M")
		$myimage = $maleimg . $imgext;
	else
		$myimage = $femaleimg . $imgext;
}

// specify the file name - you can use a full path, or "../../" type stuff here
// if the image is not in the same directory as this code file
//$image = imagecreatefrompng("gs-banner-sm.png");
$image = imagecreatefromgif($myimage);

// specify the font size
$font_size = 24;

// in this case, the color is white, but you can replace the numbers with the RGB values
// of any color you want
$color = imagecolorallocate($image, 255,255,255);

// Our shadow colors
$black = imagecolorallocate($image, 0,0,0);
$blue = imagecolorallocate($image, 0,46,184);

// $image - the base image file we specified above
// $font_size - Well duh. Its the size of the font
// 0 - the angle of the text - we don't want an angle, so we leave it at 0
// 1 - pixels to the right from the leftmost part of the image
// 110 - pixels down from the top of the image
// $color - the color we defined above
// Our font
// $BibNumber the athlete # we're overlaying
ImageTTFText ($image, $font_size, 0, 1, 111, $color, "custom/php_fonts/font.ttf",$BibNumber);

// The shadow
ImageTTFText ($image, $font_size, 0, 2, 110, $blue, "custom/php_fonts/font.ttf",$BibNumber);

// Render it out and then kill it - no memory leaks here :)
header("Content-type: image/png");
imagepng($image);
imagedestroy($image);

That is pretty much it. Of course, there is some styling that is done and some images that need to be created. I use 22 images total (11 for male and 11 for female). The difference is the status of the racer. So for a racer that has completed 1 lap, there is a big “1″ at the top left of the image, for a racer that has finished all required laps, there will be an “F” there. I decided not to dynamically add that status letter or # due to speed. I know that for the events I have designed this software to support, there will be no more than 5 laps. I went ahead and added support for up to 9 laps (hence 1-9, S (Start) and F (Finish).

The only thing I knew would always be dynamic is the racer’s # (bib#) which is what is dynamically embedded into the new image.

I hope this helps someone else that needs to do something similar. It runs very quickly and the end result is this:

Posted in Application Tutorial | Leave a comment