Checking for missing images script

[tessthepup]

Hi guys, I hope you are all well.

I am revisting this script after a few months on and off due to work commitments.

I need to pull in the data from the products table i.e. gtin and also exclude products that are inactive.

I am a bit stumped on the query whether to left join, inner join etc.

Any help offered to get me to the next stage would be appreciated.

Code: Select all

/* Our products_description array is now built, start checking data. */
    $missing_description = [];

    foreach ($db->fetch_all("SELECT products_id, language_id, products_name, products_description FROM products_description") as $product) {
      if (empty($product['products_description'])) {
      Guarantor::guarantee_subarray($missing_description, $product['products_id']);
      $missing_description[$product['products_id']][$product['language_id']] = $product['products_name'] ?? 'Name missing';
      }
     }
?>

   <div class="w-75 mt-4 mx-auto"><h3>Missing Products Description Report</h3></div>
   <div class="w-75 mt-4 mx-auto"><h6>The table below is the result of checking the products_description table for missing product descriptions.</h6></div>

   <table class="table w-75 table-fit mt-4 mx-auto">
    <thead class="thead-light">
     <tr>
      <th><?php echo 'id'; ?></th>
      <th><?php echo 'Name'; ?></th>
      <th></th>
     </tr>
    </thead>

<?php
   foreach ($missing_description as $id => $data) {
    foreach ($data as $l => $name) {
?>

    <tr>
     <td><?= $id ?></td>
     <td><?= $name ?></td>
   </tr>
<?php
    }
   }
?>
 </tr>
</table>

[burt]

There is (almost always) example code somewhere in Phoenix. The example code might be close to what you need or give ideas for what you need;

https://github.com/CE-PhoenixCart/Phoen ... fo.php#L21

Maybe you don't need;
and p.products_id = " . (int)$_GET['products_id'] . "
and maybe not the language bit as well
and probably not the status bit as well

[ecartz]

exclude products that are inactive.

This suggests that you do need the status part.

Get the query working in phpMyAdmin and then copy it into the script.

I can't tell what that script is trying to do, so I can't tell you how to actually do that. Maybe

Code: Select all

SELECT pd.*, p.* FROM products_description pd INNER JOIN products p ON pd.products_id = p.products_id WHERE p.products_status = 1 AND pd.products_description = ''

Since you're checking data from both tables in the where clause, there's no point in doing a left join.

[tessthepup]

@burt @ecartz

Thanks burt, I got the query working

ecartz - the basic script shows any products with missing descriptions in the database.

Any tips on getting the gtin into the foreach loop. I got the gtin number in there but it displayed the same gtin for each product and not the unique one for each product.

[ecartz]

Code: Select all

      $missing_description[$product['products_id']][$product['language_id']] = ['name' => $product['products_name'] ?? 'Name missing', 'gtin' => $product['products_gtin'];

and fix the other code to match.