CODEWITHSUNDEEP
phpmysql
user3201441
user3201441

Reputation: 167

PHP Getting files in a directory

First, this might be a stupid question for some but as I am a newbie to this, I am still learning while doing things. So here's my question :

I am trying to get all files in a directory so that I can insert its content to the database. I am currently using localhost. Using glob() function, I got what I need. But here's where I have a problem. Since glob() function returns an arraylist of filenames, how can I get the filename currently being read, and insert that also in the database table?

Here's my current code w/c I got from this site :

$dir = "MyFiles/";
$curFile = glob($dir."*.csv");


foreach ($curFile as $filename) {
   $handle = fopen($filename,"r");
   }
   //loop through the file
   do{
      if ($data[0]) {
         mysql_query (" INSERT INTO tblCoordinates (id,filename,lat,lon)
         VALUES
         ('',
          '.$filename.',
          '".addslashes($data[0])."',
          '".addslashes($data[1])."')
         ");

   } while ($data = fgetcsv ($handle,1000,",","'"));
}

Again, I also need the filenames to be inserted to the database, but with my current code, the filename is not inserted.

Upvotes: 3

Views: 111

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Just noticed: In your statement '.$filename.' is wrong. It should be '$filename' ...

You should assemble the sql command first and then execute it as a single statement like this:

$glob=glob($dir."*.csv");
foreach ($glob as $filename) {
  $handle = fopen($filename,"r");
  while ($da = fgetcsv ($handle,1000,",","'")) {
    if ($da[0]) $data[]="('','$filename','".JOIN("','",array_slice($da,0,2))."')";
  }
} 
$mysql="INSERT INTO tblCoordinates (id,filename,lat,lon)  VALUES\n".JOIN(",\n",$data);
// echo $mysql // --> check it first ...
mysql_query($mysql);

Running on my data in $dir='./' the echo gave me this

INSERT INTO tblCoordinates (id,filename,lat,lon)  VALUES 
('','./dat.csv','1','23'),
('','./dat.csv','2','27'),
('','./dat.csv','3','31'),
('','./dat.csv','4','35'),
('','./dat.csv','5','39')

What is your statement like and - most of all - your error message?

(And, of course: the mysql_query() function is deprecated, use mysqli_query() instead ...)

Upvotes: 1

PEM
PEM

Reputation: 1978

I do not think you should use this approach. You might (and probably will) kill your database every time you run the script (especially if you have many files in this directory and many line in each csv file). It would seem better to prepare a transaction, one insert with many values, then commit. That way, you will also be able to easily echo the query to see if everything is alright.

Note that you should check if filename is a file or a directory (I am not sure about . and .. but you might need to check that too)

Also, I would recommend doing something like 

$someVar = glob($dir."*.csv");
foreach($someVar as $filename) {
    // your code here
}

Upvotes: 2

Related Questions