troubleshooting Question

PHP Script to Limit MySQL Data View By Date

Avatar of Jonathan Greenberg
Jonathan GreenbergFlag for United States of America asked on
PHPMySQL Server
11 Comments1 Solution1455 ViewsLast Modified:
The PHP script below enables me to view a table in my MySQL database.  It allows me to choose, with checkboxes, which of the 4 columns I want to view, and to sort by column from a drop-down menu.

I now need to limit the number of rows that are displayed, and I'd like to do this by date, if possible.  My 4th column is Date, and the format in which the date is saved is yyyy-mm-dd hr:sc:ms (e.g., 2008-05-12 17:29:44).

How can I modify my script so that it will allow me to choose a date range within which to display my DB table's data?
<?php
// let the server give you feedback upon errors/warnings
error_reporting(E_ALL);
 
$DB_SERVER=""; //SPECIFY IP or DOMAIN OF YOUR DB SERVER
$DB_USERNAME="";
$DB_PASSWORD="";
$DB_NAME=""; //SPECIFY NAME OF YOUR DATABASE
 
//set the default state for the checkboxes
$checkedFields = array();
$checkedFields['invoice_num'] = " checked='checked' ";
$checkedFields['firstname'] = " checked='checked' ";
$checkedFields['lastname'] = " checked='checked' ";
$checkedFields['transaction_date'] = " checked='checked' ";
 
if( isset($_REQUEST['fieldTracker']) )
{
	//determine the "unchecked" ones 
	if( empty($_REQUEST['invoice_num']) )
		$checkedFields['invoice_num']="";
	if( empty($_REQUEST['firstname']) )
		$checkedFields['firstname']="";
	if( empty($_REQUEST['lastname']) )
		$checkedFields['lastname']="";
	if( empty($_REQUEST['transaction_date']) )
		$checkedFields['transaction_date']="";
}
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
 "http://www.w3.org/TR/REC-html40/loose.dtd">
<html>
<head>
<title></title>
</head>
<body>
<div>
  <form action="<?=$_SERVER['PHP_SELF']?>" method="post" name="select" id="select">
    <input type="checkbox" name="invoice_num" value="1" <?=$checkedFields['invoice_num']?> />Invoice
    <input type="checkbox" name="firstname" value="1" <?=$checkedFields['firstname']?> />First Name
    <input type="checkbox" name="lastname" value="1" <?=$checkedFields['lastname']?> />Last Name
    <input type="checkbox" name="transaction_date" value="1" <?=$checkedFields['transaction_date']?> />Date
  <select name="Orderby" id="Orderby">
    <option value="invoice_num"<?php if(isset($_REQUEST['Orderby']) && $_REQUEST['Orderby'] == 'invoice_num') : ?> selected="selected"<?php endif; ?>>Invoice Number</option>
    <option value="firstname"<?php if(isset($_REQUEST['Orderby']) && $_REQUEST['Orderby'] == 'firstname') : ?> selected="selected"<?php endif; ?>>First Name</option>
    <option value="lastname"<?php if(isset($_REQUEST['Orderby']) && $_REQUEST['Orderby'] == 'lastname') : ?> selected="selected"<?php endif; ?>>Last Name</option>
    <option value="transaction_date"<?php if(isset($_REQUEST['Orderby']) && $_REQUEST['Orderby'] == 'transaction_date') : ?> selected="selected"<?php endif; ?>>Date</option>
  </select>
    <input name="fieldTracker" type="submit" value="submit" />
  </form>
</div>
<?php
 
if ( !($link = mysql_connect($DB_SERVER, $DB_USERNAME, $DB_PASSWORD)) ) {
    echo 'Could not connect to mysql: ' . mysql_error();
    exit;
}
if (!mysql_select_db($DB_NAME, $link)) {
    echo 'Could not select database: ' . mysql_error();
    exit;
}
$sql    = 'SELECT * FROM tbl_authnet_data';
if (isset($_REQUEST['Orderby']))
{
      $sql    = $sql . " Order By ". $_REQUEST['Orderby'];
}
$result = mysql_query($sql, $link) or die("Query Error: " . mysql_error());
 
if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
$colspan=0;
print "<table align='center' border='0' cellpadding='2' bgcolor='#CCCCCC'>";
print "<tr style='color: white;'>";
if( !empty($checkedFields['invoice_num']) )
{
	++$colspan;
	print "<th>Invoice</th>";
}
if( !empty($checkedFields['firstname']) )
{
	++$colspan;
	print "<th>1st Name</th>";
}
if( !empty($checkedFields['lastname']) )
{
	++$colspan;
	print "<th>Last Name</th>";
}
if( !empty($checkedFields['transaction_date']) )
{
	++$colspan;
	print "<th>Date</th>";
}
 
print "</tr>";
print "<tr><td colspan='{$colspan}'></td></tr>";
 
// Define colors for alternating rows
$color1 = "#fffaf0";
$color2 = "#ffffff";
$row_count = 0;
 
while ($row = mysql_fetch_assoc($result))
{
	$row_color = ($row_count % 2) ? $color1 : $color2;
		print "<tr>";
		foreach ($row as $field => $value)
		{
		if( isset($checkedFields[$field]) && !empty($checkedFields[$field]) )
           	print "<td nowrap='nowrap' align='center' bgcolor='{$row_color}'><font size='2'>{$value}</font></td>";
		}
		print "</tr>";
		$row_count++;
}
print "</table>";
 
?>
</body>
</html>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros