Perl sql query for adding categories problem.

[carpealexdiem]

I am making a script in perl for autoimporting and updating my shop(Categories,Products,Images,Prices,etc).

 

My problem is that after I make the categories with various sql queries, they don't show on the main page if I don't delete the smarty cache. Even so, after I delete the smarty cache, when I click on the categories it gives me 404 Error. I have found a fix but it's not very relevant to my automation script;What I did was open any random category and click modify and then click save. This makes my categories as they should be but I need a more development friendly way to achieve this.

 

I am 99.9% sure that my SQL queries are correct and in fact when clicking to Save it doesn't change anything in the MySQL tables. So I think this is still cache related or something like that.

Edited March 21, 2013 by carpealexdiem (see edit history)

[Terragg]

Ah yes, Categories - my old nemesis.

 

If you're running 1.5+ you may want to disable the cache before updating, update, and then re-enable it after updating.

 

BO Interface

Advanced Parameters > Performance > Smarty > Cache = No

 

Code level

UPDATE `ps_configuration` SET `value` = '0' WHERE `ps_configuration`.`name` = 'PS_SMARTY_CACHE';

 

Also with 1.5+ dont forget to update the ps_category_lang, ps_category_shop, and ps_category_group tables.

 

Since you're stomping around the tables anyway, make sure that you have the exactly correct values for:

level_depth

nleft

nright

position

 

Getting either the nleft or nright number off by even one means the category data tree is corrupted and produces 'unexpected' results. Not that I have experienced that. Nope. Not me. Never.

 

Position ( in both ps_category and in the ps_category_shop table ) needs to be spot on or the order can change in 'unexpected' ways.

 

HTH,

[carpealexdiem]

Thank you Terragg. I will try your suggestions right away.

For the nleft and nright I use a simple script because I don't have subcategories yet:

 

 

my $nleft = $dbh->selectrow_array('SELECT MAX(nright) FROM ps_category') + 1;
my $nright = $nleft + 1;

 

As for the position I change it very easily in both tables.

 

UPDATE: I tried your code and it works partially. I mean I don't have to delete the cache anymore but the category created still gives 404 Error when clicked. I still have to edit and save from admin interface...

 

UPDATE: I DID IT!!!!! With the following code:

 

 

my $nleft = $dbh->selectrow_array("SELECT MAX(nright) FROM ps_category WHERE id_category='2'");
my $nright = $dbh->selectrow_array("SELECT MAX(nright) FROM ps_category WHERE id_category='1'");
my $nright2 = $nright + 1;
my $nright1 = $nright2 + 1;


$sth = $dbh->prepare( q{
UPDATE ps_category SET nright=? WHERE id_category='1'
});
$sth->execute($nright1);

$sth = $dbh->prepare( q{
UPDATE ps_category SET nright=? WHERE id_category='2'
});
$sth->execute($nright2);

 

 

And ofcourse using the UPDATE Smarty Cache query before and after the script.

 

Now I am making it more complex by passing the data from the csv to autocreate new categories and subcategories.

 

Thank you very much.

Edited March 21, 2013 by carpealexdiem (see edit history)

[carpealexdiem]

Unfortunately it doesn't solve my problem. I must find a way to empty the blockcategories.tpl.php from /cache/smarty/cache folder or to force update the cache... Because it doesn't work with only the code:

 

UPDATE`ps_configuration`SET`value`='0'WHERE`ps_configuration`.`name`='PS_SMARTY_CACHE';

Edited March 21, 2013 by carpealexdiem (see edit history)

[Terragg]

I'm still learning the internals, but a webpage request might need to come in while caching is off. (A simple curl command line request would be fine). Although I really haven't had an issue with blockcategories, double check that:

 

BO Interface:

Advanced Parameters > Performance > Caching > Use cache = N

 

Which isn't the Smarty Cache setting. I don't recall how to change it at the code level.

 

But I still can't help thinking that your node tree isn't set right. When I do backend updates, I make sure to regenerate the node tree using the PrestaShop internals ( rather than re-writing it in perl ). I use an update_categories.php file :

 

<?php
// Pull in basic settings
require( dirname( __FILE__ ).'/../../../config/config.inc.php');
// Regenerate the pre-ordered node sets
echo "Regenerating Tree Traversal Data...\n";
Category::regenerateEntireNtree();
echo "Done.\n";
?>

 

Since this file lives in module I developed, the require() path will be different based on where you put the php file. You can run it from the command line by:

 

$> php -f update_categories.php

 

For just a few categories, it'll be relatively quick. For the 400+ categories I normally operate on it will take about 90 seconds, give or take depending on server load.

 

HTH,

[rabacik_pl]

 

But I still can't help thinking that your node tree isn't set right. When I do backend updates, I make sure to regenerate the node tree using the PrestaShop internals ( rather than re-writing it in perl ). I use an update_categories.php file :

 

<?php
// Pull in basic settings
require( dirname( __FILE__ ).'/../../../config/config.inc.php');
// Regenerate the pre-ordered node sets
echo "Regenerating Tree Traversal Data...\n";
Category::regenerateEntireNtree();
echo "Done.\n";
?>

 

Since this file lives in module I developed, the require() path will be different based on where you put the php file. You can run it from the command line by:

 

$> php -f update_categories.php

 

For just a few categories, it'll be relatively quick. For the 400+ categories I normally operate on it will take about 90 seconds, give or take depending on server load.

 

HTH,

 

This work for me fine. )