Inserting dates into MySQL from select dropdowns

[MarcD]

Hello,

I am developing an application where my client can post announcements to be displayed on his website between certain dates.

For this, i used a form with 2 sets of select dropdown boxes.
BegindateDay
BegindateMonth
BegindateYear

EnddateDay
EnddateMonth
EnddateYear

After posting, the date elements get assembled under one variable:

$beginDate = $_POST['begindateYear'] . '-' . $_POST['begindateMonth'] . '-' . $_POST['begindateDay']; // Formats to YYYY-MM-DD

$endDate = $_POST['enddateYear'] . '-' . $_POST['enddateMonth'] . '-' . $_POST['enddateDay']; // Formats to YYYY-MM-DD

$bDate = strftime("%Y-%m-%d", $beginDate);
$eDate = strftime("%Y-%m-%d", $endDate);


Then the $sDate and $eDate variables are inserted into MySQL.

Now for some reason, MySQL doesn't like this, because when i check my table in PHPMyAdmin, the dates are both 1970-01-01.


What am i missing?

Thanks in advance

[Phill Pafford]

Hmm,

Print out $bDate & $eDate, If the formats are already in this format: "Formats to YYYY-MM-DD " then what are you trying to do with this: strftime("%Y-%m-%d", $beginDate);

maybe just use the $beginDate & $endDate.

[MarcD]

Hi,

Thanks for your reply.

When i print them out i get the following (i selected 21st of August 2008 in the dropdown boxes):
$beginDate = 2008-August-21
$endDate = 2008-August-21

I checked my PHPMyAdmin and without the "strftime("%Y-%m-%d", $beginDate);" bit, it inserts 0000-00-00 into MySQL (i.e. the default value for a Date field).

[Phill Pafford]

Sounds like your not passing them right, please post your SQL query

[MarcD]

This is my query:

$query = "insert into tblAnnouncements(begindate, enddate) values('$beginDate', '$endDate')";


Although, just thinking out loud:
Isn't the problem that when i printed them, it printed the full month, instead of the numeric month (i.e. August instead of 08)?
Should MySql be passed the numeric version like 2008-08-21? This was what i was trying to achieve with the "strftime("%Y-%m-%d", $beginDate);"

[Phill Pafford]

Yes MySQL date format does not accept August it needs to be 08.

I would also print the SQL query to the screen to see what it looks like before running it.

[MarcD]

Query: insert into tblAankondiging(startdatum, einddatum) values('2008-August-17', '2008-August-22')


Thats what the printed query looks like.
So the dates have to be converted to a MySQL format.

How would i do that?

Because i tried:

$bDate = strftime ('%d-%m-%Y',strtotime ($beginDate));
$sDate = strftime ('%d-%m-%Y',strtotime ($startDate));


But that doesnt work either.


Thanks in advance

[Phill Pafford]

My Advise would be to pass the month as 08 instead of August.

of if you want you could do something like this


function getMonth($monthName)
{
// CONVERT TO UPPERCASE
$monthName= strtoupper($monthName);

// TRIM OF ANY WHITESPACE
$monthName= trim($monthName);

switch ($monthName)
{
case 'JANUARY':
$monthNumber = '01';
break;
case 'FEBRUARY':
$monthNumber = '02';
break;
case 'MARCH':
$monthNumber = '03';
break;
case 'APRIL':
$monthNumber = '04';
break;
case 'MAY':
$monthNumber = '05';
break;
case 'JUNE':
$monthNumber = '06';
break;
case 'JULY':
$monthNumber = '07';
break;
case 'AUGUST':
$monthNumber = '08';
break;
case 'SEPTEMBER':
$monthNumber = '09';
break;
case 'OCTOBER':
$monthNumber = '10';
break;
case 'NOVEMBER':
$monthNumber = '11';
break;
case 'DECEMBER':
$monthNumber = '12';
break;

return $monthNumber;
}

$bDate = getMonth($beginDate);
$eDate = getMonth($endDate);

echo "BDATE: " . $bDate . "<br />";
echo "EDATE: " . $eDate . "<br />";


I haven't tested this but should work