Can't figure out date functions to save my life...

[cmattoon]

Hi all,

I can't seem to get the date/time functions to work properly in PHP. I think what I'm trying to do is pretty simple, but still apparently over my head.

 

1) Receive form input via POST with a date (mm/dd/yyyy)

2) "SELECT * FROM schedule WHERE tx_date='$_POST[date]' ORDER BY tx_time ASC"

3) using a while($row = mysql_fetch_assoc(above)) loop, generate a table (eventually, filter via AJAX with two select boxes.. start/end time).... for now, show all transports scheduled between 00:00 and 23:00 on the specified date.

(If a transport is scheduled at any given time, show time in bold, with tx information. If there isn't one scheduled, show a link with the time. Users click the link to schedule a transport... ensures that nobody schedules more than two trips per half hour block. )

 

Basically..

 

07:00 - John Doe - Place 1 - Place 2

07:00

07:30

07:30

08:00 - John Doe - Place 2 - Place 1

 

etc. Time blocks need to be half hour increments, and two of each time need to be displayed.

 

Anyway,

My problem is, when i use the following code, it always starts at 16:00...

 

$txdate = $_POST['sch_date1']; 				// converts text input into DATE datatype, as well as UNIX
$segs = explode("/",$txdate);
$datestr = $segs[2].$segs[0].$segs[1];
$unix_date = strtotime($datestr);
$human_date = date("Y-m-d",$unixdate);

echo $human_date."<p>"; 
$i = "0000";                        
echo $i."<p>";                   
$i = date("H:i",$i);  //<<<----- looks like the problem starts here....
echo $i."<p>";                  
$t = strtotime($i);		// $t == UNIX date/time
$j = date("H:i",$j); 	      // $j == formatted date/time (HH:mm)

echo $j."<br>";


$x = 0;
while($x < 10){
$t = $t + (60*30);
$j = date("H:i",$t);
echo $j."<br>";
$x++;
}

 

 

When the script is run, with 5/22/2010 as the date, this is the output:

2010-05-22
0000

16:00

16:00
16:30
17:00
17:30
18:00
18:30
19:00
19:30
20:00
20:30
21:00

 

 

Maybe I'm going about this all wrong???

 

Thanks in advance!

[kenrbnsn]

First, if the date is coming into your program as mm/dd/yyyy, this is a format that strtotime understands, so you don't need this code:

<?php
$txdate = $_POST['sch_date1']; 				// converts text input into DATE datatype, as well as UNIX
$segs = explode("/",$txdate);
$datestr = $segs[2].$segs[0].$segs[1];
$unix_date = strtotime($datestr);
$human_date = date("Y-m-d",$unixdate);
?>

Just do:

<?php
$human_date = date('Y-m-d',strtotime($_POST['sch_date1']));
?>

 

The second problem is caused because you're over-thinking the problem. The easiest way to get the list you want is with a simple loop:

<?php
echo $human_date . "<br>\n";
for ($i=0;$i<24;++$i) {
   $hr = sprintf('%02d',$i);
   echo $hr . ":00<br>\n";
   echo $hr . ":30<br>\n";
}
?>

 

Ken

[cmattoon]

Awesome, thanks!

 

One more question... if I do this:

$x = $hr . ":00";
$y = $hr . ":30";

 

will I be able to do:

mysql_query("SELECT * FROM schedule WHERE tx_date='$sch_date' AND tx_time='$x'")

 

I'm not sure how picky PHP and SQL are with the different date/time formats.. I seem to run into errors when I do anything but store dates as VARCHAR and compare the two literal strings "05/22/2010" == "05/22/2010", but this doesn't do much for sorting purposes.

[jcbones]

You can store dates as DATE with the format of YYYY-MM-DD.  That will allow you to run MySQL's native date functions against that column.

[kenrbnsn]

If the column is of type DATETIME you can do

<?php
$q = "SELECT * FROM schedule WHERE tx_datetime='$sch_date $x:00'";
$rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
?>

 

Ken

[cmattoon]

Ok.. maybe this belongs in the SQL section, but I hate to start another thread..

I've got the code to generate two of each half-hour blocks like such:

07:00

07:00

07:30

07:30

08:00

08:00

etc

 

I'm not sure how the SQL query or logic works to generate this chart and fill with information.. I can handle the formatting, links, etc.. but I've always done "select where", and a while loop to echo all information... which would work if there were two records for each time in the table (right?). Your query works fine for pulling trip_time from the database, as it's stored as varchar(5) --ie "07:00". I've gotten that far..

 

How do I handle if no data exists (say, no 7:00 trips at all, or only one 7:00)?

$sql2 = mysql_query("SELECT * FROM schedule WHERE trip_time='$x' AND trip_date='$human_date'");
	while($row = mysql_fetch_assoc($sql2)){
		if($row[khfd] == ""){
			$khfd = ""; // trip ID # (10-1234)
			$trip_time = "<a href=\"../wcv/tx2.php?t=$row[trip_time]&d=$human_date\">$row[trip_time]</a>";
			$ptid = NULL;
		}else{
			$khfd = $row[khfd];
			$trip_time = $row[trip_time];
			$ptid = $row[pt_id];
		}
		echo "<tr><td>$khfd</td><td>$trip_time</td><td>$row[ref_fac]</td><td>$row[rec_fac]</td><td>$row[pt_id]</td><td>$row[driver]</td><td>$row[status]</td></tr>";
	}

 

I find myself doing TWO SQL queries, one for X:00 and one for X:30. These loops are inside the loop you provided earlier (I modified the constants to be shift start/end time)

for ($i=$s_start;$i<$s_end;++$i) {   
$hr = sprintf('%02d',$i);  

..sql stuff...
}

 

But this only shows data for the one trip I have entered... I cant seem to get it to generate blank table rows.. if you remove the SQL and make it:

for ($i=$s_start;$i<$s_end;++$i) {   
$hr = sprintf('%02d',$i);   
$x = $hr . ":00";
$x2 = $hr . ":00";
$y = $hr . ":30";
$y2 = $hr . ":30";
echo "<tr><td></td><td>$x</td><td colspan=\"100\"></td></tr>";
echo "<tr><td></td><td>$x2</td><td colspan=\"100\"></td></tr>";
echo "<tr><td></td><td>$y</td><td colspan=\"100\"></td></tr>";
echo "<tr><td></td><td>$y2</td><td colspan=\"100\"></td></tr>";