Compare folder with DB

c1000mg · May 22, 2007

Hi,

I was wondering if anyone could help me with a code that will make my job a bit easier...
I need to compare my Database with the content on a folder, I have a Table call ProductDB (with fields SKU, ProductName and Distributor) and I have a folder with images named with SKU.jpg, I will like to get a report of all the missing pictures in the folder.

Thank you all!!!

laserlight · May 22, 2007

Can you select the relevant field from the database and loop through the result set?
Can you open a directory and read the filenames within it?

c1000mg · May 22, 2007

I did loop results, but I use Dreamweaver to do it, I have a bit of knowledge of php but no that much!!
I used the code on php.net for opendir and it list all the files in the folder... but i have no idea how to combine both codes.

<?php virtual('/Connections/ProducDB.php'); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_ProductDB = 20;
$pageNum_ProductDB = 0;
if (isset($_GET['pageNum_ProductDB'])) {
  $pageNum_ProductDB = $_GET['pageNum_ProductDB'];
}
$startRow_ProductDB = $pageNum_ProductDB * $maxRows_ProductDB;

mysql_select_db($database_ProducDB, $ProducDB);
$query_ProductDB = "SELECT * FROM ProductDB ORDER BY ProductDB.SKU";
$query_limit_ProductDB = sprintf("%s LIMIT %d, %d", $query_ProductDB, $startRow_ProductDB, $maxRows_ProductDB);
$ProductDB = mysql_query($query_limit_ProductDB, $ProducDB) or die(mysql_error());
$row_ProductDB = mysql_fetch_assoc($ProductDB);

if (isset($_GET['totalRows_ProductDB'])) {
  $totalRows_ProductDB = $_GET['totalRows_ProductDB'];
} else {
  $all_ProductDB = mysql_query($query_ProductDB);
  $totalRows_ProductDB = mysql_num_rows($all_ProductDB);
}
$totalPages_ProductDB = ceil($totalRows_ProductDB/$maxRows_ProductDB)-1;

$queryString_ProductDB = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_ProductDB") == false && 
        stristr($param, "totalRows_ProductDB") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_ProductDB = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_ProductDB = sprintf("&totalRows_ProductDB=%d%s", $totalRows_ProductDB, $queryString_ProductDB);
?>

<style type="text/css">
<!--
.style5 {font-family: Arial, Helvetica, sans-serif; font-weight: bold; color: #FFFFFF; }
.style6 {font-family: Arial, Helvetica, sans-serif}
.style10 {font-family: Arial, Helvetica, sans-serif; font-size: 12px; }
.style11 {font-size: 12px}
-->
</style>

<table width="800" border="0" align="center" cellpadding="8" cellspacing="1">
  <tr>
    <td width="80" align="center" bgcolor="#003366"><span class="style5">Picture</span></div></td>
    <td width="75" align="center" bgcolor="#003366"><div align="center"><span class="style5">ASWO#</span></div></td>
    <td width="380" bgcolor="#003366"><span class="style5">Product Name </span></td>
    <td width="222" bgcolor="#003366"><span class="style5">Manufacturer</span></td>
  </tr>
  <?php do { ?>
    <tr bgcolor="#C4EDFF">
      <td><img src="/untitled/Images/<?php echo $row_ProductDB['SKU']; ?>.jpg" width="80" height="80" /></td>
      <td valign="top"><div align="center"><span class="style6"><?php echo $row_ProductDB['SKU']; ?></span></div></td>
      <td valign="top"><span class="style6"><?php echo $row_ProductDB['pretty_name']; ?></span></td>
      <td valign="top"><span class="style6"><?php echo $row_ProductDB['manufacturer']; ?></span></td>
    </tr>
    <?php } while ($row_ProductDB = mysql_fetch_assoc($ProductDB)); ?>
</table>


<?php
mysql_free_result($ProductDB);
?>


<?php
$dir = "./Images/";
// Open a known directory, and proceed to read its contents
if (is_dir($dir)) {
    if ($dh = opendir($dir)) {
        while (($file = readdir($dh)) !== false) {
            if($file == "." || $file == ".."){continue;}
			echo "$file <br>";
        }
        closedir($dh);
    }
}
?>

kilbey1 · May 23, 2007

It sounds to me as if once you open the directory, you should try to get the filenames into an array; then you'll have to compare your database result against that array using something like in_array().

Here is but one way to get your directory structure into an array:
Recursive Directory Array
Then, try looking into in_array() and preg_match().

c1000mg · May 23, 2007

I've been reading about and I found that can also be done with array_diff ... and it will actually give me the result I need, but I can't find a way to make the arrays from My DB and from the folder (because the folder has an extension ".jpg"), I was wondering If the do-while loop I have in my code could be modified to look for the Variable $FileName in the directory and if the file is in the folder it will skip that record and go to the next??? or something like that!!!, sorry for my English and php limitations :o , I've been trying to learn a bit of php but is a very slow process.

<?php do { ?>

<?php 
$FileName = $row_ProductPicDB['SKU'].".jpg";
?>
<tr bordercolor="#006699" bgcolor="#D5F0FC">
    <td align="center"><img src="/untitled/Images/<?php echo $FileName; ?>" width="45" height="45" /></td>

  <td><?php echo $row_ProductPicDB['SKU']; ?></td>
  <td><?php echo $row_ProductPicDB['pretty_name']; ?></td>
  <td><?php echo $row_ProductPicDB['manufacturer']; ?></td>
</tr>
<?php } while ($row_ProductPicDB = mysql_fetch_assoc($ProductPicDB)); ?>
</table>
</body>
</html>
<?php

mysql_free_result($ProductPicDB);

I need this code to make my life a lot easier I need to have a picture library and I like this code to tell me the pictures I'm missing to be able to take them... THANK YOU ALL FOR YOUR HELP... I'm missing like 6500 picture I have to take by the end of next month...lol

c1000mg · May 23, 2007

kilbey1 wrote:
It sounds to me as if once you open the directory, you should try to get the filenames into an array; then you'll have to compare your database result against that array using something like in_array().

Here is but one way to get your directory structure into an array:
Recursive Directory Array
Then, try looking into in_array() and preg_match().

Oh i didn't read this I'm checking it up now... TY but It takes me a long time to type !!!

c1000mg · May 25, 2007

HI everyone

I tried in_array to build the table and it works, but it doesn't work properly (again is because i mainly use deramweaver), I have to have all the records in one page!! If anyone know how can i make it to show in different pages please let me know (because I don't have a developers logic )

  <?php 

$dir = "./Images/";
$filesDir = scandir($dir,1);


  do { ?>

<?php 
$FileName = $row_ProductPicDB['SKU'].".jpg";
if (in_array($FileName, $filesDir, true)) {} else {
?>
<tr bordercolor="#006699" bgcolor="#D5F0FC">
    <td align="center"><img src="/untitled/Images/<?php echo $FileName; ?>" width="45" height="45" /></td>

  <td><?php echo $row_ProductPicDB['SKU']; ?></td>
  <td><?php echo $row_ProductPicDB['pretty_name']; ?></td>
  <td><?php echo $row_ProductPicDB['manufacturer']; ?></td>
</tr>
<?php }} while ($row_ProductPicDB = mysql_fetch_assoc($ProductPicDB)); ?>
</table>

Here is the full Dreamweaver Code:

<?php virtual('/Connections/ProducDB.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_ProductPicDB = 1000000;
$pageNum_ProductPicDB = 0;
if (isset($_GET['pageNum_ProductPicDB'])) {
  $pageNum_ProductPicDB = $_GET['pageNum_ProductPicDB'];
}
$startRow_ProductPicDB = $pageNum_ProductPicDB * $maxRows_ProductPicDB;

mysql_select_db($database_ProducDB, $ProducDB);
$query_ProductPicDB = "SELECT * FROM ProductDB ORDER BY ProductDB.SKU";
$query_limit_ProductPicDB = sprintf("%s LIMIT %d, %d", $query_ProductPicDB, $startRow_ProductPicDB, $maxRows_ProductPicDB);
$ProductPicDB = mysql_query($query_limit_ProductPicDB, $ProducDB) or die(mysql_error());
$row_ProductPicDB = mysql_fetch_assoc($ProductPicDB);

if (isset($_GET['totalRows_ProductPicDB'])) {
  $totalRows_ProductPicDB = $_GET['totalRows_ProductPicDB'];
} else {
  $all_ProductPicDB = mysql_query($query_ProductPicDB);
  $totalRows_ProductPicDB = mysql_num_rows($all_ProductPicDB);
}
$totalPages_ProductPicDB = ceil($totalRows_ProductPicDB/$maxRows_ProductPicDB)-1;

$queryString_ProductPicDB = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_ProductPicDB") == false && 
        stristr($param, "totalRows_ProductPicDB") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_ProductPicDB = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_ProductPicDB = sprintf("&totalRows_ProductPicDB=%d%s", $totalRows_ProductPicDB, $queryString_ProductPicDB);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style11 {color: #FFFFFF; font-weight: bold; font-family: Arial, Helvetica, sans-serif; font-size: 14px; }
-->
</style>
</head>

<body>
<table width="800" border="1" align="center" cellpadding="3" cellspacing="3">
  <tr>
    <td width="50" align="center" bgcolor="#006699"><span class="style11">Pic</span></td>
    <td width="70" bgcolor="#006699"><span class="style11">SKU</span></td>
    <td bgcolor="#006699"><span class="style11">pretty_name</span></td>
    <td width="195" bgcolor="#006699"><span class="style11">manufacturer</span></td>
  </tr>
  <?php 

$dir = "./Images/";
$filesDir = scandir($dir,1);


  do { ?>

<?php 
$FileName = $row_ProductPicDB['SKU'].".jpg";
if (in_array($FileName, $filesDir, true)) {} else {
?>
<tr bordercolor="#006699" bgcolor="#D5F0FC">
    <td align="center"><img src="/untitled/Images/<?php echo $FileName; ?>" width="45" height="45" /></td>

  <td><?php echo $row_ProductPicDB['SKU']; ?></td>
  <td><?php echo $row_ProductPicDB['pretty_name']; ?></td>
  <td><?php echo $row_ProductPicDB['manufacturer']; ?></td>
</tr>
<?php }} while ($row_ProductPicDB = mysql_fetch_assoc($ProductPicDB)); ?>
</table>
<br />
<br />
<table border="0" align="center">
  <tr>
    <td><?php if ($pageNum_ProductPicDB > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_ProductPicDB=%d%s", $currentPage, 0, $queryString_ProductPicDB); ?>"><img src="/images/First.gif" border="0" /></a>
          <?php } // Show if not first page ?>
    </td>
    <td><?php if ($pageNum_ProductPicDB > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_ProductPicDB=%d%s", $currentPage, max(0, $pageNum_ProductPicDB - 1), $queryString_ProductPicDB); ?>"><img src="/images/Previous.gif" border="0" /></a>
          <?php } // Show if not first page ?>
    </td>
    <td><?php if ($pageNum_ProductPicDB < $totalPages_ProductPicDB) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_ProductPicDB=%d%s", $currentPage, min($totalPages_ProductPicDB, $pageNum_ProductPicDB + 1), $queryString_ProductPicDB); ?>"><img src="/images/Next.gif" border="0" /></a>
          <?php } // Show if not last page ?>
    </td>
    <td><?php if ($pageNum_ProductPicDB < $totalPages_ProductPicDB) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_ProductPicDB=%d%s", $currentPage, $totalPages_ProductPicDB, $queryString_ProductPicDB); ?>"><img src="/images/Last.gif" border="0" /></a>
          <?php } // Show if not last page ?>
    </td>
  </tr>
</table>
</body>
</html>
<?php

mysql_free_result($ProductPicDB);


?>

kilbey1 · May 25, 2007

So are you saying that it works, but you want to view the results on different pages, like pagination (1, 2, 3...)? Because for that, you might try here.

Compare folder with DB

Cc1000mg

laserlight

Cc1000mg

Kkilbey1

Cc1000mg

Cc1000mg

Cc1000mg

Kkilbey1