Check if items from an array exists in table and if not insert them into new records?

Question

I have an array of image file names which i want to compare against a table in my DB, if the filename is not present then i want to insert it. I have started work on the insert statement, so i will come back to this later. What i am looking for is some help with the code which will check if it exists, then leads on to the insert statement? Below is the code used to build the array, would i be best to include the code to check then insert the data in here, or build a foreach loop which takes each item from the array and checks it?

//Create an array of all images that are Jpgs and start with 'n'
if ($dir = opendir($uploads))
{
  $images = array();

  foreach (glob($uploads."*.{jpg,jpeg,JPG,JPEG}", GLOB_BRACE) as $filename)
  {
    $f = str_replace($uploads, null, $filename);
    if (strtolower($f[0]) == 'n')
    {
      $images[] = $f;
    }
  }
}  

Thank you for your ongoing support.

Donna

Edit: Thank you @Lodder for your code. I have tried to implement it with my existing code but it seems to fail at the assignment of the $values variable. I have checked and cannot identify a particular syntax error? Can you spot anything? Here is my full code:

    <?php

    //display errors
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

    //init Joomla Framework 
    define('_JEXEC', 1); 
    define('JPATH_BASE', dirname(__FILE__).'/');
    define('DS', DIRECTORY_SEPARATOR); 

    require_once (JPATH_BASE .DS. 'includes' .DS. 'defines.php'); 
    require_once (JPATH_BASE .DS. 'includes' .DS. 'framework.php');


    //Tell the code where the product images are
    //Use joomla API <-----------------------------------------------------------Needs looking at
    $uploads = '/home/#######/public_html/images/stories/virtuemart/product/';

    //Create an array of all image sthat are Jpgs and start with 'n'
    if ($dir = opendir($uploads))
    {
      $images = array();

      foreach (glob($uploads."*.{jpg,jpeg,JPG,JPEG}", GLOB_BRACE) as $filename)
      {
        $f = str_replace($uploads, null, $filename);
        if (strtolower($f[0]) == 'n')
        {
          $images[] = $f;
        }
      }
    } 

    //$images = array('image1.jpg', 'image2.jpg');

    //Start comparison
    $db = JFactory::getDbo();

    $query = $db->getQuery(true);
    $query->select($db->quoteName('file_url'))
          ->from($db->quoteName('#__virtuemart_medias'));
    $db->setQuery($query);

    $rows = $db->loadColumn();

    $differences = array_diff($rows, $images);

    foreach ($differences as $key => $val)
    {
        $query = $db->getQuery(true);
        $query->clear(); 
$filetitle = substr($f, 0, strrpos($f, '.'));   
    $filepath = "images/stories/virtuemart/product/".$f.;    

        $columns = array('virtuemart_vendor_id', 'file_title', 'file_description', 'file_mimetype', 'file_type', 'file_url','published');
        $values = array($db->quote('1'), $db->quote($filename), $db->quote($filetitle), $db->quote('images/jpeg'), $db->quote('Product'), $db->quote($filepath), db->quote('1'));

       $query->insert($db->quoteName('#__virtuemart_medias'))
              ->columns($db->quoteName($columns))
              ->values(implode(',', $values));

        $db->setQuery($query);
        echo $db->replacePrefix((string) $query);
        //$db->execute();
    }
    ?>

Thank you.

Answer 1

I'll try to explain this as easily as possible:

$images = array('image1.jpg', 'image2.jpg');

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->quoteName('COLUMN_NAME'))
      ->from($db->quoteName('#__tablename'));
$db->setQuery($query);

$rows = $db->loadColumn();

$differences = array_diff($rows, $images);

foreach ($differences as $key => $val)
{
    $query = $db->getQuery(true);
    $query->clear();

    $columns = array('COLUMN_NAME');
    $values = array($db->quote($val));

    $query->insert($db->quoteName('#__tablename'))
          ->columns($db->quoteName($columns))
          ->values(implode(',', $values));

    $db->setQuery($query);
    $db->execute();
}

---

So for testing purposes, let's say $images is your array of image names.

You're firstly going to have to run a select query on your table that contains the images. So change COLUMN_NAME in the code above to whatever your column name is.

---

$rows = $db->loadColumn(); is now your array of all the results from your column.

---

$differences = array_diff($rows, $images); Then compares the array from your database to the array of images defined in the very first line of code. The results are an array of values that do not match.

---

You can then run a insert query in a foreach loop, that loops through the $differences array.

---

Hope this helps

Answer 2

There were a few points in your coding attempt to address.

• The $upload path and the file_url path were never going to match, so I've redesigned the process of calling glob() and querying the db table so that the values are comparable as "apples to apples" before calling array_diff().
• You are using $f in your second loop, but that variable will only be carrying the first loop's final iteration value. I think you probably mean to use $val in the second loop (and $key is not used, so doesn't not need to be declared).
• I'm not entirely confident that your column->value assignments were logically lined up in your snippet. I've made some adjustments based on my own thinking, but if they are not what you want, my diagnostic enqueueMessage() calls should help you to figure out what is happening.
• As a matter of best coding practice, you should minimize total trips to the database. I have written my code to only perform one INSERT query no matter how many rows of data are to be save.

