[UPHPU] MySQL dates

[Justin Giboney]

I should have designed the db using a date field (or even better a  
long integer with unix timestamp), but I didn't plan well, and now I  
suffer.

Justin Giboney



On Aug 8, 2007, at 2:02 PM, Matthew Frederico wrote:

> On 8/8/07, Justin Giboney <giboney at giboneydesigns.com> wrote:
> In my current mysql db, i have 3 fields one for the day,  one for the
> month, and one for the year. I need to compare them to the current  
> date.
>
> This is the code I have so far, but it doesn't work.
>
> $sql = "SELECT * FROM Movies WHERE CURDATE() > (DVD_Release_Year,
> DVD_Release_Month, DVD_Release_Day) AS theDate LIMIT 0,7";
> $result = @mysql_query($sql);
> while ($row = mysql_fetch_array($result)) {
>         echo $row['Movie_Title'] . '<br />';
> }
>
> Also I dont think your query will work with the "AS theDate" after  
> your WHERE clause.
>
> You could also try this as a start:
> SELECT * FROM Movies WHERE CURDATE() > CONCAT_WS 
> ('-',DVD_Release_Year,DVD_Release_Month,DVD_Release_Day) ;
>
> CONCAT_WS = Concatenate "with separator".
> Mysql is prety smart about dates and times and how the math works.
>
> Just one question: Why didn't you just use a "DATE" field in your  
> table?
>
> -- 
> -- 
> -- Matthew Frederico