SQL Insert Date Always comes out NULL

Question

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

Sidenote: You do realize that you're constantly overwriting your query with `$delete_records = mysql_query` plus, you don't have a `WHERE` clause, so how is it to know what to delete? — Funk Forty Niner, Oct 13 '14 at 17:11
Also, NULL is not recommended due to performance. http://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search — Martijn, Oct 13 '14 at 17:12
You will have to post some of your payload data. When cannot guess what the actual values are you try to insert. — arkascha, Oct 13 '14 at 17:13
And the classical note: you are using a deprecated and outdated mysql connector. Please port to either the newer `mysqli` connector or use `PDO` both being more secure and offering better features. — arkascha, Oct 13 '14 at 17:14
Dates are in comment above. I tried to echo the array and it has the date correct but changes to 00-00-0000 or whatever after I insert — user3761203, Oct 13 '14 at 20:29

score 1 · Accepted Answer · answered Oct 13 '14 at 17:17

1

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.

answered Oct 13 '14 at 17:17

Li Kia Chiu

213
1
6

Good catch. I wouldn't be surprised if that's the problem. – AdamMc331 Oct 13 '14 at 17:23
Yeah that was it. Stupid database was just being ridiculous. Thanks again – user3761203 Oct 13 '14 at 21:20

score 0 · Answer 2 · answered Oct 13 '14 at 17:18

0

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

answered Oct 13 '14 at 17:18

I wrestled a bear once.

22,983
19
69
116

SQL Insert Date Always comes out NULL

2 Answers2