I have run this on my localhost to be successful. I've packed as many comments and enqueueMessage() checkpoints as I could to help you see the what is generated throughout the process. This makes my snippet appear rather "messy", so when everything works as desired for you, just delete/comment all of my diagnostic lines. If you find any issues with my snippet, please let me know so that I can adjust my solution for the benefit of future researchers.

<?php    
//display errors
error_reporting(E_ALL);
ini_set("display_errors", 1);

//init Joomla Framework 
define('_JEXEC', 1); 
define('JPATH_BASE', dirname(__FILE__) . '/');
define('DS', DIRECTORY_SEPARATOR); 

require_once (implode(DS, [JPATH_BASE, 'includes', 'defines.php'])); 
require_once (implode(DS, [JPATH_BASE, 'includes', 'framework.php'));

//Tell the code where the product images are
$product_path_dirs = ['images', 'stories', 'virtuemart', 'product'];
$product_path = implode('/', $product_path_dirs);
$uploads = JPATH_BASE . DS . implode(DS, $product_path_dirs) . DS;
// $uploads = '/home/#######/public_html/images/stories/virtuemart/product/';
JFactory::getApplication()->enqueueMessage("<div>\$uploads = <br><b>{$uploads}</b></div>", 'notice');

//Create an array of all images that are jpgs and start with 'n'
JFactory::getApplication()->enqueueMessage("<div>Original Working Directory = <br><b>" . getcwd() . "</b></div>", 'notice');
chdir($uploads); // this eliminates the path from the glob() array values
JFactory::getApplication()->enqueueMessage("<div>New Working Directory = <br><b>" . getcwd() . "</b></div>", 'notice');

$server_filenames = glob("n*.{jpg,jpeg,JPG,JPEG}", GLOB_BRACE);
                    //       ^^^^^^^^^^^^^^^^^^^- whitelist of case-insensitive file extensions
                    //      ^- literal dot
                    //     ^- zero or more of any character
                    //    ^- starts with n

// apply url path to filenames
$server_filenames = array_map(function ($v) use ($product_path) {
    return "{$product_path}/{$v}";
}, $server_filenames);
JFactory::getApplication()->enqueueMessage("<div>\$server_filenames (as json) = <br><b>" . json_encode($server_filenames) . "</b></div>", 'notice');

// Retrieve existing image filenames from db
$db = JFactory::getDbo();
$select_query = $db->getQuery(true)
                   ->select("file_url")
                   ->from("#__virtuemart_medias")
                   ->where("file_url LIKE '{$product_path}/n%g'");  // some general filtering is applied here (this could be further refined)
                                                                    // assuming case-insensitive COLLATION utf8_general_ci
$db->setQuery($select_query);
JFactory::getApplication()->enqueueMessage("<div>Rendered Select Query<br><b>" . $select_query->dump() . "</b></div>", 'notice');
try
{
    $db_filenames = $db->loadColumn();
}
catch (Exception $e)
{
    JFactory::getApplication()->enqueueMessage("Select Query Syntax Error " . $e->getMessage(), 'error');
    // use $e->getMessage() only while debug and page is not viewable by public
    exit('script halted');
}

if (!$difference = array_diff($server_filenames, $db_filenames)) {  // declare $difference and check if empty
    JFactory::getApplication()->enqueueMessage("<div>No New Qualifying Image Files Found</div>", 'notice');
}
else
{
    JFactory::getApplication()->enqueueMessage("<div>\$db_filenames (as json) = <br><b>" . json_encode($db_filenames) . "</b></div>", 'notice');
    JFactory::getApplication()->enqueueMessage("<div>\$difference (as json) = <br><b>" . json_encode($difference) . "</b></div>", 'notice');
    // build query with all static parts
    $insert_query = $db->getQuery(true)
                       ->insert("#__virtuemart_medias")
                       ->columns("virtuemart_vendor_id, file_title, file_description, file_mimetype, file_type, file_url, published");

    // iterate new, unique filenames and write separate rows for insertion
    foreach ($difference as $new_filename) {
        $path_parts = pathinfo($new_filename);
        $values = [
            1,                               // virtuemart_vendor_id
            $db->q($path_parts['filename']), // file_title
            $db->q($path_parts['basename']), // file_description
            $db->q('images/jpeg'),           // file_mimetype
            $db->q('Product'),               // file_type
            $db->q($new_filename),           // file_url
            1                                // published
        ];
        $insert_query->values(implode(',', $values));
    }
    JFactory::getApplication()->enqueueMessage("<div>Rendered Insert Query<br><b>" . $insert_query->dump() . "</b></div>", 'notice');
    $db->setQuery($insert_query);
    try {
        $db->execute();
        JFactory::getApplication()->enqueueMessage("<div>" . $db->getAffectedRows() . " Rows Of Data Inserted", 'success');
    } catch (Exception $e) {
        JFactory::getApplication()->enqueueMessage("Insert Query Syntax Error: " . $e->getMessage(), 'error');
        // use $e->getMessage() only while debug and page is not viewable by public
    }
}