During the years I have been working on Prestashop professionally I gathered many useful SQL queries which help you to overcome some unusual tasks.
Here is a collection of useful SQL queries for Prestashop project ready to be fired. This is my personal collection from years spent on developing Prestashop projects. I will do my best to update this list periodically so please do re-check this post from time to time.
If you have any suggestions please write it in a comment of this post below and I will be more than happy to find the answer and then extend this list. I have been working with Prestashop for many years on really complex project with thousands of products and I have to do many unusual tasks for my clients
All SQL query have been tested under Prestashop in version 1.6.1
SQL Query For Exporting Data
export categories and products:
select p.id_product, pl.name as product_name, pl.description_short as product_description_short, pl.description as product_description, concat('http://', ifnull(conf.value,'example.com'), '/img/p/', p.id_product, '-' , pi.id_image, '.jpg') as product_image, p.id_category_default, cl.name as category_name, cl.description as category_description, concat('http://', ifnull(conf.value,'example.com'), '/img/c/', cl.id_category, '.jpg') as category_image from ps_product p left join ps_image pi on p.id_product = pi.id_product left join ps_product_lang pl on p.id_product = pl.id_product left join ps_category_lang cl on p.id_category_default = cl.id_category left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN' group by p.id_product
export just categories:
select cl.id_category, cl2.name as parent_name, cl.name, cl.description, cl.link_rewrite, concat('http://', ifnull(conf.value,'example.com'), '/img/c/', c.id_category, '.jpg') as url_image, cl.id_lang from ps_category c inner join ps_category_lang cl on cl.id_category = c.id_category inner join ps_category_lang cl2 on cl2.id_category = c.id_parent and cl2.id_lang = cl.id_lang left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN' order by cl.id_lang, c.level_depth
export just products:
select cl.id_product, cl.name, cl.description, concat('http://', ifnull(conf.value,'example.com'), '/img/p/', c.id_product, '-' , i.id_image, '.jpg') as url_image, cl.id_lang from ps_product c left join ps_image i on c.id_product = i.id_product left join ps_product_lang cl on cl.id_product = c.id_product left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN' group by cl.id_product order by cl.id_lang
Reset your Prestashop password
UPDATE ps_employee SET passwd = md5('CookieKeyHereNEWPASSWORDHERE') WHERE email ='email@example.com' //example UPDATE ps_employee SET passwd = md5('DLKSDBqSddf7TNqNBTuuefrDkgsfsdaymAoQupDShBQvQZhrQD5zUPASSWORD123') WHERE email ='firstname.lastname@example.org'
you can find your cookie key in the /config/settings.inc.php file