[SOLVED] date ranges

[Ninjakreborn]

What is the easiest way to search by date ranges when you have it saved in the db as a unix time stamp.

I don't want to change the time stamp on this because it's programmed liked that throughout the rest of the system, and the deadline is drawing near, is there a way to search date ranges?

I need to just use the form I created earlier (drop down menu of the month and hte 1st day last day of that month.

I need to be able to get all all reports from February, or march, or whatever.

Any advice?

[roopurt18]

Convert the value you created earlier into a timestamp and use:

 

WHERE date_field >= $begin_date AND date_field <= $end_date

[obsidian]

Since you're not using DATE types, you'll need to come up with the first timestamp possible for the month as well as the last and query for anything in between.

<?php
$month = 2; // February
$firstTS = mktime(0,0,0,$month,1,date('Y'));
$lastTS = mktime(0,0,0,$month + 1,1,date('Y'));

$sql = mysql_query("SELECT * FROM myTable WHERE timestamp >= '$firstTS' AND timestamp < '$lastTS'");
?>

[Ninjakreborn]

Well I have that script you originally helped me setup passing the month over to this page.

 

<?php
session_start();
header("Cache-control: private"); //IE 6 Fix
if(!$_SESSION['member']) {
header('Location: index.php');
exit;
}		
include('../common/config.php');
require_once('../common/class.ezpdf.php');
?>
<?php
$pdf =& new Cezpdf();
$pdf->selectFont('../common/fonts/Helvetica.afm');
$month = mysql_real_escape_string($_POST['month']);
$select = "SELECT * FROM transactions WHERE submitted >= '$month' and submitted <= '$month'";
$query = mysql_query($select);
while ($row = mysql_fetch_array($query)) {
$pdf->ezText("

-- Start Transaction --
User Id:             " . $row['userid'] . "
Biller/Account:   " . $row['biller_or_account'] . "
Type:               " . $row['type'] . "
Amount:            " . $row['amount'] . "
Date:                " . date("d/m/Y", $row['userid']) . "
Date Submitted: " . date("d/m/Y", $row['userid']) . "
Status:             " . $row['status'] . "
-- Stop Transaction --


");
}
$pdf->ezStream();
?>

 

The month being the month that is getting passed from the other script in a timestamp.

I need to narrow it down even more and only let them see one's that they have authorization to see, but first I need this to work.  I know how to add on the other part, what I am trying here isn't working.

I looked and what is getting past fromt hat script you help me setup.

 

<form name="statement" id="statement" action="transpdf.php" method="post">
<select name="month">
<option>Select A Billing Cycle</option>
<option>----------------------</option>
<?php
// something quick here, it simply outputs a drop down menu of the last 3 months.
// It also accounts for leap year, and special days, and all of that other good
// stuff. 
list($m, $y) = explode('-', date('m-Y'));
for ($i = -3; $i < 1; $i++) {
  $ts = mktime(0,0,0,$m+$i,1,$y);
  $n  = date('F', $ts);
  $days = date('t', $ts);
  echo "<option value=\"$ts\">$n 1 - $days</option>\n";
}
?>
</select>
<input name="go" type="image" src="../common/images/button_go.jpg" />
</form>

It's passing the time stamp, which is how I did calculations before on another system I had to get date ranges on.

Any advice on why this isn't working is appreciated, I set it to show this month too, because all the test ones are in there, and I wanted to test it, then change it back when I am done.

[AndyB]

... what I am trying here isn't working.

 

Sorry to hear that. Now if we knew what wasn't working, maybe we could help.

[Wildbug]

MySQL has two functions, UNIX_TIMESTAMP() and FROM_UNIXTIME(), to switch between UNIX timestamps.  If this date problem continues to give you trouble, you could alter your database to use a DATETIME column, just change your queries to include the timestamp conversion functions, and still leave the script's use of timestamps intact.  That way you'd still be able to use the SQL's date/time/grouping to your liking.

 

Or you could just use FROM_UNIXTIME in the query...

 

SELECT blah,blah2 FROM blah_table WHERE MONTHNAME(FROM_UNIXTIME(ts_col)) = "January";
// ... or ...
SELECT *,MONTHNAME(FROM_UNIXTIME(ts_col)) AS d,YEAR(FROM_UNIXTIME(ts_col)) AS y FROM blah_table GROUP BY d,y;

 

Untested, but should set you in the right direction.

[Ninjakreborn]

<?php
session_start();
header("Cache-control: private"); //IE 6 Fix
if(!$_SESSION['cymember']) {
header('Location: index.php');
exit;
}		
include('../common/config.php');
require_once('../common/class.ezpdf.php');
function getlastday($month, $year) {
for ($day = 28; $day < 32; $day++) {
if (!checkdate($month, $day, $year)) return $day-1;
}
}
?>
<?php
$pdf =& new Cezpdf();
$pdf->selectFont('../common/fonts/Helvetica.afm');
$monthvar = mysql_real_escape_string($_POST['month']); // get post
//echo "<br />Month var: " . $monthvar;
$month    = date("m", $monthvar); // get month itself
//echo "<br />Month var: " . $month;
$year     = date("Y", $monthvar); // get year itself
//echo "<br />Year var: " . $year;

$first = "1/" . date("m/Y", $monthvar); // get first day<br />
//echo "<br />first 1 var: " . $first;

$first = strtotime($first); // convert first day to timestamp
//echo "<br />first 2 var: " . $first;

$last  = getlastday($month, $year);
//echo "<br />last 1 var: " . $last;

$last  = $last . date("/m/Y", $monthvar);
//echo "<br />last 2 var: " . $last;

$last   = strtotime($last);
//echo "<br />last 3 var: " . $last;
//exit;

$select = "SELECT * FROM transactions WHERE submitted >= '$first' and submitted <= '$last'";
$query = mysql_query($select);
while ($row = mysql_fetch_array($query)) {
$pdf->ezText("

-- Start Transaction --
User Id:        " . $row['userid'] . "
Biller/Account: " . $row['biller_or_account'] . "
Type:           " . $row['type'] . "
Amount:         " . $row['amount'] . "
Date:           " . date("d/m/Y", $row['userid']) . "
Date Submitted: " . date("d/m/Y", $row['userid']) . "
Status:         " . $row['status'] . "
-- Stop Transaction --


");
}
$pdf->ezStream();
?>

Ok I knew I had to get the first month and the last month on this page, so I went through all the calculations and tested it.

When it come's up the query isn't returning the fields.

I put in some commented codes to help with debugging if I ran into this problem.

 

It's full output is.

Month var: 1175403600 // the timestamp I get from the other script

Month: 04 // the month of that timestamp

Year: 2007 // the year of that timestamp

first 1: 1/04/2007 // the first day of the month

first 2: 1167890400 // the first day of the month in timestamp form

last 1: 30 // last day of this particular month

last 2: 30/04/2007 // last day in date format

last 3: 1244091600 // entire date (with last day) put into a unix timestamp

 

Based on this output I know that all of the stuff is happening correctly.

The only problem is it's not returning the date ranges I am trying to return.

[AndyB]

echo the query. If you're certain it makes sense, test the same query with phpMyAdmin.  At least then you'll know whether the query makes sense considering your database structure and data.