PHP read and insert tab delimited file into MySQL db

What I'm trying to do:


I'm uploading a tab delimited text file and then reading it via PHP.
Then I'm trying to turn the rows of text into an array to insert into a MySql database.

I feel very close to getting this, but I've run out of Mountain Dew and need some new eyes.

My problem:

This is the error I receive:
0: INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the first question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the first que' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the second question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the second qu' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the third question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the third que' at line 1
 INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the fourth question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the fourth qu' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1

Open in new window





The file looks like:

33	this is the first question	4	1
33	this is the second question	4	1
33	this is the third question	4	1
33	this is the fourth question	4	1

Open in new window



My read the file, convert to array and insert code looks like this:

$readfile=file($uploadPath.$name);
	for($k=0; $k<=count($readfile); $k++)
    {
      $fields=explode("\r",$readfile[$k]);
	  for($i=0; $i<=count($fields); $i++) 
	  {
	  $values=explode("\t",$fields[$i]);
		$columns = "'examid', 'question', 'type', 'flag_active'";
      $query=("INSERT into questions ($columns) VALUES           ('$values[0]','$values[1]','$values[2]','$values[3]')");
		echo $query;
        mysql_query($query) ;
		echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
	  }
	}

Open in new window

LVL 2
rrattieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
Does the file have headers?
0
rrattieAuthor Commented:
no, no column headers just the rows of data.
0
GaryCommented:
Your php is a bit messy, this is what I use

$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
    $filedata[] = explode("\t",$line);
}

Open in new window


That should give you a correct array.
You could then either add the sql into the loop or loop the array again.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Chris StanyonWebDevCommented:
Remove the quotes from around your field names:

$columns = "examid, question, type, flag_active";

Open in new window

0
Ray PaseurCommented:
You might want to check out fgetcsv()
0
rrattieAuthor Commented:
@ChrisStanyon

That got me up to being able to do my insert and actually inserting the data into the table. However, I ended up with 3 extra rows for some reason.

@GaryC123

Could you expand on what you meant?  I gave your code a shot and wasn't able to get it to work properlly.
0
Chris StanyonWebDevCommented:
Maybe you have blank lines in the file!!
0
GaryCommented:
Add var_dump after the code above

$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
    $filedata[] = explode("\t",$line);
} 
var_dump($filedata);

Open in new window

And let me know what you get.
Note you have to change import.txt to your file name
0
rrattieAuthor Commented:
@ChrisStanyon

I cannot see any blank lines, that was my first assumption too. But if I open the file in a text editor that shows the line break and tab characters there aren't any extra.

@GaryC123

Here is the var_dump:
0: array(1) { [0]=> array(13) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" 
[2]=> string(1) "4" [3]=> string(4) "1 33" [4]=> string(27) "this is the second question" [5]=>
string(1) "4" [6]=> string(4) "1 33" [7]=> string(26) "this is the third question" [8]=> string(1) 
"4" [9]=> string(4) "1 33" [10]=> string(27) "this is the fourth question" [11]=> string(1) "4" 
[12]=> string(1) "1" } }

Open in new window

0
GaryCommented:
$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
	$filedata[] = explode("\t",$line);
} 

$columns = "examid,question,type, flag_active";
foreach ($filedata as $line) {
	$query=("INSERT into questions ($columns) VALUES ('$line[0]','$line[1]','$line[2]','$line[3]')");
	echo $query . "\n";
	mysql_query($query) ;
	echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
}

Open in new window

0
rrattieAuthor Commented:
@GaryC123

