How to export all produtct details, products descriptions, short description, all images as csv file using SQL Manager? What is the sql query to use??

[kathmanduclothing]

[DARKF3D3]

I think the best way to do that it's to use a module like this one:
https://mypresta.eu/modules/administration-tools/export-products-pro.html

[ps8modules]

Hi.

Read the reviews on mypresta.eu and run away quickly. 99.99% won't get support, modules don't work. Just use the search here on the forum.

There are also many SQL solutions here on the forum. Just search.

Edited December 27, 2023 by ps8moduly.cz (see edit history)

[DARKF3D3]

I use some mypresta modules with satisfaction and over the years they have always been updated with compatibility to the latest ps version available.

[ps8modules]

Please, have you read what is being asked for here? Only about SQL query for SQL Manager, not about paid modules.

[DARKF3D3]

Yes I read and I recommended to him the safest solution to do that.
I do not see a query in your answer too other than a criticism to my answer and to @vekia module.
 

[ps8modules]

 

Hi. Just randomly. I have personally solved problems with his modules in new versions of Prestashop for my customers. Compatible Prestashop versions are automatically generated for him in the e-shop. This forum is primarily for free help, unless it's something that can't be adjusted, retrieved, edited in the backoffice. Giving links to paid modules is not a rare advice or a good reference, especially if you are an agency. I personally recommended using the search on the forum, because I myself have given at least five solutions here, including ready-made php scripts. 

How do you want to preach about someone when you don't even have the basic decency to say hello at the beginning of your posts.

[ps8modules]

@kathmanduclothing 

Hi. Can you please provide all the information, if you want to omit some products, if you want to filter the products somehow, if the link to the image should be internal or url rewrite, etc. I will give you the complete script here.

[DARKF3D3]

56 minuti fa, ps8moduly.cz dice:

How do you want to preach about someone when you don't even have the basic decency to say hello at the beginning of your posts.

 

 

Saying hello at the beginning of your posts does not make you any less arrogant.
I am neither an agency nor a developer, so unlike you I am not here on the forum to promote my business, acquire new clients or spreading s**t on other developers.

[ps8modules]

4 minutes ago, DARKF3D3 said:

Saying hello at the beginning of your posts does not make you any less arrogant.
I am neither an agency nor a developer, so unlike you I am not here on the forum to promote my business, acquire new clients or spreading s**t on other developers.

I am here on the forum mainly to help.

I'm one of the few who gives fully working codes here and possibly helps by remotely connecting to users and fixing their Prestashop for free.

You are very wrong.

Can you give the user the fully functional SQL Query he is requesting ?

I'd like to start a discussion here about options for users @kathmanduclothing and helped him FREE !!!

 

[DARKF3D3]

It does not matter what it says on the profile, I'm not an agancy and I'm not here for promotion... Unlike you.
You have written five messages here, and these messages were only critical of me and @vekia.
So now I ask you... Where is your contribution to the question posed by the author of this discussion? Where's the "fully working codes"?
I just see criticism and a big promotional signature below each of your messages.

 

[ps8modules]

Maybe I need more information from the questioner to give them the code.

[DARKF3D3]

And you couldn't have asked that right away instead of polemicising my answer?

[kathmanduclothing]

14 hours ago, ps8moduly.cz said:

@kathmanduclothing 

Hi. Can you please provide all the information, if you want to omit some products, if you want to filter the products somehow, if the link to the image should be internal or url rewrite, etc. I will give you the complete script here.

I want all the information of all the products like product description, title, Short description, price, reference no., quantity, category, all images. I think it will be okie with images being internal. Could you help me with Sql Query for this ?

 

[ps8modules]

Hi,
it should be noted that GROUP_CONCAT does not work in lower versions of Prestashop.
It already works since Prestashop version 8.x.
However, I will give you a sample SQL that can be used in phpMyAdmin (for Prestashop version below 8.0).

If you have a table prefix other than ps_, you need to change it.

In your case, a PHP script would be better, which will process everything for you and give you a finished CSV.

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'ID Product',
p.reference 'Product eference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
pa.id_product_attribute 'ID product attribute',
pa.reference 'Attribute reference',
pa.supplier_reference 'Attribute supplier reference',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT(DISTINCT CONCAT(
        '/img/p/', 
        IF(CHAR_LENGTH(im.id_image) >= 5, CONCAT(SUBSTRING(im.id_image, -5, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 4, CONCAT(SUBSTRING(im.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 3, CONCAT(SUBSTRING(im.id_image, -3, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 2, CONCAT(SUBSTRING(im.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 1, CONCAT(SUBSTRING(im.id_image, -1, 1), '/'), ''),
        im.id_image,
    '.jpg'), ", ") AS 'Images (x,y,z...)'
FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product;

 

Edited December 29, 2023 by ps8moduly.cz (see edit history)

[musicmaster]

On 12/28/2023 at 4:44 PM, ps8moduly.cz said:

Hi,
it should be noted that GROUP_CONCAT does not work in lower versions of Prestashop.
It already works since Prestashop version 8.x.
However, I will give you a sample SQL that can be used in phpMyAdmin (for Prestashop version below 8.0).

If you have a table prefix other than ps_, you need to change it.

In your case, a PHP script would be better, which will process everything for you and give you a finished CSV.

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'ID Product',
p.reference 'Product eference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
pa.id_product_attribute 'ID product attribute',
pa.reference 'Attribute reference',
pa.supplier_reference 'Attribute supplier reference',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT(DISTINCT CONCAT(
        '/img/p/', 
        IF(CHAR_LENGTH(im.id_image) >= 5, CONCAT(SUBSTRING(im.id_image, -5, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 4, CONCAT(SUBSTRING(im.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 3, CONCAT(SUBSTRING(im.id_image, -3, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 2, CONCAT(SUBSTRING(im.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(im.id_image) >= 1, CONCAT(SUBSTRING(im.id_image, -1, 1), '/'), ''),
        im.id_image,
    '.jpg'), ", ") AS 'Images (x,y,z...)'
FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product;

 

That is reasonably complete. But there are more fields (like EAN13). In my opinion it is almost impossibly complex to export all fields. It is better to either export the whole database or to make an analysis of what is needed first.

Two minor comments:

 - GROUP_CONCAT is a SQL command. It is not dependent on the Prestashop version.

 - I would describe "GROUP_CONCAT(DISTINCT(cl.name))" as "Categories"