SQL Manager Export Products

[rogasgr]

So i  am making a scenario for a client to use csv files to update his 1000+ products every once in a while. I found many nice information and i am currently stuck a bit. 

 

I found a great sql syntax to export image urls and it works fine but i do not know how to do that for multiple images as CSV Import of prestashop works with this.

 

The Query has the image URL from here https://www.prestashop.com/forums/topic/353557-tutorial-helpful-sql-queries/

This is the SQL Manager query that i have at the moment.

SELECT p.id_product,p.reference, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.id_category) SEPARATOR ",") as categories, p.price,p.unity as MonadaMetrisiUnity,p.price/p.unit_price_ratio as TimiAnaMonadaMetrisis, p.id_tax_rules_group, p.wholesale_price,  p.supplier_reference, p.id_supplier, p.id_manufacturer, p.quantity as StockQuantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now as AvailabilityText, pl.available_later,
case
    when length(im.`id_image`)=6 then
     concat('http://www.evo.gr/prest/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http://www.evo.gr/prest/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http://www.evo.gr/prest/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http://www.evo.gr/prest/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://www.evo.gr/prest/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://www.evo.gr/prest/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end as "ImgURL_1"
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_image im ON (im.id_product= p.id_product)
WHERE pl.id_lang = 2
AND cl.id_lang = 2
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product     

Edited July 22, 2015 by rogasgr (see edit history)

[rogasgr]

it seems that i can wrap each WHEN of the CASE with GROUP_CONCAT(DISTINCT(   ) SEPARATOR ",")

 

but now the thing is that when a product has 2 images then the case doesnot work OK. 

 

http://www.FOO.gr/prest/img/p/2/2/6/226.jpg,http://www.FOO.gr/prest/img/p/2/9//29.jpg    

 

i then get two slashes on products that have more than one image.

 

e.g. if a product has one image with 3 letters id_image and one with two letters id_image then the script runs both id_image as 3 letter ids

This has to do with the Case syntax.

Any help?

[RogueWaveLimited]

I am curious why you are exporting images when you said you were trying to create a CSV to update products?

[rogasgr]

i am exporting imageURLs so when my client has 100 new products then he renames the JPG's by the product reference and in the CSV he uses a URL in the server pointing to the server. So then Prestashop imports the jpg's. WHen after a while my client wants to change images on a product he can export all products in a csv and replace one URL, and also change that products Delete existing images (0 = No, 1 = Yes)  

When i finish i will post the query.

[RogueWaveLimited]

I don't understand why you are exporting images from PS for "100 new products" that have not been imported into PS yet. How can the products exist in PS?

[rogasgr]

i want to create a seamless procedure for every change (add new products, change prices for some products, add products to a category). So in every situation the user logs in, exports the products, does whatever and imports according to the template i created. 

 

i am having an issue, i would like to replace the dot "." of the price with the comma "," when exporting from sql manager. Can you help?

[RogueWaveLimited]

So when you export all you get is the name of the image. Then as a separate operation you'll need to actually get a copy of the image to your local computer that you can rename with the product name and then use to import back into PS. But why do that when you are just reimporting the same image.

 

You should be aware that what you are trying to do will work okay for a few products but when you get into 1000's of products the process will start breaking down with timeouts etc. during import.

 

In mysql you can replace text with "REPLACE(str,from_str,to_str)".  So in a select you would

 

select replace(price, '.', ','), ...etc.

[rogasgr]

you are right. If i use the imageURL i will get images reimported. I should then create a second scenario only for new products. 

So i go on with the scenario of

 

a) Make Changes to current products

export products->make changes (price, categories, desc etc but no imageURL)->import according to template 

 

b)Importing new products

have a blank CSV with columns according to import template( with URL of image to import)->import according to NewProductTemplate

 

 

 

Thanks for that RogueWaveLimited

I would like some help with the replace of the dot.

 

in my SELECT p.id_product,p.reference, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.id_category) SEPARATOR ",") as categories, replace(p.price,'.',',') 

 

i get an error Is an unauthorized keyword.

[RogueWaveLimited]

try

 

SELECT p.id_product,p.reference, p.active, pl.name, replace(p.price,'.',','), GROUP_CONCAT(DISTINCT(cl.id_category) SEPARATOR ",") as categories

[rogasgr]

hmmm that did not help either.

[rogasgr]

i stripped off the GROUP_CONCAT but that does not solve it. It seems that SQL Manager does not support replace?

[rogasgr]

just checking this short version and still not working...

SELECT p.id_product,REPLACE(p.price,'.',',')
FROM ps_product p
GROUP BY p.id_product     

hah....but if i put all the REPLACE(p.price,'.',',') inside a parenthesis its ok!

SELECT p.id_product, (REPLACE(p.price,'.',',')) as Price
FROM ps_product p
GROUP BY p.id_product     

[quentic]

Thanks so much for this parenthesis trick! I was wondering why the MYSQL replace function could not be used in the Prestashop SQL manager.

Chris

[Welzfisch]

rogasgr... AWESOME! made my day!