SQL Manager to export mobile phone

[plaxton]

Can someone help me create a SQLquery for a customer's phone number?

I have a query  list of order data, I only need a phone number.

 

The above query checks the PS_ORDER table, but the phone number is in the address in PS_ORDER_DETAILES. Additionally, each client has several addresses. I would like it to select the Id.ADDRESS with the highest number. What should an SQL query look like when it needs a phone number?

Presta: 1.6.1

best regards

[Nickz]

It should be shown in your BO under user? Or didn't he give one?

[Knowband Plugins]

a.phone will return the phone number of the shipping address used in the Order. 

Your for latest address, it would be more complicated. 

 

[plaxton]

No, a.`phone` it does not work.

" Attribute "a.phone" does not exist in table "`ps_orders`". "

Phone is in tabel PS_ADDRESS in each id address.

[Knowband Plugins]

OKay. Use address.phone.

 I can see the ps_address is alisa as address in your query then it should be "address.phone"

 

[plaxton]

This `address.phone` does not work. Again , there is not phone in PS_ORDER table . It is Prestashop 1.6.

So I have to write command to get phone from PS_ADDRESS be id-address with number is in PS_ORDER. This number id-address I have.

In this case, I have add lines inquiry "LEFT JOIN" ,  from "PS_ADDRESS " and then based on "a.id_address_delivery" get a phone. Or something like that.

I understand MySQL query but I dont know this language  and I can't formulate the query.

[Mediacom87]

Hi,

SELECT SQL_CALC_FOUND_ROWS
    a.id_order,
    a.reference,
    a.total_paid_tax_incl,
    a.payment,
    a.date_add AS date_add,
    a.pdf_printed AS pdf_printed,
    c.firstname,
    a.id_customer,
    c.email,
    address.phone,
    address.phone_mobile,
    a.id_address_delivery,
    CONCAT(c.firstname, ' ', c.lastname) AS customer,
    osl.name AS osname,
    os.color,
    IF(
        (SELECT so.id_order FROM ps_orders so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0,
        0,
        1
    ) AS new,
    country_lang.name AS cname,
    IF(a.valid = 1, 0) badge_success,
    (
        SELECT SUM(total_paid_real / conversion_rate)
        FROM ps_orders o
        WHERE o.id_customer = a.id_customer
          AND o.id_shop IN (1)
          AND o.valid = 1
    ) AS total_spent

FROM ps_orders a

LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer)
LEFT JOIN ps_address address ON address.id_address = a.id_address_delivery
LEFT JOIN ps_country country ON address.id_country = country.id_country
LEFT JOIN ps_country_lang country_lang ON (country_lang.id_country = country.id_country AND country_lang.id_lang = 1)
LEFT JOIN ps_order_state os ON (os.id_order_state = a.current_state)
LEFT JOIN ps_order_state_lang osl ON (osl.id_order_state = os.id_order_state AND osl.id_lang = 1)

WHERE 1
  AND a.date_add >= '2025-01-01 00:00:00'
  AND a.date_add <= '2025-03-10 23:59:59'

ORDER BY a.id_order DESC;

 

[Mediacom87]

and an optimized version for more precise data analysis :

SELECT SQL_CALC_FOUND_ROWS
    a.id_order,
    a.reference,
    a.date_add,
    a.payment,
    a.total_paid_tax_incl,
    a.total_shipping_tax_incl,
    a.conversion_rate,
    a.pdf_printed,
    a.valid AS is_valid,
    
    c.id_customer,
    CONCAT(c.firstname, ' ', c.lastname) AS customer_name,
    c.email,
    
    address.company AS delivery_company,
    address.address1 AS delivery_address1,
    address.address2 AS delivery_address2,
    address.postcode AS delivery_postcode,
    address.city AS delivery_city,
    country_lang.name AS delivery_country,
    address.phone,
    address.phone_mobile,
    
    osl.name AS order_status,
    os.color AS order_status_color,

    IF(
        (SELECT 1 FROM ps_orders so 
         WHERE so.id_customer = a.id_customer 
           AND so.id_order < a.id_order 
         LIMIT 1),
        0, 1
    ) AS is_new_customer,

    (
        SELECT SUM(o.total_paid_real / o.conversion_rate)
        FROM ps_orders o
        WHERE o.id_customer = a.id_customer
          AND o.valid = 1
          AND o.id_shop IN (1)
    ) AS total_spent_by_customer

FROM ps_orders a

LEFT JOIN ps_customer c ON c.id_customer = a.id_customer
LEFT JOIN ps_address address ON address.id_address = a.id_address_delivery
LEFT JOIN ps_country country ON country.id_country = address.id_country
LEFT JOIN ps_country_lang country_lang ON (
    country_lang.id_country = country.id_country 
    AND country_lang.id_lang = 1
)
LEFT JOIN ps_order_state os ON os.id_order_state = a.current_state
LEFT JOIN ps_order_state_lang osl ON (
    osl.id_order_state = os.id_order_state 
    AND osl.id_lang = 1
)

WHERE a.date_add BETWEEN '2025-01-01 00:00:00' AND '2025-03-10 23:59:59'

ORDER BY a.id_order DESC;

 

[plaxton]

Thanks wery much. Now it working. 

Thats I needed. 🤩

 

[SOLVED]

 

Edited March 23 by plaxton
add solved (see edit history)