Check category_id to parent_id

I want to see if the category_id of a product_id is within the parent_id.

The code I am doing does the following:

it takes an input from the user which is the category names separated by commas. It then checks to see if the products category_name is found within the array of category names input by the user. The code works, however it can only be used for bottom-level category names and not parent category names.

Here is the database query which then joins the different tables together by their similar keys:

 
$products_query = "SELECT p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_tax_class_id, p.products_price_sorter, GREATEST(p.products_date_added, p.products_last_modified, p.products_date_available) AS base_date, m.manufacturers_name, p.products_quantity, pt.type_handler, pc.categories_id, cd.categories_name, c.parent_id
           FROM " . TABLE_PRODUCTS . " p
            LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id)
            LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON (p.products_id = pd.products_id)
            LEFT JOIN " . TABLE_PRODUCT_TYPES . " pt ON (p.products_type=pt.type_id)
            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " pc ON (p.products_id = pc.products_id)
            LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON (pc.categories_id = cd.categories_id)
            LEFT JOIN " . TABLE_CATEGORIES . " c ON (cd.categories_id = c.categories_id)
           WHERE p.products_status = 1
            AND p.product_is_call = 0
            AND p.product_is_free = 0
            AND pd.language_id = " . $languages->fields['languages_id'] ."
           ORDER BY p.products_id ASC";
 $products = $db->Execute($products_query);

I'm afraid the purpose of your post is unclear. Are you are asking for assistance with this, or posting what you have done for other people's benefit?

I need assistance.

The user can type in any category names separated by commas into the admin config and then the script will split them into an array. It then compares every product in the database to see if their category name matches the category any of the category names input by the user. If so, print that product in the feed file. I have a working script already which will print the product if it's lowest level category name matches one input by the user. But, I'd like to be able to include all products in higher level categories which would only contain sub-categories.

For example, if this was a heirarchy of categories:

Computers -> Video Cards -> BFG 8800 GTX, BFG 8800 GTS, etc..
-> Sound Cards -> Creative X-FI, M-Audio Revolution, etc..

Then inputting the word computers as a selected category will add all products that are contained in sub-categories of the top-level category, computers.

Here is more code to make it easier:

 
$products_query = "SELECT p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_tax_class_id, p.products_price_sorter, GREATEST(p.products_date_added, p.products_last_modified, p.products_date_available) AS base_date, m.manufacturers_name, p.products_quantity, pt.type_handler, pc.categories_id, cd.categories_name, c.parent_id
           FROM " . TABLE_PRODUCTS . " p
            LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id)
            LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON (p.products_id = pd.products_id)
            LEFT JOIN " . TABLE_PRODUCT_TYPES . " pt ON (p.products_type=pt.type_id)
            LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " pc ON (p.products_id = pc.products_id)
            LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON (pc.categories_id = cd.categories_id)
           WHERE p.products_status = 1
            AND p.product_is_call = 0
            AND p.product_is_free = 0
            AND pd.language_id = " . $languages->fields['languages_id'] ."
           ORDER BY p.products_id ASC";
 $products = $db->Execute($products_query);

This part of the code checks to see if the category name matches the one input by the user:

   if(zen_google_base_categories(trim(GOOGLE_BASE_CATEGORIES), trim($products->fields['categories_name'])) == true || GOOGLE_BASE_CATEGORIES == "") { // check to see if category limits are set.  If so, only process for those categories.

And this is the function above:

 
function zen_google_base_categories($str, $string) {
  $str = strtolower($str);
  $str = split(",", $str);
  $string = strtolower($string);
  if(in_array($string, $str)) {
   return true;
  } else {
   return false;
  }
 }

I need to be able to match the product_id of the product, to the correct parent_id of the top-level category, and then finally the parent_id to the correct category_name. I do have a solution, but I am just wondering if there is a better way. Here is my solution:

 
function zen_froogle_category_tree($id_parent=0, $cPath='', $cName='', $cats=array()){
  global $db, $languages;
  $cat = $db->Execute("SELECT c.categories_id, c.parent_id, cd.categories_name
             FROM " . TABLE_CATEGORIES . " c
              LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.categories_id = cd.categories_id
             WHERE c.parent_id = '" . (int)$id_parent . "'
             AND cd.language_id='" . (int)$languages->fields['languages_id'] . "'
             AND c.categories_status= '1'",
             '', false, 150);
  while (!$cat->EOF) {
   $cats[$cat->fields['categories_id']]['name'] = (zen_not_null($cName) ? $cName . ', ' : '') . zen_froogle_sanita($cat->fields['categories_name']);
   $cats[$cat->fields['categories_id']]['cPath'] = (zen_not_null($cPath) ? $cPath . '_' : '') . $cat->fields['categories_id'];
   if (zen_has_category_subcategories($cat->fields['categories_id'])) {
    $cats = zen_froogle_category_tree($cat->fields['categories_id'], $cats[$cat->fields['categories_id']]['cPath'], $cats[$cat->fields['categories_id']]['name'], $cats);
   }
   $cat->MoveNext();
  }
  return $cats;
 }

This will create an array of all the categories a product is in, which I could then use as my comparison.

Ok, I'm going to try using my solution, but now I need to find a function that compares one array to another array to see if any of the values in the first array appear in the second. Does something exist or will I need to create my own?

in_array will check if a string is within an array and isn't useful.

Getting my head around your objective and code is probably a bit too much for a forum response - sorry. But your second question is much easier.

The following will do what you describe

function in_both_arrays($array1, $array2) {
 return array_intersect($array1, $array2) == NULL ? false : true;
}

It is not restricted to strings. Indeed it will compare across data types, so if you had (int)2 in one array and '2' in the other it would return true. If you wanted strict (type) comparison, this can be set as a flag in the array_intersect function.

I solved the issue last night at 2am and am now documenting the next release of the Google Base Feeder for this afternoon.

The issue I was having was that for some reason array_intersect was not working past the first value even though I did a print_r to check that both were arrays with at least one equal value among the array.

If anyone ever needs to compare two arrays, here is the code:

 
 function zen_google_base_categories($words, $compwords) {
 $match = 0;
 $compwords = split(",", $compwords);
 $words = split(",", $words);
 foreach ($words as $word) {
  foreach ($compwords as $compword) {
   if (trim(strtolower($compword)) == (trim(strtolower($word)))) {
    $match++;
   }
  }
 }
 if ($match > 0) {
  return true;
 } else {
  return false;
 }
}