duandou9931 2014-10-13 17:09
浏览 498
已采纳

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

  • 写回答

2条回答

  • dongle2627 2014-10-13 17:17
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题