Update

[Jluis]

In order to update  ps_feature_value  , ps_feature_product  database  for existing products with valaues from a csv file  as shown in the picture below , i made the function below  , an error appears :

CREATE PROCEDURE processorders()

BEGIN

 DECLARE done INT DEFAULT FALSE 

  DECLARE b , c , d int(10) 
 
   DECLARE ordernumbers CURSOR FOR SELECT  id_feature,id_product,id_feature_value  FROM psdms_feature_product
    
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE

   
    OPEN ordernumbers

    read_loop:LOOP

    FETCH ordernumbers INTO b,c,d

  IF done THEN
      LEAVE read_loop
    END IF 
   
    IF (c) THEN
    IF (b == 510) THEN
   
					update psdms_feature_value_lang t1

				    LEFT JOIN
							psdms_feature_product t2
					ON      t1.id_feature_value = t2.id_feature_value
				    SET value ='95090€'
					WHERE   t2.id_product = 1 AND  t2.id_feature = 510

    ELSE

    
insert into  psdms_feature_value ( id_feature ) values (510)
insert into  psdms_feature_product ( id_feature, id_product , id_feature_value) values (b , c , d )

END IF
    
END IF
   
END LOOP
 
 CLOSE ordernumber 

END 

 

the error is : 

 

Quote

Erreur dans la requête (1064): Syntax error near 'DECLARE b int(10) DECLARE c int(10) DECLARE d int(10) DECLARE orde' at line 7

Any suggestion please ??

[selectshop.at]

Seems you are using a third partie module. For to update prices you need also to update some other tables. If you are not the developer of the module, than please contact developer. To make manual updates in database without knowing the dependencies Prestashop is writing is a high risk. If you are the developer of the module, than you should take a look into database schema.

ps_product and ps_product_shop is also a required table which handles prices.

[Jluis]

i'm the owner , i would like to modify " caractéristique->prix HT  " as mentionned in the image .  If the product has that old value just we update it , if not we create a new feature and associate to the situable product as mentionned in code , this does not work? 

[Jluis]

it's not a module , just a function that i may execute into Mysql database host server

[selectshop.at]

As written on previous answer your query is not updating needed tables. Ammend.

[Jluis]

any suggestion will be appreciated  please as  my  first steps with prestashop developpement 

[selectshop.at]

You read what I wrote ? Ammend the query by including also the needed tables, which are: ps_products and ps_product_shop.

[musicmaster]

I am not an expert on Mysql procedures. But it seems to me that this error says that there is something wrong with your syntax. If that is indeed the case you would find more help on a mysql forum as your problem has nothing to do with Prestashop. 

If I had to make one guess it would be to put a semicolon after "DECLARE b , c , d int(10)".

[Jluis]

I ask you as an expert prestashop if the logic of the implementation is correct ??

[Jluis]

@PrestaShop Superstar , we should make a  put a join at this line with mentionned tables ?

   DECLARE ordernumbers CURSOR FOR SELECT id_feature,id_product,id_feature_value  FROM psdms_feature_product ;

[selectshop.at]

Each sql statement should be ended by ";". But for to update prices you also need to update the tables I mentioned before.

[Jluis]

sorry , but ps_product and ps_product_shop tables doesn't contain any Column  as shown in image ( the boxed area in red ) characteristics
section , price HT attribute

[Jluis]

@PrestaShop Superstar , Actuallay there is no error , when i run only that part of the code  , it works well and the attribute value chnages 

update psdms_feature_value_lang t1

				    LEFT JOIN
							psdms_feature_product t2
					ON      t1.id_feature_value = t2.id_feature_value
				    SET value =666
					WHERE   t2.id_product = 1 AND  t2.id_feature = 362;

but integrated in the function nothing is done 

[selectshop.at]

7 minutes ago, Jluis said:

but integrated in the function nothing is done 
 

Yes, because there are the dependent entries in ps_product and ps_shop_products missing in your SQL query.

 

[Jluis]

@PrestaShop Superstar , I have tried that relationship join between tables but nothing is done , is tha the way ?

 DECLARE ordernumbers CURSOR FOR SELECT id_feature,id_product,id_feature_value  FROM psdms_feature_product  z1,  psdms_product_shop z2 , psdms_product z3 WHERE z1.id_product=z2.id_product AND z1.id_product=z3.id_product ;

 

Edited February 12, 2018 by Jluis (see edit history)

[selectshop.at]

As you don't post the whole query it is difficult to help. If you are updating PRICES you need to add:

 

UPDATE ps_product

and

UPDATE ps_product_shop

as well.

 

If you are updateing only FEATURES, than take a look into the database schema I linked before for other table dependencies.

 

[Jluis]

@PrestaShop Superstar , it is the whole code 

DELIMITER $$
CREATE PROCEDURE testt61()

BEGIN


 DECLARE done INT DEFAULT FALSE;

  DECLARE b , c , d INT(10) unsigned;
 
   DECLARE ordernumbers CURSOR FOR SELECT id_feature,id_product,id_feature_value  FROM psdms_feature_product  z1,  psdms_product_shop z2 , psdms_product z3 WHERE z1.id_product = z2.id_product AND z1.id_product = z3.id_product ;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   
    OPEN ordernumbers ;

    read_loop:LOOP FETCH ordernumbers INTO  b , c , d ;

    IF done  THEN
      LEAVE read_loop ;
    END IF ; 
   
    IF c = 1  THEN
    IF b = 362 THEN
   
					update psdms_feature_value_lang t1

				    LEFT JOIN
						psdms_feature_product t2
					ON      t1.id_feature_value = t2.id_feature_value
				    SET value ='888€'
					WHERE   t2.id_product = 1 AND  t2.id_feature = 362;

    ELSE

    
insert into  psdms_feature_value ( id_feature ) values (510);
insert into  psdms_feature_product  values (b , c , d );

END IF;
   
END IF;
   
END LOOP;
 
 CLOSE ordernumbers;

END ;
DELIMITER ;