only display upcoming dates

trendMM

A while back I wrote a basic script for a shows section on my bands website and now I would like to place an "upcoming shows" sidebar on some of the pages so people will notice there are shows coming up before they even check out the shows page. What I would like to do is keep all the dates of our shows up but have the side bar only display the next 4 upcoming shows we have that are on the current day or in the upcoming future and filter out all the past shows that are prior to the current day. Make sense?

The way I have my script set up is that you select the day (1-31), month (1-12), and year (2006-2012). Then they are all stored into their own fields within the database table. I know I can do something with the "select" function and compare those fields together against the current date but how? Do I combine all of the fields like (2006-31-4) or what?

If someone could please help me out or give me a snipplet of how the select funciton would be setup that would be great. Thanks in advance!

Evan

Weedpacket

If you had used one of the database's date/time types to store the date it would have been a simple comparison with NOW(). As it stands you'd have to either pull all those bits out, and in PHP use [man]mktime[/man] to make a timestamp, comparing it with [man]time[/man].

The drawback there is that you have to pull out all the records before finding which ones you want to keep.

The alternative is to use some of the database's string functions to make a date string, convert it to a date, and compare it with NOW() in the database. What exactly those string and date functions are depends on which DBMS you're using. Consult its documentation for details.

A second alternative of course is to use a date/time type.

ysu

actually there is an STR_TO_DATE function in mysql...
( Edit: eh sorry it's only in mysql 5 )
( http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html )

...or you could do a mathematical calculation of seconds and compare that...

...but I suggest fixing that wrongly selected storage method instead. It will give you endless pain in the future. Dates should be stored in a 'date' type field.

trendMM

thank you both for your replies. much appreciated. can you please explain how i would code it to use the time and date function instead to be stored into the database because i can see where that would make it a lot easier on the way back out. i'm pretty lost on what to do to make that happen though. thanks!

ysu

First add a field to the db which is a 'date' type.
On entering data, combine the fields to one and call strtotime()

$realDate = strtotime($year.'-'.$month.'-'.$day);
or actually
$strDate = date('Y-m-d' , strtotime($year.'-'.$month.'-'.$day));

after this dates are comparable with each other.
To select the furure dates,
$now = $date('Y-m-d');
then simply:
$sql = "select fields from table where date-type-field>'$now' ";

If you want to transfer old data to the new system, simply get the data out, combine it and put it back. When you finished you can delete the old fields.

I'm not sure but maybe a direct string combination of the three would work for this purpose...I'll check it.

Edit: is this the kind of info you were after?
Edit2:
Ok it works. While there I tested if concatenating the date parts would work ...and it does too 😉
so you can use this short method (I had the date parts in smallint fields):
SELECT fields FROM table WHERE concat(year,'-',month,'-',day)>now()

but I still recommend chaning to date, it's not a biggie and much better.
If you need parts you can still select them using year() for example - just check the mysql manual, look for 'date and time functions'

this would be the conversion method:
update table set realdatefield= concat(year,'-',month,'-',day);

bradgrafelman

Actually, if you don't have MySQL5, and you don't add the date column as mentioned above (though I would recommend this instead), you can use something like this:

SELECT * FROM `myTable` WHERE DATE( concat(`year`,'-',`month`,'-',`day`)) > CURDATE()

trendMM

thank you both for helping me out. i will try the code out in the morning for the strtotime function and let you guys know if it works. if so i will set this topic as resolved.

to get this right though. while posting to the db i want to convert all 3 fields into one field using the strtotime function?

$strDate = date('Y-m-d' , strtotime($year.'-'.$month.'-'.$day));

thanks again!

ysu

You could simply put it together of course, the strtotime is only ensuring that it's a real date you're entering the database. But it needs to be in one string if that's what you mean by your quesiton.

You can put a check on it after that line to see if the date is within your accepted range.

trendMM

well here let me show you an example of what i mean and let me know if is the right way to do this.

// Include config file
include('common.php');

$link = dbConnect();

// Fetch the current time
$posted = time();

// Convert Month Day Year to Realdate
$date = date('Y-m-d' , strtotime($year.'-'.$month.'-'.$day));

// Build Query
$query = "INSERT INTO shows (date, venue, location, time, body)
VALUES('$date', '$venue', '$location', '$time', '$body')";

// Execute Query
$result = @($query)

thanks a lot!

ysu

It's a good start, only the data validation is missing. 🙂

Example:
If for any reason the posted year, month and day does not create a real date or creates a date out of your accepted range (which I don't know) you might want to create an error message without finishing the transaction.

Weedpacket

You can use [man]checkdate[/man] to check that the date is submitted is valid (then the date(strtotime()) business is redundant).

If you already have separate $year, $month, and $day fields then (assuming they're integers) [man]mktime[/man] is a more efficient way of assembling them into something date() can use. It's also a bit more robust; $month=2, $day=30 is correctly recognised as being an unorthodox way of referring to March the second.

trendMM

What do you guys think would be better, mktime or strtotime? If strtotime then what am I missing in my code to make it work correctly?

ysu

Use whichever you like, both are ok.
But if you have difficulties with one, use the other 🙂

Or tell us what's not working, and we may be able to help.
What's happening? What should be happening? What are the input and output values?

trendMM

hey guys, thanks once again for all of your help. i was wondering though. i've messed with mktime a little bit here and there to see how it works.

#1. i was wondering, is there any possible way i can use it when checking the db for dates that are today or in the future?

SELECT * FROM `events` WHERE sdate (date("M-d-Y", mktime(0, 0, 0, $smon, $sday, $syr)) > CURDATE()

#2. i had thought about using mktime to just store a timestamp into the $sdate field and that worked.

$sdate = mktime(0, 0, 0, $smn, $sdy, $syr);

but then i realized even though i'd personally just like to store the timestamp in that i have no idea how to pull it back out into seperate input boxes when someone visits the edit page for a certain event.

two methods i have in my head. i'd rather use #2 with the timestamp if i can get help with my edit page problem but if you guys think #1 is just as good please let me know. any help would be greatly appreciated!

thanks in advance!

trendMM

*bump

ysu

trendMM wrote:
#1. i was wondering, is there any possible way i can use it when checking the db for dates that are today or in the future?

you don't have to, if you're using a 'date' type field for storing dates.
where mydate>now() works.
If you look around on the mysql manual 'date time functions' part I'm sure you can find a funcion to compare the today's datestamp as well.
Edit: actually you don't even have to do that, get the current timestamp from the program and compare that.

trendMM wrote:
...but then i realized even though i'd personally just like to store the timestamp in that i have no idea how to pull it back out into seperate input boxes when someone visits the edit page for a certain event.

on solution: you can separate strings by certain characters using the explode() function. for example 1-2-3 can be separated by the dash.