SQL CSV Export

[jobybär]

Hallo Leute,

versuche seit geraumer Zeit mit SQL einen CSV Export zu erstellen.

Kenne mich mit SQL leider nur oberflächlich aus. Durch probieren und Google habe ich einen gewissen Umfang geschaft.

Bei den Zeilen mit # Kommentaren würde ich Hilfe gebrauchen.

Ach ja id_language =1 und id_shop =1
 

Select
       p.reference As 'Artikelnummer_SKU',
       p.ean13 As 'EAN', 
       pl.name As 'Produktname', 
      
#  Eigenschaft Farbe   id_feature =9   ps_feture_value_lang   As 'Farbe',

#  Hersteller       ps_manufacturer.name  As 'Marke',

#  Eigenschaft Material   id_feature =10   ps_feture_value_lang  As 'Material',

#  URL des Produkts z. Bsp. https://landmuecke.eu/tiere/storch-sitzend-mit-baby-10-cm-hoch-4058766200147-3646.html  AS 'Deeplink URL',

#  pl.description_shor und  pl.description in eine Spalte zusammenfassen ohne HTML Formatierung     As 'Produktbeschreibung',
       pl.description_short As 'Kurz_Beschreibung', 
       pl.description As 'Produktbeschreibung', 
       Round((p.price *1.19),2) AS 'Preis',
#  Alter Preis wenn der Preis lnk. Tax mit Rabatt ist     (Preis_ink._tax * Ps_specific_price.reduktion)   As  'Alter Preis',
       cu.iso_code      As 'Währung',
       pl.available_now As 'Verfügbarkeit',
       col.value  AS 'Lieferzeit',

#  Versandkosten id_carrier = 2 ; id_range_price = 2 ; id_zone = 9  AS 'Versandkosten',
       
       p.condition As 'Zustand',
 
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi.id_image,
        -- put the image extension
        '.jpg') as "Bild URL",
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi2.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi2.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi2.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 1",  
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi3.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi3.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi3.id_image) >= 4, CONCAT(SUBSTRING(pi3.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi3.id_image) >= 3, CONCAT(SUBSTRING(pi3.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi3.id_image) >= 2, CONCAT(SUBSTRING(pi3.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi3.id_image) >= 1, CONCAT(SUBSTRING(pi3.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi3.id_image,
        -- put the image extension
        '.jpg') "AUX Bild URL 2",   
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi4.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi4.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi4.id_image) >= 4, CONCAT(SUBSTRING(pi4.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi4.id_image) >= 3, CONCAT(SUBSTRING(pi4.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi4.id_image) >= 2, CONCAT(SUBSTRING(pi4.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi4.id_image) >= 1, CONCAT(SUBSTRING(pi4.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi4.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 3",   
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi5.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi5.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi5.id_image) >= 4, CONCAT(SUBSTRING(pi5.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi5.id_image) >= 3, CONCAT(SUBSTRING(pi5.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi5.id_image) >= 2, CONCAT(SUBSTRING(pi5.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi5.id_image) >= 1, CONCAT(SUBSTRING(pi5.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi5.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 4"
       
FROM ps_product p 
       
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
LEFT JOIN ps_image pi3 ON(p.id_product = pi3.id_product and pi3.position = 3)
LEFT JOIN ps_image pi4 ON(p.id_product = pi4.id_product and pi4.position = 4)
LEFT JOIN ps_image pi5 ON(p.id_product = pi5.id_product and pi5.position = 5)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
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_specific_price sp ON(p.id_product = pl.id_product)
INNER JOIN ps_currency cu
INNER JOIN ps_configuration_lang col ON (col.id_configuration = 511)
   
WHERE p.active=1
AND   pl.id_lang = 1
AND   pl.id_shop = 1
GROUP BY p.id_product;

 

Edited December 22, 2017 by jobybär (see edit history)