Date comparison with non date fields in DB

[future_man]

Here's an interesting one.. (or at least I think so):

I'm having to modify an existing application which is storing dates in somewhat of a non-orthodox way.

Dates are stored in a table with the following int fields: day, month, year.

What I need to do is come up with some simple (hopefully) code only to pull the next three dates from today's date.

Here's the table schema:

Field Type Attributes Null Default Extra 
show_id   int(11)   No    auto_increment  

month   int(2)   Yes  NULL    

day   int(2)   Yes  NULL    

year   int(4)   Yes  NULL    

weekday   varchar(20)   Yes  NULL    

location   varchar(100)   Yes  NULL    

venue   varchar(100)   Yes  NULL    

details   varchar(100)   Yes  NULL    

Any ideas?

Thanks!

[nnichols]

I would suggest that you add a DATE field. It is easy to populate the new field using -

UPDATE table SET new_date_col=CONCAT(year,'-',month,'-',day);

Hope this helps 😉

[future_man]

Sounds like a good approach. I'll give it a shot.

Any advice on the best way to do the date comparision? I'm thinking something along these lines, but its not working yet:

// get todays date.
$thisDay = date( "Y m d");
// select all the shows in the database
$result = mysql_query("SELECT show_id, month, day, year, location, details, venue, date 
FROM $database_table 
WHERE date >= '$thisDay' 
ORDER BY year, month, day ASC LIMIT 3",$db) or die_now("<h2>Could not select shows</h2>");

Thanks!

[gtibok]

// get todays date.
$thisY = date("Y");
$thism = date("m");
$thisd = date("d");
// select all the shows in the database
$result = mysql_query("SELECT * FROM $database_table WHERE year='$thisY' AND month='$thism' AND day='$thisd' ORDER BY year, month, day ASC LIMIT 3",$db)
or die_now("<h2>Could not select shows</h2>");

but try nnichols ideea cuz it's mutch easy that way

[future_man]

Ok, thanks guys! Here's what I have now that seems to be working as needed:

// get todays date.
$thisDay = date( "Y m d");

// select all the shows in the database
$result = mysql_query("SELECT show_id, month, day, year, location, details, venue, date 
FROM $database_table 
WHERE DATE_FORMAT(date, '%Y %m %d') >= '$thisDay' 
ORDER BY year, month, day ASC LIMIT 3",$db) 
        or die_now("<h2>Could not select shows</h2>");

[nnichols]

Do you want to show just the first 3 shows starting from todays date or do you want all shows for the next three days??

[future_man]

Just the first three shows starting from today's date.

I think I've got it working.

Thanks so much for your help.

I appreciate it.

[nnichols]

instead of -

WHERE DATE_FORMAT(date, '%Y %m %d') >= '$thisDay'

you could use -

WHERE date >= CURDATE()

[future_man]

Wow! That does seem more elegant. Thanks!

[future_man]

Ok, one more question. My update statement doesn't seem to be working right:

$result = mysql_query("update $database_table
        set month='$month', day='$day', year='$year', weekday='$weekday', venue='$venue', location='$location', details='$details', date=CONCAT($year,'-',$month,'-',$day)
        where show_id = '$the_id'",$db) 
        or die_now("<h2>Could not change show</h2>");

The date value is getting set as 0000-00-00.

What am I doing wrong here?

[nnichols]

date='$year-$month-$day'

[future_man]

Ahh!! The simple things in life.

Thanks again for all your help.