PHP无法通过HTML表单更新MySQL表中的日期 [英] Php fails to update Date in MySQL table via HTML form
问题描述
我无法解决这个看似简单的问题。我有一个HTML表单发布到一个php文件，效果很好。但是，当我将日期值从HTML发布到php时，MySQL数据库会更新所有必填字段，但日期仍显示为0000-00-00。当我将php变量更改为CURDATE（）时，它可以正常工作，但是，我不需要当前日期，而是我从HTML文件中发布的日期。这是我的HTML：
I cannot solve this seemingly simply problem. I have an HTML form posting to a php file which works perfectly well. However, when I post a date value from HTML to php the MySQL database updates all the required fields except the date which still shows as 0000-00-00. When I change the php variable to CURDATE() it works OK however, I do not require the current date but the date I post from my HTML file. This is my HTML:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Credit Note Generator v.2</title>
<meta name="generator" content="">
</head>
<body>
<div id="space"><br></div>
<div id="container">
<div id="wb_Form1" style="position:absolute;left:210px;top:110px;width:530px;height:381px;z-index:4;">
<form name="cnote" method="post" action="cnote.php" id="cnote">
<input type="number" id="dupid" style="position:absolute;left:240px;top:31px;width:207px;height:20px;line-height:20px;z-index:0;" name="dupid" value="" required="required">
<input type="date" id="cndate" style="position:absolute;left:240px;top:81px;width:207px;height:20px;line-height:20px;z-index:1;" name="cndate" value="" required="required" >
<textarea name="cnreason" id="cnreason" style="position:absolute;left:240px;top:141px;width:202px;height:127px;z-index:2;" rows="6" cols="24" required="required"></textarea>
<input type="submit" id="Button1" name="" value="Generate" style="position:absolute;left:220px;top:310px;width:132px;height:47px;z-index:3;">
输入我从HTML传递到php的日期是：
The input date I am passing from this HTML to php is:
<input type="date" id="cndate" style="position:absolute;left:240px;top:81px;width:207px;height:20px;line-height:20px;z-index:1;" name="cndate" value="" required="required" >
我的php文件（我仅包括相关部分）内容如下：
My php file (I am only including the relevant part) reads as follows:
<?php
var_dump($_POST);
$con = mysql_connect("192.168.10.223","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("BMS", $con);
$pass = $_POST["dupid"];
$cnotes = $_POST["cnreason"];
$cndatepost = $_POST["cndate"];
$cndate2 = strtotime($cndatepost);
$cndate = date('Y-m-d',$cndate2);
//get highest
$sqlgethighest = mysql_query("SELECT MAX(CN_No) FROM Main_Data_Table");
$row = mysql_fetch_array($sqlgethighest);
$cnoteno=($row['MAX(CN_No)'])+1;
$sql="INSERT INTO Main_Data_Table (ID, Name, Surname, TitleID, NationalityID,
Age, AgentID, `Course Start`, `Course End`, CourseTypeID, `Rate per unit`,
`Arrival Date`, `Departure Date`, `Arrival Time`, `Departure Time`, `Arrival Flight`,
`Departure Flight`, AccommodationID, HostFamilyID, `Rate per night`, TransferId,
`Material Fee`, `Total Units Booked`, `Reg Fee`, BoardBasisID, `Ref Fee Acc`,
Notes, Notes2, DateofCanx, ReasonforCanx, HFXId, old_Invoice_number, LevelID,
Hide, Alloc_CodeAMID, Alloc_CodePMID, Lunch, Binder, `Audio CD`, `CD-ROM/Web`,
Tours, PromoMat, TrfFee, Settle_Bill, Commission, Pay_Local, Settle_Bill_rcpt,
Pay_on_AC, Pay_on_AC_amt, Pay_on_AC_rcpt, Deposit, SageExportNo, Invoice_number,
TrfRateCode, `Extra Commission`, F56, F57, HOT_CONF, Room_type_ID, Invoice_Date,
CN_Date, CN_No, Passport_No, DOB, CN_Presence, Corresp_CN)
SELECT NULL, Name, Surname, TitleID, NationalityID, Age, AgentID, `Course Start`,
`Course End`, CourseTypeID, `Rate per unit`, `Arrival Date`, `Departure Date`,
`Arrival Time`, `Departure Time`, `Arrival Flight`, `Departure Flight`,
AccommodationID, HostFamilyID, `Rate per night`, TransferId, `Material Fee`,
`Total Units Booked`, `Reg Fee`, BoardBasisID, `Ref Fee Acc`, CONCAT('Ref Invoice number: ', Invoice_number, ' ', '$cnotes'), Notes2,
DateofCanx, ReasonforCanx, HFXId, old_Invoice_number, LevelID, Hide,
Alloc_CodeAMID, Alloc_CodePMID, Lunch, Binder, `Audio CD`, `CD-ROM/Web`,
Tours, PromoMat, TrfFee, 0, Commission, 0, 0, 0, 0, 0, 0, 0, 0, TrfRateCode,
`Extra Commission`, F56, F57, HOT_CONF, Room_type_ID, NULL, $cndate, $cnoteno, NULL, NULL, 0, NULL
from Main_Data_Table WHERE ID='$pass'";
我的var_dump正确显示：string（10） 2015-02-26表示字符串$ cndate这是我要传递的值，但由于某种原因，它仍在作为0000-00-00更新。
My var_dump correctly displays: string(10) "2015-02-26" for the string $cndate which is the value I am trying to pass but for some reason it is still updating as 0000-00-00.
谢谢
推荐答案
在插入时将$ cndate放在单引号'$ cndate'中。
While inserting put $cndate in single quote '$cndate'.
还要检查cndate字段的数据类型
Also do check data type of cndate field
这篇关于PHP无法通过HTML表单更新MySQL表中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
