Need help with Dates

peppino

my database is as follows:

| eventEndMonth | eventEndDay | eventEndYear |

| March | 2 | 2004 |

how do i query the database to select all upcoming dates, i would also like to select past dates. how do i do this? im having trouble figuring this one out. im new and just learning so ANY help is much appreciated.

Thanks!
peppino

LordShryku

Well, you're database structure is way wrong. You should be using a timestamp field or a date type field to store these, and this would be much easier.

peppino

thanks for the help. how should i take date data from my Add Event form and what is the best way to store the event start date and event end date into the database?

LordShryku

Personally, to store dates, I usually generate the timestamp in php and store it in a bigint field. Look at [man]time[/man], [man]strtotime[/man], and [man]mktime[/man] for ways to do this. Then to get your results, it's much easier.

$now = time();
// future events
$sql = "SELECT fields FROM table WHERE eventDate > ".$now;
// past events
$sql = "SELECT fields FROM table WHERE eventDate < ".$now;

dalecosp

Originally posted by LordShryku
Personally, to store dates, I usually generate the timestamp in php and store it in a bigint field. Look at [man]time[/man], [man]strtotime[/man], and [man]mktime[/man] for ways to do this. Then to get your results, it's much easier.

Hear, hear!! 😉

To grab from your form:

        $month=$_POST['month'];
        $date=$_POST['date'];
        $year=$_POST['year'];
        $hour=$_POST['hour'];
        $minute=$_POST['minute'];
        $meridian=$_POST['meridian'];
        $submit=$_POST['submit'];
        $title=trim(addslashes($_POST['title']));
        $description=trim(addslashes($_POST['description']));

    // create a UNIX timestamp --- use PHP's mktime() --- the prototype is below

    //int mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]])

    $checkmonth="$month 1 $year";
    $checkmonth=strtotime($checkmonth);
    $makemonth=date("m", $checkmonth);
    if ($meridian=="pm") {
            if ($hour==12) { $nothing=null; }
            else { $hour=$hour+12; }
    }
    $TS=mktime($hour, $minute, 0, $makemonth, $date, $year);

    $sql="insert into calendar values($TS, '$title', '$description', '', '')";

Something I did a while back.

The "checkmonth/makemonth" stuff is because I'd gotten the month name from the form instead of the month number, and mktime can't handle it that way....

HTH,

🙂