将文件夹中的文件名列表插入mysql

Question

I retrieve a list of filenames from a folder and need to insert it into a mysql database using php.

I insert into the database and it starts building and won't stop. Its only about 20 items in the folder but it keeps looping into database:

<?php
$directory = "/xml/";
$results_array = array();

if(is_dir($directory)) {
    if($handle = opendir($directory)){
        while(($file = readdir($handle)) !== false) {
           $results_array[] = $file;
        }
        closedir($handle);
    }
}
foreach($results_array as $value) {
   $e_get = substr($value, 0, 16);
   $edate = substr($e_get, -6);
   $checkDB = $pdo->query("select `dateString` from `report`");
   $checkDB->fetchAll();
   foreach($checkDB as $checkItems){
       if($checkItems->dateString != $edate) {
          $pdo->query("insert into `report`(`dateString`) values
                       ('$edate');
       }
   }
}

Now I am trying a different way but I only get one item:

<?php
$directory = "/xml/";
$results_array = array();

if(is_dir($directory)) {
    if($handle = opendir($directory)){
        while(($file = readdir($handle)) !== false) {
           $results_array[] = $file;
        }
        closedir($handle);
    }
}
$edate = array();
foreach($results_array as $value) {
   $e_get = substr($value, 0, 16);
   $edate[] = substr($e_get, -6);
}
foreach($edate as $date) {
  $checkDB = $pdo->query("select `dateString` from `report`");
  foreach($checkDB as $checkItems) {
     if($checkItems->dateString != $date) {
          $pdo->query("insert into `report`(`dateString`) values
                       ('$edate');
     }
  }
}

Please help... Thanks!

Answer 1

I'm assuming that the first part of the code is correctly populating $edate. After that you're reading the database unnecessarily, and you've used $edate in your second query, instead of $date.

Because the database should be checking for uniqueness you should only need this:

foreach($edate as $date) {
    $pdo->query("insert ignore into `report`(`dateString`) values ('$date')";
}

(INSERT IGNORE inserts a new entry, or ignores it if it detects an invalid request, such as a duplicate in a UNIQUE column.)

However, this won't pick up any syntax or other errors that may occur, so you need to add some error checking. My suggestion:

// Just after you open the PDO connection, set PDO to
// throw an exception in the event of an error
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// Then...
try {
    foreach($edate as $date) {
        $pdo->query("insert ignore into `report`(`dateString`) values ('$date')";
    }
} catch(PDOException $e) {
    echo 'Database error '.$e->getCode().': '.$e->getMessage();
    exit(1);
}

You could handle the exception differently, or not bother to catch it here and let the default exception handler catch it, if you have one.

Note: you could do this in a single INSERT by imploding the $edate array.

// No loop required
$query = "insert ignore into `report`(`dateString`) values ('" . implode("'),('",$edate) . "')";
$pdo->query($query);

Security: I'm assuming that you have some control over the file names in the directory and that they'll be safe to use this way. If not, you have a susceptibility to SQL Injection in this code. You need either to use PDO::quote() on the values or better, switch to prepared statements.