[TUTORIAL] helpful SQL Queries

[sooroos]

i have just needed some sql queries for my shop,, so i have wrote them myself. in case you need something more, or different related to sql/mysql just ask here, i will try to help as much my time affords it.

 

So, in Advanced Parameters --> SQL Manager --> add new queries

 

 

This one is usefull for export (pay attention to language id)

1. product id

2. product name

3. product URL (assuming you use friendly urls and your route to product is like {category:/}{rewrite}-{id}.html you can also add ean or customize as whatever you need)

4.product img URL (cover image), just replace myshop.com in the query with your shop link (my img ids have 4 or 5 chars, it can be edited to whatever you need)

select
p.`id_product`
,pl.`name`
,concat('http://myshop.com/',cl.`link_rewrite`,'/',pl.`link_rewrite`,'-',p.`id_product`,'.html') as "ProductURL"
,case
    when length(im.`id_image`)=6 then
     concat('http://myshop.com/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://myshop.com/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://myshop.com/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://myshop.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http://myshop.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http://myshop.com/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end as "ImgURL_1"
FROM `ps_product` p
join `ps_product_lang` pl on pl.`id_product`= p.`id_product`
join `ps_category_lang` cl on cl.`id_category`= p.`id_category_default`
join `ps_image` im on im.`id_product`= p.`id_product`
where pl.`id_lang`=1 and cl.`id_lang`=1 and im.`cover`=1 and p.`active`=1

If you have costomized your urls you should check if you have doubled urls or metas... or whatever webmastertool says you should improve. Here is how to check if you have doubled links (pay attention to lang id)

SELECT p.`reference`,p.`id_product`,count(l.`link_rewrite`),l.`link_rewrite` FROM `ps_product_lang` l
join `ps_product` p on p.`id_product` = l.`id_product`
where l.`id_lang`= 1
group by l.`link_rewrite`
having count(l.`link_rewrite`)>1
order by p.`id_product`

Feel free to ask me whatever you need here

Edited September 12, 2014 by sooroos (see edit history)

[nelsonhg]

Hi sooros, im not good at all building sql queries. Would u mind to help me to build one?

I need just from one table the name, identification number, and adress (thats from the orders table) and i need it to join it to a costumer id (thats from another table).

How i can do it?

 

Thanks for your help in advance

[sooroos]

let me see if i understood correctly: you need, customer name, order id, and delivery address?

if so then

SELECT
o.`id_order`
,concat( cs.`firstname`,' ', cs.`lastname`) as "Customer"
,concat(ad.`address1`,', ',ad.`postcode`,', ',ad.`city`,', ',ad.`lastname`,' ',ad.`firstname`) as "Delivery Address"
FROM `ps_orders` o
join `ps_customer` cs on o.`id_customer`=cs.`id_customer`
join `ps_address` ad on o.`id_address_delivery`=ad.`id_address`
order by cs.`firstname`,cs.`lastname`,o.`id_order`

[nelsonhg]

Thanks sooros for ur awesome answer!!!

I need to add a column: id_costumer that is located in the table  'ps_costumer'  

 

I have tried the left join but not success yet.

How i can added to ur solution??

 

Thanks again for ur time

 

Nelson

[sooroos]

SELECT
o.`id_order` as "order id"

,cs.`id_customer` as "customer id"
,concat( cs.`firstname`,' ', cs.`lastname`) as "Customer"
,concat(ad.`address1`,', ',ad.`postcode`,', ',ad.`city`,', ',ad.`lastname`,' ',ad.`firstname`) as "Delivery Address"
FROM `ps_orders` o
join `ps_customer` cs on o.`id_customer`=cs.`id_customer`
join `ps_address` ad on o.`id_address_delivery`=ad.`id_address`
order by cs.`firstname`,cs.`lastname`,o.`id_order`

Edited August 31, 2014 by sooroos (see edit history)

[nelsonhg]

Thanks sooroos that works perfect!!!

 

Just to understand, where i can find the meanings of cs, o, ad and so on? 

I have checked the w3c but when i try something in ps 1.6 just dont work.

 

Thanks again!

 

Nelson

[sooroos]

i bolded for you above where i have used aliases (SQL aliases are used to give a database table, or a column in a table, a temporary name.)

[nelsonhg]

Thanks!!