Check if the folder/file already exists in the database

Question

I have some files on my server that I want to put into a database. I'll add some more information such as comments about the files (the SQL query for the files will be easier to compare with the database after I have fixed this problem) to the database and also to save some bandwidth. The thing is that I want to see if the folders/files already exists in the database. If not, add them to the database.

Here's how my code looks like at the moment (sql() is a function I have created) (the code below only echos the names of the existed names in the database for development purposes):

$dir = new RecursiveDirectoryIterator($admin['data_folder_main'], FilesystemIterator::SKIP_DOTS);
$it = new RecursiveIteratorIterator($dir, RecursiveIteratorIterator::SELF_FIRST);
$it->setMaxDepth(1);

foreach($it AS $fileinfo) {

    # DATABASE (fetch)
    $folder = sql("SELECT *
                   FROM items_folders
                   WHERE name_folder = :folder
                  ", Array('folder' => $it->getSubPath()), 'fetch');


    echo $folder['name_folder'].'<br>';

}

And here's how the database looks like:

CREATE TABLE IF NOT EXISTS `items_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_folder` int(11) NOT NULL DEFAULT '0',
  `name_folder` text NOT NULL,
  `name_folder_sub` text NOT NULL,
  PRIMARY KEY (`id`)
)

The problem is that the code above prints the existed name from the database and then echos the name same amount of times as it is folders/files in the folder on the server. Here's how I want it: does the folder exists in the database? Yes or no.

Yes? Good! Ignore it (don't add it to the database). Moving on to the next folder/file (if there is any).

No? Add it to the database. Moving on to the next folder/file (if there is any).

How can I make this possible?

Answer 1

You simply need to test for whether there is a value and if not, add the item to the database

foreach($it AS $fileinfo) {

    # DATABASE (fetch)
    $folder = sql("SELECT *
                   FROM items_folders
                   WHERE name_folder = :folder
                  ", Array('folder' => $it->getSubPath()), 'fetch');


   //test to see if there is a value in the database
      if($folder['name_folder']==''){
          // if not, add it
          strSQL = "UPDATE items_folders SET name_folder='". $folder['name_folder'] . "' WHERE id= ". $folder['id'];
         //you don't tell us if you are using PDO or mysql_ so do your own execution
         execute;
         echo "item added";
      }
}

Not tested, but it gives you an idea of what to do.

Answer 2

If you use

" SELECT CAST(COUNT(*) AS BIT) AS existing FROM items_folders WHERE name_folder = :folder"

you should get an boolean return value (true or 1 if it exists).

"SELECT DISTINCT * FROM items_folders WHERE name_folder = :folder"

removes all double values and if you want to instert a folder if it not exists use:

"[Insert-Statement] WHERE NOT EXISTS (SELECT * FROM items_folders WHERE name_folder = :folder)"