Georgia Tech Library Tally Sidebar using PHP and MySQL

This project is made available under the Gnu Public License v.3.0

Questions regarding the tally, please contact Jay Forrest at jay.forrest@library.gatech.edu

Project Background

Georgia Tech Library began using an online tally sheet in 2003. This tally sheet was created by Scott Riggle, Office of Information Technology, and Kathy Tomajko, Georgia Tech Library. In 2004, maintenance and development was transferred to Jay Forrest, Georgia Tech Library, who at the time worked in the Information Services Department (e.g. Reference). The demo tally sheet is a modified version of our Library Services Desk tally sheet originally created by RaeAnne Forrest, Georgia Tech Library, when she worked in the Circulation and Reserves Department. Georgia Tech currently uses a single page tally sheet that is customized to each Library service, and uses a sidebar based tally sheet for many of these service points. For more information, please see our presentation to the 2013 Southeastern Library Assessment ConferenceLink forthcoming.

Contents

  1. MySQL Backend
  2. Building the Tally Sheet
  3. Limiting Access to the Tally Sheet
  4. Links Pages
  5. CSS
  6. Customizing the Sidebar

Coming Soon: Visualizing Tally Data, Adding a Sidebar, Gallery, adding more tally choices

MySQL Backend

We are using a MySQL backend for storing tally data, here is the SQL query to create the table:


SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Table structure for table `tally`
--

