Link to home
Start Free TrialLog in
Avatar of katlees
katleesFlag for United States of America

asked on

Help with Date Function

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

Avatar of johanntagle
johanntagle
Flag of Philippines image

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?
Avatar of katlees

ASKER

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
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)
Avatar of katlees

ASKER

Website Here
The code I am referring to is for the schedules to the right of the scrolling image.
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.
Avatar of katlees

ASKER

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?
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.
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of katlees

ASKER

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