Problem with LOAD DATA INFILE query

[RyanMinor]

I am having a strange issue with running a LOAD DATA INFILE query from a PHP script. Here is my PHP code:

$query = $db->prepare("LOAD DATA INFILE ? 
    INTO TABLE csv_data 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\r\n' 
    (game_week, home_team, home_score, visitor_team, visitor_score)");
if (!$query->execute(array($location))) {
    $errors[] = 'CSV upload query failed.';
} else {

 

The first five lines of the csv file are as follows:

1,Abington,24,Fels,8
1,Abington Heights,28,Pittston Area,0
1,Academy Park,29,Marple Newton,20
1,Aliquippa,36,Ambridge,0
1,Allegany MD,56,Southern Garrett MD,6

 

When I run this query everything gets uploaded perfect except that the first value in the database table for game_week is 0 instead of 1. So the first row looks like this:

game_week | home_team | home_score | visitor_team | visitor_score
0 | Abington | 24 | Fels | 8

 

Why is that first value 0 instead of 1. I really don't understand why this is happening. Any help is greatly appreciated.

 

Also, here is my table structure and I am using XAMPP on Windows 7 if that matters at all:

CREATE TABLE csv_data ( 
    game_id int(4) NOT NULL AUTO_INCREMENT, 
    game_date date NOT NULL, 
    game_week tinyint(2) NOT NULL, 
    home_team varchar(250) NOT NULL, 
    home_score tinyint(3) NOT NULL, 
    visitor_team varchar(250) NOT NULL, 
    visitor_score tinyint(3) NOT NULL, 
    PRIMARY KEY (game_id)
) 
ENGINE=MyISAM 
AUTO_INCREMENT=675 
DEFAULT CHARSET=latin1

[RyanMinor]

Anybody have any ideas?

[PFMaBiSmAd]

I suspect you have a non-printing character (null,tab,just a \r or just a \n) before the '1' and when that CSV field is treated as a number it results in a zero value.

[RyanMinor]

I added another line above the first one in the CSV and it worked fine. I hate to have to add a blank line thought everytime I do an upload or if someone else does an upload. Is there any way around this?

[darkfreaks]

have you tried using MYSQL COALESCE function it returns non null values and it will return null if it hits a NULL value.

 

http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/