CREATE TABLE IF NOT EXISTS `tally` (  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ipadd` varchar(16) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `source` varchar(32) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `location` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `type` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `question` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `details` text CHARACTER SET latin1,
  `spent` float NOT NULL DEFAULT '1',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contact_method` varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `question` (`question`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

You may also consider other fields like response_time for virtual transactions, or patron_group.

Back to Top

Building the Tally Sheet

HTML Header


<?php if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start(); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
   <title>Library Tally Sidebar: Demo</title>
   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
   <link rel="stylesheet" type="text/css" href="tally.css">
</head>
<body>
<!--     
    This program was created by Jay Forrest at the Georgia Tech Library.

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
--!>
  

Connect to the Database


<?php
// Original Code created by jay.forrest@library.gatech.edu
// Demo Created 10/2013
// Created for SE Library Assessment Conference by Jay Forrest, Georgia Tech Library
// GPL


//Declare Module Name 
  $moduleName = "Demo Tally Sheet";
  $moduleDatabase = ""; // the name of your tally database
  $moduleAdministrator = "Jay Forrest"; // the name of your tally database administrator
  $moduleAdminemail = "jay.forrest@library.gatech.edu"; // the email of your tally database administrator


//Connect to Database
  include "";  // Path to your database connection script

?>

Sample database connection script


<?php
$username = ''; //MySQL user name
$password = ''; //MySQL password

//Connnect to mySQL database
$mysqli = new mysqli('localhost', $username, $password, $moduleDatabase);

//Get ip address
$ipAddress = getenv('REMOTE_ADDR');

//Connect or notify administrator and user of connection failure
if ($mysqli->connect_error) {
  $body = "Connecting to the $moduleName failed from $ipAddress -- $hostName" . $mysqli->connect_errno . "   " . $mysqli->connect_error ;
  mail($moduleAdminEmail, $moduleName.' Connection error', $body);
  die('failed to connect to database, email notification has been sent to ' . $moduleAdministrator);
}

?>

Create the Links Toolbar


<p>
  <span class="button"><a href="links.php" class="buttonText" onclick="this.target=_main;" >Links</a></span>
  <span class="button"><a href="index.php" class="buttonText" onclick="this.target=_main;">Tally Sheet</a></span>
  <span class="button"><a href="http://www.library.gatech.edu/" class="buttonText" target=_main>Library Home Page</a></span>
</p>

We use the links pages to keep common information and instructions for our Library Services Desk staff, we actually use three one for Reference, one for Access Services, and one for IT, a sample page is provided below

Open the Feedback box


<div class="feedback" > <!-- Here is where your tallies will appear on submit. -->

Interpreting the submit button


<?php

/*Generic code
  if(in_array("<value>", $_POST)) { // This is the value from a single submit button
    echo "<><br />Tally Submitted"; // echo what you want the user to see when they submit this tally
    $message = 1; // Let the code know that a tally has been submitted
    $demoTally = "'<type>' , '<question>'";  $spent = 1;  //code the tally type, question, and default time spent for this tally
  }
*/
  if(in_array("Directional", $_POST)) {
    echo "Directional<br />Tally Submitted";
    $message = 1;
    $demoTally = "'d' , 'Directional Assistance'";  $spent = 1;
  }
  if(in_array("IT", $_POST)) {
    echo "IT<br />Tally Submitted";
    $message = 1;
    $demoTally = "'i' , 'IT Support'";  $spent = 5;
  }
  if(in_array("Reference", $_POST)) {
    echo "Reference<br />Tally Submitted";
    $message = 1;
    $demoTally = "'t' , 'Information Instruction'";  $spent = 5;
  }
  if(in_array("Other Services", $_POST)) {
    echo "Other Services<br />Tally Submitted";
    $message = 1;
    $demoTally = "'o' , 'Other Services'";  $spent = 5;
  }

As you create more buttons for the tally sheet, you will need to make additional statements like above to process what is coming in from the tally sheet via $_POST.

Setting the Contact method


  //ContactMethod - values are  1 (default) Walkin; 2 = phone; 3 = email; 4 = chat; 5 = other
  $contactMethodArray = array (1,2,3,4,5);
  if (in_array($_POST['contactMethod'], $contactMethodArray)) { $contactMethod = $_POST['contactMethod']; } else { $contactMethod = 1; }

Override the time spent


  //Override default time if numeric time is submitted
  if ($_POST['spent'] &&&amp; is_numeric($_POST['spent'])) {$spent = abs($_POST['spent']);}

Write the tally to the database


  if ($message == 1) {
    //$exitStatus - creates a demo mode if not set to notdie - allows display, but does not record tally
    $exitStatus = "notdie";
    include "includeLibIPLimit.php"; 
    if ($exitStatus == "notdie") {
      $mysqli->query("INSERT INTO `".$moduleDatabase."`.`tally` (`ipadd`, `source`, `location`, `type`, `question`, `spent`, `contact_method`) 
		VALUES ('$ipAddress', 'demoTally', 'demo', $demoTally, $spent, $contactMethod)");
      echo "<META http-equiv='refresh' content='3;URL=index.php'>";
    } else { 
      echo "<br />Demo Mode - Tally not recorded"; 
    }
  }
?>

So first we see if a tally came through ($message==1), and then we check if the tally came from a valid source using the includeLibIPLimit.php (see below for limiting access to the Tally Sheet). This code changes the variable $exitStatus for invalid IPs. A valid IP will perform the mysqli query to add the information to the database. We are hard-coding the source and location fields.

Close the Feedback box


</div>

Start the Tally Sheet Form


<form method="post" action="index.php">

Create the buttons for recording the contact method


  <p>
    <span class="contactMethod">
      <!-- Icons courtesy Akhtar Sheika: http://www.2expertsdesign.com/ -->
      <label for="contactMethod1"><img src="images/1261001860_DocumentsGnomeStockPerson.png" alt="In Person" style="width: 22px;height: 22px; padding:0px;"/></label><input id="contactMethod1" name="contactMethod" type="radio" value="1" checked="checked" title="Select this button if the transaction occurred in person at the desk"/>
      <label for="contactMethod2"><img src="images/1261001813_Mobile.png" alt="Phone" style="width: 22px;height: 22px;"/></label><input id="contactMethod2" name="contactMethod" type="radio" value="2" title="Select this button if the transaction occurred via phone"/>
      <label for="contactMethod3"><img src="images/1261001745_Mail-2.png" alt="Email" style="width: 22px;height: 22px;"/></label><input id="contactMethod3" name="contactMethod" type="radio" value="3" title="Select this button if the transaction occurred via email"/>
      <label for="contactMethod4"><img src="images/1261001694_Chat.png" alt="Chat" style="width: 22px;height: 22px;"/></label><input id="contactMethod4" name="contactMethod" type="radio" value="4" title="Select this button if the transaction occurred via chat"/>
      <label for="contactMethod5"><img src="images/1261001771_Twitter-3.png" alt="Social Networking" style="width: 22px;height: 22px;"/></label><input id="contactMethod5" name="contactMethod" type="radio" value="5" title="Select this button if the transaction occurred via a social media like facebook, twitter, etc"/>
    </span>
  </p>

Create the time spent override


  <p>
    <span class="timeSpent">
      <input id="spent" name="spent"  onfocus="this.select()" type="text" class="time" value="Time Spent Override in Minutes" title="You can enter a numeric value of minutes spent to override the default times assigned to the questions below."/>
    </span>
  </p>

Contact Method and Time spent are selected first, so that we can use the tally buttons below as submit buttons. When training, think of "How was the transaction conducted? How long did it take? And what was the transaction about?"

Create the tally buttons


  <p style="font-size:small;">Click once on the appropriate button below</p>
  <p>

	<!-- Generic button
         <input type="submit" class="largeButton <class>" id="tally#" name="tally#" value="<Tally question, phrase that gets submitted by the form>" title="<Description of the tally>"/><br />
       -->
	<input type="submit" class="largeButton directional" id="tally1" name="tally1" value="Directional" title="Questions about library hours, location of restrooms, offices: Default Time: 1 minute."/><br />
	<input type="submit" class="largeButton itTally" id="tally2" name="tally2"  value="IT" title="Assistance with cameras, scanners, or computers.  Default Time: 7 minutes."/><br />
	<input type="submit" class="largeButton accessServices" id="tally3" name="tally3"  value="Other Services" title="Default Time: 8 minutes."/><br />
	<input type="submit" class="largeButton reference" id="tally4" name="tally4" value="Reference" title="Questions about journals- print, online / help with searching online databases like Compendex, or searching GIL/VuFind catalog on library website: Default Time: 8 min."/><br />
</p>

Use the title of each of the inputs to describe the tally and include the default time spent that is recorded when the question is tallied.

Close the page


</form>
</body>
</html>

Back to Top

Limiting Access to the Tally Sheet

We generally limit access to our tally sheet based on IP address and are currently using a PHP-based solution rather than a .htaccess solution because of how our web-server is configured. The PHP solution also allows us to create a demo version of the tally sheet.


<?php
// Created 9/2011 - Jay Forrest, Collection Assessment & Statistics Coordinator

$hostName =  gethostbyaddr($ipAddress);  // $ipAddress is created in includeSqliHost.php


//Customize this substr and length to your library
 if (substr($hostName, -19) == '.library.gatech.edu') {
   $trimHostName = substr_replace($hostName, '', -19);
 } else {
   $trimHostName = $hostName;
 }
 

//Set IP ranges that allow tally
$validIP = array("Library" => "130.207.50" , "Commons" => "130.207.180" , "Library2" => "143.215.141");

//Unacceptable HostNames (known visitor terminals: lib-training)
$invalidHosts = array( "lib-training");


if ( !in_array($trimHostName, $invalidHosts) && (in_array(substr($ipAddress, 0, 10), $validIP) || in_array(substr($ipAddress, 0, 11), $validIP))) {
//Valid 

} elseif ($exitStatus == "notdie" ) {
  //Declare exit status before loading include, the purpose is to facilitate demonstrations, where want someone to see how an item functions, but not allow their actions to write to our databases
  $exitStatus = "visitor";
} else {
  echo "You do not have access to this module";
  die;
}
?>

Back to Top

Creating a Links Page


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
  <meta http-equiv="Content-type" content="text/html;charset=UTF-8" />

<!-- refresh to Tally sheet after 20 seconds -->
  <meta http-equiv="refresh" content="20;URL='http://www.library.gatech.edu/libdata/demoTally/index.php'">
  <link rel="stylesheet" type="text/css" href="tally.css">
  <title>Links</title>
</head>

<body>
<p>
  <span class="button"><a href="links.php" class="buttonText" onclick="this.target=_main;" >Links</a></span>
  <span class="button"><a href="index.php" class="buttonText" onclick="this.target=_main;">Tally Sheet</a></span>
  <span class="button"><a href="http://www.library.gatech.edu/" class="buttonText" target=_main>Library Home Page</a></span>
</p>
<hr />

<span class="library"><a href="dashboard.html" onclick="this.target='_main';">Dashboard</a></span>

</body>
</html>

Back to Top

CSS


/* Sidebar and Links CSS */
body {
  margin: 0;
  background-color: #EDEDED;
}
p {
  margin: 1px;
  padding-bottom: .2em;
}
a {
  color: #000000;
  text-decoration: none;
}

.button {
  background-color: rgba(200,180,149,0.7);
  border: 2px outset blue;
  cursor: pointer;
  margin: 2px;
  padding: 1px;
  width: 198px;
  display: block;
  text-align: center;
  font-weight: bold;
}
.button a{
  text-decoration: none;
  color: #000000;
}
.button:hover {
  background-color: rgba(200,180,149,0.8);
  border: 2px inset #000000;
}

.link {
  background-color: rgba(180,180,149,0.7);
  border: 2px outset #000000;
  cursor: pointer;
  margin: 2px;
  padding: 1px;
  width: 188px;
  display: block;
  text-align: center;
  text-decoration: none;
  font-weight: normal;
  color: #000000;
}

/* Tally Sheet CSS */

.feedback {
  background-color: rgba(100,180,149,0.6); 
  height: 4em;
  text-align: center;
  width: 200px;
  margin: .4em;
  font-size: small;
}

input[type=radio] {margin: 0;  }

.contactMethod {
  margin: 4px 4px 4px 10px;
}

.timeSpent {
  background-color: rgba(200,180,149,0.6); 
  margin: 3px 3px 3px 3px;
  width: 200px;
  display: block;
}
input.time {
  background-color: rgba(200,180,149,0.6); 
  border: 2px inset #000000;
  width: 196px;
}

/* Large Buttons */
.largeButton {
  background-color: rgba(200,180,149,0.8); 
  cursor: pointer;
  height: 1.85em;
  margin: 1px 3px 3px 1px;
  padding: 2px;
  width: 15em;
  font-weight:bold;
}

/* Small Buttons */
.smallButton {
  background-color: rgba(200,180,149,0.8); 
  cursor: pointer;
  height: 1.55em;
  margin: 1px 3px 2px 1px;
  width: 15.4em;
  font-size:small;
}

.directional {
  border: 3px outset orange;
}
.directional:hover {
  background-color: rgba(200,180,149,0.7); 
  border: 3px inset orange;
}
.reference {
  border: 3px outset green;
}
.reference:hover {
  background-color: rgba(200,180,149,0.7); 
  border: 3px inset green;
}
.accessServices {
  border: 3px outset red;
}
.accessServices:hover {
  background-color: rgba(200,180,149,0.7); 
  border: 3px inset red;
}
.itTally{
  border: 3px outset blue;
}
.itTally:hover {
  background-color: rgba(200,180,149,0.7); 
  border: 3px inset blue;
}

/* Links CSS */
.library{
  background-color: rgba(180,180,149,0.5);
  border: 2px outset #00ff33;
  cursor: pointer;
  margin: 2px;
  padding: 1px;
  width: 198px;
  display: block;
  text-align: center;
  font-weight: bold;
}

Back to Top

Customizing the sidebar

Allowing a custom date


  //Add this to the validation section
  //Date check for Consultation Tallies
  $updateTime = date("Y-m-d H:i:s"); 

    $consultationCheckDate = $_POST['year']."-".$_POST['month']."-".$_POST['day'];
    if ( $consultationCheckDate == date("Y-n-j", mktime(0, 0, 0, $_POST['month'], $_POST['day'], $_POST['year']))) {
      $updateTime = date("Y-m-d H:i:s", mktime(date("H"), date("i"), 0, $_POST['month'], $_POST['day'], $_POST['year']));
    }
  
  //Change the MySQL insert query to
     $mysqli->query("INSERT INTO `".$moduleDatabase."`.`".$moduleTallyTable."` (`ipadd`, `hostname`, `source`, `location`, `details`, `type`, `question`, `spent` , `contact_method` , `time`) 
       VALUES ('$ipAddress', '$trimHostName', '$moduleSource', '$moduleServicePoint', '', $tally, $spent, $contactMethod, '$updateTime')");
  
  //Add this to the beginning of the tally form (we place it at the beginning as it should be filled before a tally is submitted.
<p><span class="backDate">
<?php

	// Date Selection
	   //Months
		$selected_month = date("n");
		$selected_mon = date("M");
	
		$months = array (1 => 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
		echo "        <select name=\"month\" id=\"month\" title=\"Please select the month the transaction occurred.\">\n";
		echo '          <option selected="selected" value="'.$selected_month.'">'.$selected_mon.'</option>'."\n";
 		foreach ($months as $key => $value) {
			echo "          <option value=\"$key\">$value</option>\n";
		}
		echo "        </select>\n";

	   //Days
		$selected_day = date("d");
		$days = range (1, 31);
		echo "        <select name=\"day\" id=\"day\" title=\"Please select the day the transaction occurred.\">\n";
		echo '          <option selected="selected">'.$selected_day.'</option>'."\n";
		foreach ($days as $value) {
			echo "          <option>$value</option>\n";
		}
		echo "        </select>\n";

 	  //Year
	
		$selected_year = date("Y");
		
		$years = array($selected_year-1, $selected_year);
		echo "        <select name=\"year\" id=\"year\" title=\"Please select the year the transaction occurred.\">\n";
		echo '          <option selected="selected">'.$selected_year.'</option>'."\n";
		foreach ($years as $value) {
			echo "          <option>$value</option>\n";
		}
		echo "        </select>\n";



?>

</span></p>


Here we use PHP to generate the time stamp rather than SQL, and this allows us to use an override drop down, but still default to the current date/time

Back to Top

Add a Service Details Box



//Place this in the validation code:
  //Clean details text entry
  if($_POST['details'] != 'Optional description') {
    $cleanDetails = htmlentities($_POST['details'], ENT_COMPAT, 'UTF-8');
    $mysqlDetails = $mysqli->real_escape_string($cleanDetails);
  } else {
    $mysqlDetails = '';
  }

//Change the MySQL insert query to
     $mysqli->query("INSERT INTO `".$moduleDatabase."`.`".$moduleTallyTable."` (`ipadd`, `hostname`, `source`, `location`, `details`, `type`, `question`, `spent` , `contact_method`) 
       VALUES ('$ipAddress', '$trimHostName', '$moduleSource', '$moduleServicePoint', '$mysqlDetails', $tally, $spent, $contactMethod)");

//Place this in the form above the submit buttons
<?php
echo '  <span class="serviceDetails">';
echo '<input id="details" name="details"  onfocus="this.select()" value="Optional description" type="text" class="details" title="This box can be used to provide additional detail to the tallied transaction."/>';
echo "  </span>\n";
?>

Back to Top