Ok so I have a working MySQL query, and I want to take what is currently working…and fine tune it.
Here’s the current query:
SELECT * FROM events WHERE start AND end = DATE(NOW()) LIMIT 1What I am trying to do is make a ticker that shows that there is an event…say within 12 hours of the start date, but when “end” is after NOW(), the next result will be shown.
I don’t need the else statement or anything I just need to figure out how to do this with the query.
Any ideas?
i’m a little confused on exactly what you want. you said if end is after now, but your query shows both end and start. do you want to compare start at all, or just make sure it hasn’t ended? if you just want to make sure it hasn’t ended, try:
SELECT * FROM events WHERE end < NOW() LIMIT 1
No I don’t want it to be displayed until about 12 hours before “start”, but no longer displayed after “end”. Does that make sense? I was half paying attention when I wrote it sorry. My assistant was talking my ear off. lol.
yes that does, what about how it chooses events? is completely random ok or do you want the event starting soonest to the current date?
The query I originally posted does this, but if the event is not an “all day (12 to 12) event” it doesn’t show the event. It skips right over it. So I basically want where if the event starts at 6pm, it will show the event at 8am that morning, and if the event ends at 9pm, at that time, it will show the next event (which i already have that functionality in place, i just need the query for the first one, they’re not interchangeable.
i’m sorry i’m not trying to be a pain it’s just a little confusing. it makes more sense now but it’s still a little foggy. i want to clarify: the way this works with php is the information is loaded dynamically from the database every time the page is loaded. to affect that you can either have it write out the page and check for a datestamp, or create a session and create an expire, it isn’t done in the original query because that will run EVERY time the page loads. I have to ask, how is it you have a different query for the next event? i don’t quite understand that.
SO, if it hasn’t started, it hasn’t ended, right?. your query should simply look for
SELECT * FROM events WHERE DATE_ADD(NOW(), INTERVAL 12 HOUR) < start LIMIT 1but that won't stop it from picking an event that starts a month from now, over an event that starts tomorrow. to do that, use order by:
SELECT * FROM events WHERE DATE_ADD(NOW(), INTERVAL 12 HOUR) < start LIMIT 1 ORDER BY start
Basically this mysql connection has a else statement.
So if there is no event with todays date, it will display something else (it’s a ticker, content changes every day), but if there’s an event on the calendar, i want it to automatically display the event, then when it’s ended the else statement kicks in.
For example: Event ABC, Starts at 7pm Server Time. and Ends at 10pm Server Time.
The query should check the database for anything with 12 hours of the current time. So if it is 4:00am on the day of the event, the ticker will not show anything until 7am on the day of the event. Then at 10pm since there is no other event scheduled for that day, there will not be any results, hence the else statement will automatically kick in, and go through the motions again until another event is listed with todays date, and Now() is within 12 hours of “start”.
I already have the else statement in place, I just need the query to be fine tuned.
I hope this makes much more sense.
ok so the the NEXT thing is not necessarily an event. that makes a lot more sense. something to understand about php though is that it is a dynamic language, so it is executed every time the page loads. so it’s not a matter of setting it to start at 7am and end at 10pm, but rather does it right now, on the current load, match the conditions we want. so the existing else statement already checks to see if there is a matching event and moves on to the new item if not? where is the query for that, how do you know that there is a matching event and you want to execute all the code to display it?
now i already mentioned specifying the end in the query makes no sense. if it hasn’t started it hasn’t ended, and you can’t tell it to stop displaying 12 hours from now with the original sql query itself. but what you can do is grab the end time and save everything to a session
[php]
// I don’t know what you are using to connect to your database, so assume result$ is the result of the query
// SELECT * FROM events WHERE DATE_ADD(NOW(), INTERVAL 12 HOUR) < start LIMIT 1 ORDER BY start
if ((!isset($SESSION[‘end’])) | ($SESSION[‘end’] > time())) {
session_start();
$_SESSION = $result;
} else {
// here you put the code that USES $_SESSION (instead of $result) to display the data in the ticker
}
[/php]
I really don’t want to change any of the existing code outside of the MySQL statement. Here’s what I currently have.
[php]
date_default_timezone_set(‘America/New_York’);
$con = mysql_connect(“localhost”,"******","******");
if (!$con) { die('Could not connect: ’ . mysql_error()); }
mysql_select_db("******", $con);
$result = mysql_query(“SELECT * FROM wp_aec_event WHERE NOW() > start LIMIT 1 ORDER BY start”);
if ( mysql_num_rows( $result ) > 0 ) {
while($row = mysql_fetch_array($result)) { ?>
Sunday Content
<? break; // next day of the week, etc. ?>[/php]
I have everything in place except for getting events 12 hours before they are schedule to start on the calendar.
well this if/else would just paste INSIDE your existing one, like this:
[php]<?php
date_default_timezone_set(‘America/New_York’);
$con = mysql_connect(“localhost”,"******","******");
if (!$con) { die('Could not connect: ’ . mysql_error()); }
mysql_select_db("******", $con);
$result = mysql_query(“SELECT * FROM wp_aec_event WHERE DATE_ADD(NOW(), INTERVAL 12 HOUR) < start LIMIT 1 ORDER BY start”);
if ( mysql_num_rows( $result ) > 0 ) {
while($row = mysql_fetch_array($result)) {
if ((!isset($SESSION[‘end’])) | ($SESSION[‘end’] > time())) {
session_start();
$_SESSION = $result;
} else {
// here you put the code that USES $_SESSION (instead of $result) to display the data in the ticker
}
// Event Listing
}} else {
date_default_timezone_set('America/New_York');
$time = new DateTime();
$time->sub(date_interval_create_from_date_string('2 hours'));
switch ($time->format('l')) {
case 'Sunday': ?>
Sunday Content
<? break;
// next day of the week, etc.
?>[/php]
but again something you are not understanding it seems: PHP is run EVERY time a page is loaded. I load up yoursite.com and you load it 2 seconds apart, that mysql query just ran two times and got whatever data matches that query twice. that will happen EVERY time ANYONE loads your page.
so what that means, is without changing more than your query it is IMPOSSIBLE to have it CACHE a specific event, to make sure it keeps showing that event until it ends. it simply doesn’t work that way. i’m sorry, what i have showed you i think is your best alternative, there are others. but I can guarantee that just changing your query won’t get what you are asking for.
i’m not really interested in adding additional coding. I appreciate the information you’ve provided. I simply just wish to adjust the MySQL query to get this information displayed. As I had previously stated, It does display the information with no issues currently, however it does not display the event before it starts. only when it starts. That is truly the only modification i’d be willing to make as there is more to the code than just what I’ve posted. Altering anything other than the query would require a recode of the page, and I don’t have the time or energy to do something that huge.
Again, I appreciate the solutions you’ve provided, however, I just need the mysql query to display x hours before “start” that’s really all i’m looking for.
i understand, but what you are looking for is IMPOSSIBLE without changing more than the query. i can promise you no one else will give you a different answer.
i hate to ask , but why exactly would the change below require ANY additional coding?
replace this:
[php]<?php
// Event Listing[/php]
with this:
[php]<?php
if ((!isset($SESSION[‘end’])) | ($SESSION[‘end’] > time())) {
session_start();
$_SESSION = $result;
} else {
$result = $_SESSION;
}
// Event Listing
// here you put the code that USES $result to display the data in the ticker[/php]
that’s it. done. no more changes. if it has a session result that hasn’t expired, set it to $result and use it. if not get a new one and save to a session to be restored to $result whenever the page is loaded.
to explain further, when you set your query to only pick those that start more than 12 hours ahead, then once it starts in 11 hours the query will no longer match, and it will stop displaying. which is the same reason it stops displaying once the event starts now. that’s simply how php/mysql work.
I would not say it’s impossible. After doing a bit of digging I was able to find this line of code with ultimately resolved my issue and gave me exactly what I was looking for:
mysql_query("SELECT * FROM event WHERE start >= now() - INTERVAL 1 DAY LIMIT 1");Thank you again for your suggestion.
that is not what you want. it’s very similar to the date_add one i gave you, both suffer the problem that the query runs EVERY TIME the page is loaded.
so it may match now, but when start is LESS than 1 day before now it will STOP working. i’m sorry but you just aren’t getting how this works. maybe you aren’t actually reading anything i say. go ahead and don’t believe me but I PROMISE that when that start date is less than a day away it will STOP displaying just like it stops NOW when it reaches the start time. all you did was push it back EVEN FARTHER, so it will stop SOONER.