Help with Date Function

katlees
katlees used Ask the Experts™
on
I have the code below and the only problem I am having is it drops off an event that is occuring for the current date. Example...

If an event is entered for today (2012-05-23) at 15:30:00 it should display until 15:30:01

Nothing is showing up for 2012-05-23

Any help?
  <?php

$currenttime = date('H:i:s');
			$currentdate = date('Y-m-d H:i:s');
				$sql = "SELECT * FROM Events WHERE LevelID LIKE '%1%' AND StartDate >= '$currentdate' ORDER BY StartDate, StartTime Limit 10";
			

	$sql_result = mysql_query($sql);

	$calendar_html = "";
	$first_one = 1;
echo"<div class=\"inner\">\n";
	while ($dept_button_row = mysql_fetch_assoc($sql_result))
	{
		$dept_button_id = $dept_button_row['ID'];
		$dept_button_Event = $dept_button_row['Event'];
		$dept_button_StartDate = $dept_button_row['StartDate'];
		$dept_button_StartTime = $dept_button_row['StartTime'];
		$dept_button_LocationID = $dept_button_row['LocationID'];
		$dept_button_OpponentID = $dept_button_row['OpponentID'];
		$dept_button_Gender = $dept_button_row['Gender'];
		$dept_button_Level = $dept_button_row['LevelID'];
		$event_time = mysql2timestamp1($dept_button_StartDate);
        $event_display_date = date("m/d/y", $event_time);

		$display_time = date('g:i A', strtotime($dept_button_StartTime));
		
		echo"<div class=\"wrapper\">
                                    	<a href=\"#\" class=\"link1\">$event_display_date</a><div class=\"text1\"><a href=\"#\">$dept_button_Event</a></div>
                                        
                                         <a href=\"#\" class=\"link11\">$display_time</a>
                                    </div>
                                    <div class=\"border\"></div>\n";
	}
	echo "</div>\n";
	?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Is the application and the database on the same server?  If not, are the clocks on the two machines synchronized?  Maybe the app is forcing a different timezone?  Maybe you can print the computed $currentime to help troubleshoot.

BTW  "WHERE LevelID LIKE '%1%' " is REALLY UGLY - sorry, there's no other way to put it.  Any "LIKE '%whatever%'" in a query will not make use of any index so if the table grows, you'll definitely get performance problems later on.  Are you really trying to match value that has "1" in any digit?

Author

Commented:
Yes, on the same server..
$currenttime displays as 16:42:18 and $currentdate displays as 2012-05-23 16:42:18

As for the %1%

There are 5 different options for this field -
1 - Varsity
12 - Varsity/Expos
2 - Expos
2/3 - Expos/Bullets
3 Bullets

So this one, I need the 1 or 12 as they are varsity games
Top Expert 2012

Commented:
You should revisit that design.  Read up on database normalization and how to handle one-to-many and many-to-many relationships properly on the database.  It involves putting the data to a separate table and doing joins.  I assure you, you will have performance issues later on if you don't.

Anyway, going back, please also post table definition and sample data (even just the output of "select StartDate from Events where StartDate>='2012-05-23'" will do)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Website Here
The code I am referring to is for the schedules to the right of the scrolling image.
Top Expert 2012

Commented:
You are showing me the symptoms of the problem - I am asking for the values on the database to try to determine the root cause of the problem.

Author

Commented:
The fields in question:
StartDate type is date  value in database is 2012-05-23
StartTime type is time value in database is 17:30:00

Is this what you are asking for?
Top Expert 2012

Commented:
Yes that will do.  It's because StartDate is just date, when compared to your current date it assumes it's time is "00:00:00"  so evertything with StartDate='2012-05-23' is before $currentdate.  Change $currentdate to just date('Y-m-d') then change your query to

SELECT * FROM Events WHERE LevelID LIKE '%1%'
AND StartDate >= '$currentdate' AND StartTime >= '$currenttime'
ORDER BY StartDate, StartTime Limit 10

Alternatively you can just combine StartDate and StartTime to just one DATETIME column so you can just make one comparison to date('Y-m-d H:i:s').  Actually you don't have to form the current date and time in php but just use the mysql function NOW() for the current date and time.
Top Expert 2012
Commented:
Let me correct that.  It should be

SELECT * FROM Events WHERE LevelID LIKE '%1%'
AND (  (StartDate = '$currentdate' AND StartTime >= '$currenttime')
            OR StartDate > '$currentdate' )
ORDER BY StartDate, StartTime Limit 10;

The previous version will exclude events after May 23 but scheduled before the current time.
Because you have date and time separated to two columns the query is a bit more complex.  If you had just one DATETIME column the filter is a simple "where StartDateTime >= now()"

Author

Commented:
Thank you so much. I will look into the %1% issue and see how I can improve the way I do it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial