insert file name into mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

insert file name into mysql

Post by peterhall »

hi guys. i want to know if ispossible to import a csv file into mysql table and insert also the file name in a column of the same table.

i'm using this code to import:

Code: Select all

<?php

$databasehost = "localhost";  
$databasename = "d_base";  
$databasetable = "db_table";  
$databaseusername ="root";  
$databasepassword = "";  
$fieldseparator = ",";  
$lineseparator = "\n";

header('Content-Type: text/plain');
define('CSV_SEARCH_PATTERN', 'D:/dir/dir/*_file.csv'); // this will be the file name to insert into mysql table
define('CSV_LINE_LENGTH', 10000);
define('CSV_DELIMITER', ';');
define('CSV_ENCLOSURE', '"');
//define('CSV_ESCAPE', '\\'); // PHP 5.3.0

$files = glob(CSV_SEARCH_PATTERN);
print_r($files);


foreach ($files as $csvfile) {
    if ($fh = fopen($csvfile, 'r')) {
        while ($row = fgetcsv($fh, CSV_LINE_LENGTH, CSV_DELIMITER, CSV_ENCLOSURE/*, CSV_ESCAPE*/)) {
		 
		}		
       
    }

$addauto = 0;  
$save = 0;  
$outputfile = "output.sql";  
if(!file_exists($csvfile)) {            
echo "File not found. Make sure you specified the correct path.\n";       
exit;   
}   
$file = fopen($csvfile,"r");   
if(!$file) {       
echo "Error opening data file.\n";       
exit;   
}   
$size = filesize($csvfile);   
if(!$size) {       
echo "File is empty.\n";       
exit;   
}   
$csvcontent = fread($file,$size);   
fclose($file);    
$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());   
@mysql_select_db($databasename) or die(mysql_error());   
$lines = 0;   
$queries = "";   
$linearray = array();     
foreach(split($lineseparator,$csvcontent) as $line) {       
$lines++;       
$line = trim($line," \t");       
$line = str_replace("\r","",$line);       
$line = str_replace("'","\'",$line);       
$linearray = explode($fieldseparator,$line);       
$linemysql = implode("','",$linearray);       
if($addauto)           
$query = "insert into $databasetable values('','$linemysql');";       
else          
$query = "insert into $databasetable values('$linemysql');";       
$queries .= $query . "\n";       
@mysql_query($query);   
}   
@mysql_close($con);   
if($save) {               
if(!is_writable($outputfile)) {           
echo "File is not writable, check permissions.\n";       
}       
else {           
$file2 = fopen($outputfile,"w");           
if(!$file2) {               
echo "Error writing to the output file.\n";           
}           
else {               
fwrite($file2,$queries);               
fclose($file2);           
}       
}   
}

echo "Imported a total of $lines records in this csv file.\n"; 
}

?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: insert file name into mysql

Post by John Cartwright »

Much simpler to use mysql's built functions. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

Re: insert file name into mysql

Post by peterhall »

I agree and I've got it, but I still with the same problem.

Imagine that I'm using the LOAD DATA INFILE to import a txt file into mysql.

what I want, besides the lines of the file, is insert, also, the name of the file, just like an auto increment, but in this case, for each file that I import, i want to add the name in the first column, like a ID, wich means,if I have 100 lines in the file A, the ID will be "file A" for all the lines in that file, 100 lines in file b, the id will be "file b" for all the lines in that file and so on...


or, if I can add an a multiple key to all lines that I import for each file, file a, 100 lines, key 1*100, file b, 150 lines, key 2*150, and so on...

all this, always in the first column, behind the data line that i import from the files!!
Post Reply