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

PHPMySQL Server

Avatar of undefined
Last Comment
katlees

8/22/2022 - Mon
johanntagle

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?
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
johanntagle

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)
Your help has saved me hundreds of hours of internet surfing.
fblack61
katlees

ASKER
Website Here
The code I am referring to is for the schedules to the right of the scrolling image.
johanntagle

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.
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

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
johanntagle

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.
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
katlees

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