SQL插入日期始终为NULL

I'm trying to use an upload page to insert into my database with the following code:

if($file!=="")
    {

        echo "<br/>".$file;
        $handle = fopen($file, "r");
        $row = 0;

        $delete_records = mysql_query("DELETE FROM affiliationagreements");
        $delete_records = mysql_query("DELETE FROM college");
        $delete_records = mysql_query("DELETE FROM program");
        $delete_records = mysql_query("DELETE FROM facility");
        $delete_records = mysql_query("DELETE FROM submitor");
        $delete_records = mysql_query("DELETE FROM location");
        //will loop each record in the CSV file
        while(($fileop = fgetcsv($handle,1000,",")) !== false )
        {
            //columns names of the CSV file are not needed
            if($row==0)
            {
                $row++;
            }
            else
                {
                    //accept apostrophes in the strings
                    $fileop = array_map("mysql_real_escape_string",$fileop);

                    $sql = mysql_query("INSERT INTO affiliationagreements(id, AANumber, Facility, Submitor, Program, Location, College, SubmissionDate, Action, EffectiveDate, Status, ExpirationDate)
                                    VALUES('',
                                    '$fileop[0]', 
                                    '$fileop[1]', 
                                    '$fileop[2]', 
                                    '$fileop[3]', 
                                    '$fileop[4]', 
                                    '$fileop[5]', 
                                    '$fileop[11]', 
                                    '$fileop[23]', 
                                    '$fileop[24]', 
                                    '$fileop[25]', 
                                    '$fileop[26]') 
                                ")or die(mysql_error());

To just give a sample, and when I upload my CSV file to add the values, I print them out in the console and see that the values are being read correctly and they are. But, once my php script ends and I return to the main page, my dates are all null. None of them are the values what are reflected in the csv file. Here is the schema for my database:

CREATE TABLE `affiliationagreements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AANumber` varchar(20) DEFAULT NULL,
  `Facility` varchar(150) DEFAULT NULL,
  `Submitor` varchar(50) DEFAULT NULL,
  `Program` varchar(60) DEFAULT NULL,
  `Location` varchar(50) DEFAULT NULL,
  `College` varchar(50) DEFAULT NULL,
  `SubmissionDate` date DEFAULT NULL,
  `Action` varchar(50) DEFAULT NULL,
  `EffectiveDate` date DEFAULT NULL,
  `Status` varchar(50) DEFAULT NULL,
  `ExpirationDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If I change SubmissionDate, EffectiveDate, and ExpirationDate to a varchar, they insert correctly but I can't use varchar because I am comparing date values. And Advice?

***Update. In the CSV file, the format is MM/DD/YYYY. I didn't think this would be a problem. Would it be better to change this? And I'm deleting records because my boss wanted the DB cleared before a file was reuploaded since the uploaded file was an update of the previously uploaded one. ****

dtcpvz8162
dtcpvz8162 日期在上面评论。我试图回应数组,它的日期是正确的,但在插入后更改为00-00-0000或其他任何内容
接近 6 年之前 回复
dongzhan2461
dongzhan2461 这些日期字符串的格式是什么?
接近 6 年之前 回复
dtwvr26066
dtwvr26066 经典说明:您使用的是已弃用且过时的mysql连接器。请移植到较新的mysqli连接器或使用PDO既更安全又提供更好的功能。
接近 6 年之前 回复
dqqvravff05370501
dqqvravff05370501 您必须发布一些有效负载数据。什么时候无法猜出你尝试插入的实际值是什么。
接近 6 年之前 回复
duan39779
duan39779 你试图回应你的查询吗?
接近 6 年之前 回复
duanpang2751
duanpang2751 此外,由于性能,建议不要使用NULL。stackoverflow.com/questions/1017239/...
接近 6 年之前 回复
douyan9417
douyan9417 这是出于目的,因为它是一个测试脚本。
接近 6 年之前 回复
dongpu1315
dongpu1315 旁注:你确实意识到你经常用$delete_records=mysql_queryplus覆盖你的查询,你没有WHERE子句,那么如何知道删除什么?
接近 6 年之前 回复

2个回答



检查您的日期格式,因为MySql需要采用YYYY-MM-DD格式</ p>

  INSERT INTO表SET date ='2014-05-13'
</ code> </ pre>

如果你有不同的格式,日期将存储'0000-00-00 '相反。 因此,在运行查询之前,请通过回显查询字符串来仔细检查插入。</ p>
</ div>

展开原文

原文

Check your date format, as MySql it needs to be in YYYY-MM-DD format

INSERT INTO table SET date = '2014-05-13'

If you have different format, the date will store '0000-00-00' instead. So double check your insertion by echo your query string before running the query.

dongou0524
dongou0524 是的就是这样。 愚蠢的数据库只是荒谬。 再次感谢
接近 6 年之前 回复
drbd65446
drbd65446 接得好。 如果这是问题,我不会感到惊讶。
接近 6 年之前 回复



mysql date </ code>字段只接受以下格式的日期: 0000-00-00 </ code > </ p>

您可以在插入查询中使用 DATE_FORMAT()</ code>但我无法提供exaples,因为您没有发布实际日期的格式。< / p>

http://www.w3schools.com/sql/func_date_format .asp </ p>
</ div>

展开原文

原文

mysql date field only accepts dates in this format: 0000-00-00

You can use DATE_FORMAT() in your insert query but I can't give exaples becuase you didn't post what format your actual date is.

http://www.w3schools.com/sql/func_date_format.asp

dtl19910708
dtl19910708 更新,查看原帖...
接近 6 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