Avatar of Jonathan Greenberg
Jonathan GreenbergFlag for United States of America asked on

PHP Script to Limit MySQL Data View By Date

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>

Open in new window

PHPMySQL Server

Avatar of undefined
Last Comment
Jonathan Greenberg

8/22/2022 - Mon
Blaz

$sql    = 'SELECT * FROM tbl_authnet_data';

// the sql limit by date - should occur before the orderby
$sql = $sql . " WHERE transaction_date < 2008-04-30 17:00:00 AND transaction_date > 2008-01-01 00:00:00"

if (isset($_REQUEST['Orderby']))
{
      $sql    = $sql . " Order By ". $_REQUEST['Orderby'];
}
ASKER
Jonathan Greenberg

Thanks, but I'm seeing only a blank page now.  Just for the sake of simplicity, I've put this line:

     $sql = $sql . " WHERE transaction_date < 2008-05-15 00:00:00 AND transaction_date > 2008-05-17 00:00:00"

after this line:
     if (isset($_REQUEST['Orderby']))

Is this right?
- -

Re: $sql = $sql . " WHERE transaction_date < 2008-05-15 00:00:00 AND transaction_date > 2008-05-17 00:00:00"

You will have empty set. transaction_date cannot be in the same time less then 2008-05-15 and more then 2008-05-17. It can be in the same time more then 2008-05-15 and less then 2008-05-17, like the following:
$sql = $sql . " WHERE transaction_date > 2008-05-15 00:00:00 AND transaction_date < 2008-05-17 00:00:00"
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Jonathan Greenberg

Yes, of course, thanks Alex.

OK, I've reversed the 2 values, but I still see only a blank page.  I now have this line:
     $sql = $sql . " WHERE transaction_date < 2008-05-17 00:00:00 AND transaction_date > 2008-05-15 00:00:00"

after this line:
     if (isset($_REQUEST['Orderby']))

There definitely are DB entries which took place between 5/15/08 and 5/17/08.  But the page is blank.
ASKER CERTIFIED SOLUTION
- -

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Jonathan Greenberg

Great, that worked.  I had to use quotes, and I also had to end the line with a semicolon.

Now, how do I set the date with html form elements, i.e., drop-down menus for the dates?  What PHP code do I need to insert in place of the dates that I've up to now hard coded into the script?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Jonathan Greenberg

Should this work?

In what format do I have to submit the dates in the text fields?  I've just tried this with "2008-05-15 00:00:00" in the FROM text field and "2008-05-17 00:00:00" in the TO text field.  After I hit SUBMIT, the page just comes back with the text fields empty and the table consisting only of the 4 headers but no rows.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
- -

please look at http://php.net/strtotime - it tries to parse any date format.
ASKER
Jonathan Greenberg

Thanks, that's really great.