This: (removed the single quotes around the column names)
$input=file($uploadPath.$name,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
		  
		  $filedata= array();
		  foreach ($input as $line) {
			  $filedata[] = explode("\t",$line);
		  } 
		  
		  $columns = "examid, question, type, flag_active";
		  
		  foreach ($filedata as $line) {
		  $query=("INSERT into questions ($columns) VALUES ('$line[0]','$line[1]','$line[2]','$line[3]')");
		  echo $query . "\n";
		  mysql_query($query) ;
		  echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
		  }

Open in new window


produced this:

0: INSERT into questions (examid, question, type, flag_active) VALUES ('33','this is the first question','4','1 33') 0:

Open in new window


And it did insert 1 row, but only 1.
0
Chris StanyonWebDevCommented:
It looks like your data is being read into the array incorrectly. You end up with one big array, instead of a separate array for each line. You file's end of line markers are not being read correctly. Forget about the database bits for a minute (you need to move away from the mysql library anyway) and just concentrate on reading your file.

$input = file('data.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$filedata=array();

foreach ($input as $line) {
   $filedata = explode("\t",$line);
   var_dump($filedata);
}

Open in new window

Once that's working, we can move onto the database bits (PDO or mySQLi)
0
GaryCommented:
There is something wrong with your source data, it is coming in as one line of data.
Can you attach it?
0
Chris StanyonWebDevCommented:
Note: If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem.
0
GaryCommented:
I must be typing slow.
0
Ray PaseurCommented:
It looks like you may be overthinking this problem.

<?php // RAY_fgetcsv.php
error_reporting(E_ALL);

// SHOW HOW TO READ A CSV FILE 

// NAME OF THE CSV FILE
$csv = 'RAY_sample_csv.csv';

// OPEN THE FILE FOR READING
$fpo = fopen($csv, 'r');
if (!$fpo) die("CANNOT OPEN $csv");

// READ THE ROWS
while (!feof($fpo))
{
    $arr = fgetcsv($fpo);
    var_dump($arr);
    
    // CONSTRUCT AND RUN YOUR INSERT QUERY HERE
}

Open in new window

HTH, ~Ray
0
Chris StanyonWebDevCommented:
@ray - the same problem will exist as using file()...The line endings aren't being recorgnized!
0
rrattieAuthor Commented:
@GaryC123

That code gave me:
 
0: array(1) { [0]=> array(13) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" 
[2]=> string(1) "4" [3]=> string(4) "1 33" [4]=> string(27) "this is the second question" [5]=>
string(1) "4" [6]=> string(4) "1 33" [7]=> string(26) "this is the third question" [8]=> string(1) 
"4" [9]=> string(4) "1 33" [10]=> string(27) "this is the fourth question" [11]=> string(1) "4" 
[12]=> string(1) "1" } }

Open in new window

Which is what I got last time I ran it (like above).

I changed it to:
$input=file($uploadPath.$name,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
		  
			$filedata=array();
			
			foreach ($input as $line) {
			   $filedata = explode("\r",$line);
			   foreach ($filedata as $thing) {
				   $value = explode("\t",$thing);
				   
			   var_dump($value);
				}
			}
		  

Open in new window


and got the following:
0: array(4) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(27) "this is the second question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(26) "this is the third question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(27) "this is the fourth question" [2]=> string(1) "4" [3]=> string(1) "1" }

Open in new window

0
GaryCommented:
Can you attach a sample of the source data.
0
rrattieAuthor Commented:
I've also enabled the auto_detect_line_endings in the php.ini file.  Yes, the attached file was created on a Mac.
testfile.txt
0
GaryCommented:
Put this before your code
ini_set('auto_detect_line_endings',true);

Open in new window

0
Chris StanyonWebDevCommented:
Your file has a CR (Carriage Return) line ending because it was created on a Mac. Normal files have a CRLF (Carriage Return Line Feed).

If you enabled the auto_detect_line_endings in your php.ini, you may have to restart your apache server.
0
Chris StanyonWebDevCommented:
Here's a run down of your code. I've swapped out the mysql library for the PDO one:

<?php
//sort out line endings
ini_set('auto_detect_line_endings',true);

//connect to your database
$dbUser = "username";
$dbPass = "password";
$conn = new PDO('mysql:host=localhost;dbname=yourDB', $dbUser, $dbPass);

//read the file into an array
$input = file('yourFile.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

//prepare the SQL statement
$query = $conn->prepare("INSERT INTO questions (examid, question, type, flag_active) VALUES (?,?,?,?)");

//loop through the array and insert the database records
$filedata=array();
foreach ($input as $line) {
    $filedata = explode("\t",$line);
    $query->execute($filedata);
}
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
This worked for me.
http://www.laprbass.com/RAY_temp_rrattie.php

<?php // RAY_temp_rrattie.php
error_reporting(E_ALL);
echo '<pre>';

// SHOW HOW TO READ A CSV FILE
// http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28236830.html#a39484705

// NAME OF THE CSV FILE
$csv = 'http://filedb.experts-exchange.com/incoming/2013/09_w37/676827/testfile.txt';

// COPY AND REPAIR THE FILE
$txt = file_get_contents($csv);
$arr = explode("\r", $txt);
$txt = implode(PHP_EOL, $arr);
file_put_contents('RAY_temp_rrattie.csv', $txt);

// OPEN THE REPAIRED CSV
$fpo = fopen('RAY_temp_rrattie.csv', 'r');
if (!$fpo) die("CANNOT OPEN 'RAY_temp_rrattie.csv'");

// READ THE ROWS
while (!feof($fpo))
{
    $arr = fgetcsv($fpo,0,"\t");
    var_dump($arr);
    
    // CONSTRUCT THE QUERY HERE
}

Open in new window

0
rrattieAuthor Commented:
@ChrisStanyon


The code below worked like a charm. Hopefully the client doesn't mine me using PDO, the current staff code it all in regular mysql.


Thanks Chris for your help!, Gary thanks for your assistance as well.

<?php
//sort out line endings
ini_set('auto_detect_line_endings',true);

//connect to your database
$dbUser = "username";
$dbPass = "password";
$conn = new PDO('mysql:host=localhost;dbname=yourDB', $dbUser, $dbPass);

//read the file into an array
$input = file('yourFile.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

//prepare the SQL statement
$query = $conn->prepare("INSERT INTO questions (examid, question, type, flag_active) VALUES (?,?,?,?)");

//loop through the array and insert the database records
$filedata=array();
foreach ($input as $line) {
    $filedata = explode("\t",$line);
    $query->execute($filedata);
}
?>

Open in new window

0
Chris StanyonWebDevCommented:
They certainly shouldn't mind you switching to PDO. All those that use the old mysql library will also have to switch at some point (to either PDO or mysqli), so they should be following your lead :)

If they do mind, point them to any of the PHP mysql pages - they'll see a big red warning !!

Thanks for the points and good luck with it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.