How to insert dates selected from drop downs into mySQL table using php?

Question

When you have 5 dropdowns for date selection (Y, m,d, H,i) how do you insert the selected date and time into a single column of a mySQL table as a datetime using PHP5?

I know they need to be combined in a string such as:

$DateTime="$Year-$Month-$Day $Hour:$Minute:00";

and then maybe use strtotime:

$Date=date('Y-m-d H:i:s', strtotime($DateTime)) ;

but where do I declare the variables and how do I build it into a query such as:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "details")) {

$insertSQL = sprintf("INSERT INTO sessions (Location1, Location2, Date)
 VALUES (%s, %s, $s)",

                       GetSQLValueString($_POST['Location1'], "text"),
                       GetSQLValueString($_POST['Location2'], "text"),
                       GetSQLValueString($_POST[' ???? '], "date"));

Answer 1

You might also look into the mktime() function: http://ca.php.net/mktime

You would then just need to declare your date variable like:

$date = mktime($_POST['H'],$_POST['i'],0,$_POST['m'],$_POST['d'],$_POST['Y']);

$location1 = mysql_real_escape_string( $_POST['Location1'];
$location2 = mysql_real_escape_string( $_POST['Location2'];

$insertSQL = "INSERT INTO sessions (Location1, Location2, Date) VALUES ('$location1', '$location2', $date)";

mysql_query( $insertSQL );

EDIT:

Or, using the sprintf format you're using:

$date = mktime($_POST['H'],$_POST['i'],0,$_POST['m'],$_POST['d'],$_POST['Y']);  

$insertSQL = sprintf("INSERT INTO sessions (Location1, Location2, Date)
     VALUES (%s, %s, $s)",

                           GetSQLValueString($_POST['Location1'], "text"),
                           GetSQLValueString($_POST['Location2'], "text"),
                           GetSQLValueString($date, "date"));

I have never used the GetSQLValueString function before though, so I can only assume that's the correct way to use it.

Answer 2

Create a utility function that converts between the 'display mode' and MySQL mode, ti will come in handy in several other projects in the future.

Date - Return a string, formatted according to the given format string

MkTime - Get a Unix timestamp for a date

Strtotime - Parse English textual datetime description into a Unix timestamp

Answer 3

MySQL provides a function to make this easy for you. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function%5Ffrom-unixtime">FROM_UNIXTIME(). I would use mktime() to get your date to a timestamp, as it's overhead is much less than strtodate, since you know the date-parts there is no need to parse it as a string.

The following is the most straight-forward approach I can think of:

$date = mktime($_POST['H'], $_POST['i'], 0, $_POST['m'], $_POST['d'], $_POST['Y']);

$location1 = mysql_real_escape_string( $_POST['Location1'];
$location2 = mysql_real_escape_string( $_POST['Location2'];

$insertSQL = "INSERT INTO sessions (Location1, Location2, Date) VALUES ('$location1', '$location2', FROM_UNIXTIME($date))";

I'm assuming that GetSQLValueString is the Dreamweaver custom function (I've only heard of it). I'm sure you can adopt this into your implementation, though.

Answer 4

Thanks to all who tried to help - the answer was a lot less complicated (blonde moment I guess! lol) Just needed to declare the post variables as:

$_POST['startDate'] ="$Year-$Month-$Day $Hour:$Minute:00";

No need for time conversions as it's already in Mysql format (YEY!)