Insert content of multiple text files into MySQL

[yasso]

Hello!

 

I am trying to read the content of an array of text files and then insert the contents of each of these files into MySQL. I got stuck at the data insert portion and was hoping to get a little help on how to get around that.

 

Here is what I have:

 

I start off with reading a comma delimited text file (Master file) that contains a list of pipe delimited text files: file1.DEL,file2.DEL,....

Then I loop through each file and attempt to insert its content to a corresponding database table that has a matching name as the pipe delimited file (without the .DEL of course).

 

Here is a sample of a file's content

 

NotReady|4

Ready|5

InProgress|6

Done|9

 

Each file could have one, some, or all of these values (some files may be blank too). The status types (NotReady,Ready...) are also database field names in each of the tables.

 

Here is the code that I have so far:

 

$Masterfile = "Master.DEL";
$fm = fopen($Masterfile , "r");
$data = fread($fm, filesize($Masterfile ));
fclose($fm);

$output = explode(",", $data);


for($i=0;$i<count($output);$i++){

         $file = $output[$i];

         // get the database table name from the file name
       $split = explode(".",$file);
$table = $split[0];


$filetable = "$file";
$ft = fopen($filetable , "r");
$content = fread($ft, filesize($filetable));
fclose($ft);


$out = explode("\n",  $content);


// now read through each file
foreach($out as $var) {

	$tmp = explode("|", $var);

	for($d=0;$d<count($tmp);$d++){
		//echo $tmp[$d] . " ";

                       // and here is the missing working piece. 
		$sql = "INSERT INTO $table ($tmp[$d]) VALUES ('$tmp[$d]')";
		$res = @mysql_query($sql,$conn) or die(mysql_error());

	}
}

}

 

Ideally, what I am trying to accomplish for the data insert piece is for each file to get the list of available status types all lined up in one comma seperated string (array maybe?) as the database columns and do the same for the numbers, to capture the database values.

 

Any help is very much appreciated as I already spent quite a bit of time trying to figure this one out without much success so far.

 

 

Thank you!

yasso

[andrewgauger]

$sql = "INSERT INTO $table ($tmp[$d]) VALUES ('$tmp[$d]')";

 

On this line of code, you are attempting to insert the column name as the value.

 

So on the first time through the nested loop you will insert Notready = Notready

And the second time through you will insert 4=4.

 

So what I assume you really want (according to the information provided) is to remove the nested loop and after you explode the pipe:

 

$sql = "INSERT INTO $table ($tmp[0]) VALUES ('$tmp[1]')";

[yasso]

Thanks for the reply. Unfortunately this does not do it, was also one of my previous attempts. doing what you recommended gives me the  following result in MySQL:

 

NotReadyReadyInProgressDone

4

0

0

0

0

5

0

0

0

0

6

0

0

0

0

9

 

Provided my first file has the content:

 

NotReady|4

Ready|5

InProgress|6

Done|9

 

Also, it only goes through the first pipe delimited file in the Master file, all others remain untouched.

 

The result that I'd love to see at the end would be this (for the above example):

 

NotReadyReadyInProgressDone

4

5

6

9

 

And then of course that all pipe delimited files are considered too.

 

Thanks for your help!

[andrewgauger]

Here is my recommendation.  You start with a variable that will define what columns you want to output and a variable that tracks the values.

 

So starting with the first line herein, you are going to do this:

 

$out = explode("\n",  $content);
$columns="";
$values="";


// now read through each file
foreach($out as $var) {
$tmp = explode("|", $var);
$columns .= $tmp[0] . ", ";
$values .= $tmp[1] . ", ";

}
$columns=rtrim($columns, ", ");
$values=rtrim($values, ", ");
$sql = "INSERT INTO $table ($columns) VALUES ($values)";
$res = @mysql_query($sql,$conn) or die(mysql_error());

[yasso]

One word is all  I can say: Beautiful! Thank you. Loop through each file and grab columns and values only first is what I missed to figure out. It works perfectly now.

 

Thanks again and cheers