Re: csv import function doesn't create category_url correctly

dummy@example.com (jnewman67) — Fri, 05 Feb 2010 14:18:56 +0000

i don't think i posted this anywhere else, but after updating the csv_import category_url issue, i also created a function that updates the category_url when a category is updated. it updates the url for the edited category and all subcategories as well.

   /**************************************************************************
  ** name: update_subcat_urls()
  ** created by:  jnewman67
  ** description:  updates the category_urls after a category is updated/relocated
  ** parameters:  parent category_id and parent category_url
  ** returns:
  ***************************************************************************/    
  function update_subcat_urls($cat_id, $cat_url) {

        $db = new ps_DB;
        $dbc = new ps_DB;
        $q  = "SELECT * FROM category, category_xref ";
        $q .= "WHERE category_xref.category_parent_id='$cat_id' AND ";
        $q .= "category_xref.category_child_id=category.category_id ";
        $db->query($q);
        while ($db->next_record()) {
            $new_url = $cat_url . "_" . str_replace(" ", "", str_replace("_", "", strtolower($db->f("category_name"))));
            $q  = "UPDATE category ";
            $q .= "SET category_url = '" . $new_url . "' ";
            $q .= "WHERE category_id = '" . $db->f("category_id") . "' ";
            $dbc->query($q);
            $this->update_subcat_urls($db->f("category_id"), $new_url);
            }
        return True;
        }

this function should be added to ps_product_category.inc.

it is called from the "update" function in ps_product_category. the mods for that function call are here:

add this to the top of the function, just inside the $this->validate_update($d) check:

             //  check to see if updating the subcategories is necessary
            $update_subs = False;
            $q  = "SELECT * FROM category ";
            $q .= "WHERE category.category_id='" . $d["category_id"] . "' ";
            $db->query($q);
            if ($db->next_record()) {
                if ($db->f("cateogry_url") <> $d["category_url"]) $update_subs = True;
                }

add this to the end of the same $this->validate_update($d) check, right before the "return True":

      // Update subcategories if necessary
            if ($update_subs) $this->update_subcat_urls($d["category_id"], $d["category_url"]);

as a note, if you open/save all the root categories, you'll effectively update all the category_urls for every product in the site. and it does it quickly (900 in one shot wasn't even noticable on my server/site/equipment).

csv import function doesn't create category_url correctly

dummy@example.com (jnewman67) — Tue, 10 Jun 2008 12:49:38 +0000

there is actually a bug in the csv_import class - the function csv_category doesn't create the full category path when populating the category_url field. it only inserts the final category name as the category_url, not the full path of the category tree.

example: a new category path of "store/widgets/red/small" would result in all the categories being created and nested correctly, but the category_url would be "store", "widgets", "red" and "small" for each category creeated, respectively. they should be "store", "store_widgets", "store_widgets_red" and "store_widgets_red_small" respectively.

in fairness, any changes made to the category after it's been imported would correctly reset the value in the DB. categories unmodified would retain their incorrect url.

reason this is important: if you want to find all products that are in a category and all subcategories in one search, you only have to search for products who's categories have a " category_url LIKE '$category_url%' ". much simplier than recursively traversing the category tree from the starting point to the bottom to find all the possible category_ids that are invovled.

this updated function fixes that problem.

this is an updated csv_category in ps_csv.inc

  /**************************************************************************
  ** name: csv_category()
  ** created by: John Syben
  ** updated by: jnewman67
  ** Creates categories from slash delimited line
  ***************************************************************************/
  function csv_category($line) {
    // Explode slash delimited category tree into array
    $category_list = explode("/", $line);
    $category_count = count($category_list);

    $db = new ps_DB;
    $category_parent_id = '0';
        $full_url = "";

      // For each category in array
      for($i = 0; $i < $category_count; $i++) {
        // See if this category exists with it's parent in xref
        $q = "SELECT category.category_id FROM category,category_xref ";
        $q .= "WHERE category.category_name='" . $category_list[$i] . "' ";
        $q .= "AND category_xref.category_child_id=category.category_id ";
        $q .= "AND category_xref.category_parent_id='$category_parent_id'";
        $db->query($q);
          // If it does not exist, create it
          if ($db->next_record()) { // Category exists
            $category_id = $db->f("category_id");
          }
          else { // Category does not exist - create it
            $hash_secret="PHPShopIsCool";
            $category_id = md5(uniqid($hash_secret));
            $timestamp = time();

                        $cat_url = strtolower($category_list[$i]);
                        $cat_url = str_replace(" ", "",$cat_url);
                        $cat_url = str_replace("_", "",$cat_url);
                        if ($full_url == "") {
                            $full_url = $cat_url;
                        } else {
                            $full_url .= "_" . $cat_url;
                        }

            // Add category
            $q = "INSERT INTO category ";
            $q .= "(category_id,vendor_id,category_name,category_url,category_publish,cdate,mdate) ";
            $q .= "VALUES ('";
            $q .= $category_id . "','";
            $q .= "1', '";
            $q .= $category_list[$i] . "', '";
            $q .= "$full_url', '";
            $q .= "Y', '";
            $q .= $timestamp . "', '";
            $q .= $timestamp . "')";
            $db->query($q);

            // Create xref with parent
            $q = "INSERT INTO category_xref ";
            $q .= "(category_parent_id, category_child_id) ";
            $q .= "VALUES ('";
            $q .= $category_parent_id . "', '";
            $q .= $category_id . "')";
            $db->query($q);
          }
        // Set this category as parent of next in line
        $category_parent_id = $category_id;
      } // end for

    // Return the last category_id which is where the product goes
    return $category_id;

  } // End function csv_category

the full_url is used to track the full tree category path. any lines with that variable on it are the ones that were actually changed.

Edikon Forums - csv import function doesn't create category_url correctly

Re: csv import function doesn't create category_url correctly

csv import function doesn't create category_url correctly