如何使用php将从下拉列表中选择的日期插入到mySQL表中？

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?

当您有5个日期选择下拉列表(Y,m,d,H,i)时,如何使用PHP5将选定的日期和时间作为日期时间插入mySQL表的单个列?

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

我知道他们需要组合成一个字符串,例如:

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

and then maybe use strtotime:

然后可能使用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"));

4 个解决方案

#1

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

您还可以查看mktime()函数: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:

或者,使用您正在使用的sprintf格式:

$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.

我之前从未使用过GetSQLValueString函数,所以我只能假设这是使用它的正确方法。

#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.

创建一个在“显示模式”和MySQL模式之间转换的实用程序函数,ti将在未来的其他几个项目中派上用场。

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

Date - 返回一个字符串,根据给定的格式字符串格式化

MkTime - Get a Unix timestamp for a date

MkTime - 获取日期的Unix时间戳

Strtotime - Parse English textual datetime description into a Unix timestamp

Strtotime - 将英文文本日期时间描述解析为Unix时间戳

#3

MySQL provides a function to make this easy for you. 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.

MySQL提供了一个让您轻松实现此功能的功能。 FROM_UNIXTIME()。我会使用mktime()来获取时间戳的日期,因为它的开销远小于strtodate,因为你知道日期 - 部分不需要将它解析为字符串。

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.

我假设GetSQLValueString是Dreamweaver自定义函数(我只听说过它)。不过,我相信你可以将它用于你的实现。

#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变量声明为:

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

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

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

不需要时间转换,因为它已经是Mysql格式(YEY!)

#1