[SOLVED] Problem with LOAD DATA LOCAL INFILE: All records are not being added

[flattened]

:'( I am having a hard time trying to understand why this is messing up.

 

What is is supposed to do is:

• Create the table
  
• Populate it by the file
  
• Then show the titles of all the records
  

(There are 97 records by the way)

 

include 'config.php';
include 'opendb.php';
// Create a MySQL table in the selected database
mysql_query("CREATE TABLE tutorials(
accessKey INT(10) NOT NULL, 
PRIMARY KEY(AccessKey),
title VARCHAR(100), 
id INT(5), 
previewImage VARCHAR(100),
intro VARCHAR(500),
full_Tutorial VARCHAR(52),
keywords VARCHAR(100),
programsUsed VARCHAR(50))")
or die(mysql_error());  
echo "Table Created!";

$sql="LOAD DATA LOCAL INFILE 'tutorials.txt' INTO TABLE tutorials FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";
	$query = mysql_query($sql) or die(mysql_error());

echo "Data Added!";
$result = mysql_query("SELECT * FROM tutorials ORDER BY full_Tutorial ASC") 
or die(mysql_error()); 
echo "<ol>";
while($row = mysql_fetch_array( $result )) {
	echo "<li></li>";
}

 

 

So, what happens is it does everything it's supposed to BUT it doesn't load all the records (loaded up to #46) I have tried reducing the # of records (tried adding just 8 ) but it still only showed 5.

 

I don't know what to do.

 

PS. Is it wrong for me to hope this is a real problem as opposed to just a missing character somewhere

[JustLikeIcarus]

I would guess its a possible formatting issue in your text file or possibly a duplicate pkey.  Cant really tell without seeing the file your importing.  Can you attach that?

[PFMaBiSmAd]

You need to identify what unique condition is present in the row(s) that are not being inserted.

[flattened]

Oh, I'm sorry!

 

The file is http://snookumz.com/tutorials.txt, that's the original file with all the records

I entered the records on Access 2007 and used the export feature. http://snookumz.com/Tutorials.accdb if that matters any.

 

The primary key is the first row with the 10 number (AccessKey).

The fields are (in order): AccessKey, Title, ID, ProgramsUsed, Intro, Full_tuto, Keywords, and Preview Image

 

When I run the query, I only get every other row except for the first (i.e. 1,2,4,6,8-96 in that manner [i deleted #97]) I haven't noticed any discrepancies in the records added and the ones not, other than the above mentioned.

 

Just a thought, would this have anything to do with the id number? The only purpose of that is for the GET variable that will be used to call each tutorial, but it's not necessary elsewise. This is so frustrating because eveything else works, including the php aspect. 

 

Thank you very much for the responses 

[PFMaBiSmAd]

The problem is the line ending. The file has \r\n, so LINES TERMINATED BY should be '\r\n'

[flattened]

:happy-04: YOU HAVE JUST MADE MY DAY!

 

It works now! I should have known about the return 

 

Mind if I ask, why in the world this problem occured with my original code?

[PFMaBiSmAd]

this problem occurred with my original code?

Huh?

 

Just opening and saving the file using a text/programming editor could have caused the line-endings to be changed from what they were originally.