SQL export txt file

[Adria Laxson]

Ok so I have this perfect code below (for my store) to export orders on a status basis. I've been trying to output to a txt file & always get some sort of error message about my Maria DB server. I feel I'm so close to the answer but can't seem to do it! I know I can convert the csv export to txt. I was just wanting to skip extra steps. An added bonus would be for it to export with the name "Orders.txt"

*I include first name only because I import customers with first & last name together.

 

SELECT
    d.id_order,
    os.name AS STATUS,
    g.id_customer,
    g.firstname AS customer,
    ad.company,
    ad.address1,
    ad.address2,
    ad.postcode,
    ad.city,
    ste.name AS state,
    cl.name AS country,
    ad.other,
    ad.phone,
    ad.phone_mobile,
    d.product_name,
    d.product_upc,
    d.product_reference,
    d.product_price,
    d.product_quantity,
    d.product_weight,
    carrier.name AS carrier,
    gl.name AS ship_code,
    o.payment,
    o.date_upd
FROM
    cicciabe_order_detail d
LEFT JOIN
    cicciabe_orders o
ON
    (d.id_order = o.id_order)
LEFT JOIN
    cicciabe_customer g
ON
    (o.id_customer = g.id_customer)
LEFT JOIN
    cicciabe_address ad
ON
    (
        o.id_address_delivery = ad.id_address
    )
LEFT JOIN
    cicciabe_state ste
ON
    (ste.id_state = ad.id_state)
LEFT JOIN
    cicciabe_country_lang cl
ON
    (cl.id_country = ad.id_country)
LEFT JOIN
    cicciabe_carrier carrier
ON
    (
        carrier.id_carrier = o.id_carrier
    )
LEFT JOIN
    cicciabe_group_lang gl
ON
    (
        g.id_default_group = gl.id_group
    )
LEFT JOIN
    cicciabe_order_state_lang os
ON
    (
        o.current_state = os.id_order_state
    )
WHERE
    o.current_state = 10
ORDER BY
    o.date_add
DESC
 

[Scully]

get some sort of error message about my Maria DB server

 

Some sort of error isn't quite precise. However the sql looks good.

 

The export would look like this:

    .....
    gl.name AS ship_code,
    o.payment,
    o.date_upd
INTO OUTFILE '/tmp/orders.csv'        
FROM
    pre_order_detail d

The database user needed special database privileges and file write permission on the selected filesystem.

[Adria Laxson]

Thank you so much. Let me try that!

[Adria Laxson]

Yes, I am having a permissions problem. So essentially I just need to change chmod permissions or does it have something to do with my server? It does look like my chmod are right but I'm still looking.

[Scully]

Error messages always help to indentify the cause. You need two kind of permission: the select into outfile may require some permission on the database itself. Then of course the system user which runs the database must have write access to the path where you want to store the file. /tmp is a place where you usually can test such things.

[Adria Laxson]

Thank you  very much for the help