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