SQL Query to export just phone numbers from clients on specific date range of creation/registration.

[calichelozano]

Hi, I'm trying to export my prestashop client's phone numbers, which I can on the SQL manager by using this query:

SELECT  phone_mobile FROM  psne_address

 

But, it will export all of them from the creation of the store's date. Now, I need to export just for example the clients who registered between March 1st 2025 - April 29th2025.

 

Can somebody please help me?

 

Thanks!

[El Patron]

To export only the mobile phone numbers of clients who registered between March 1st, 2025 and April 29th, 2025, you need to filter based on the date_add field in the ps_customer table (which tracks customer registration date), and join it with the ps_address table.

Here’s an SQL query you can use in the PrestaShop SQL Manager: change table prefix to match your shops.

SELECT a.phone_mobile
FROM ps_address a
JOIN ps_customer c ON a.id_customer = c.id_customer
WHERE c.date_add BETWEEN '2025-03-01 00:00:00' AND '2025-04-29 23:59:59'
  AND a.phone_mobile != ''

Explanation:

ps_address a and ps_customer c are joined via id_customer.

We filter using c.date_add to only get users registered within the date range.

a.phone_mobile != '' avoids empty phone entries.

[calichelozano]

23 hours ago, El Patron said:

To export only the mobile phone numbers of clients who registered between March 1st, 2025 and April 29th, 2025, you need to filter based on the date_add field in the ps_customer table (which tracks customer registration date), and join it with the ps_address table.

Here’s an SQL query you can use in the PrestaShop SQL Manager: change table prefix to match your shops.

SELECT a.phone_mobile
FROM ps_address a
JOIN ps_customer c ON a.id_customer = c.id_customer
WHERE c.date_add BETWEEN '2025-03-01 00:00:00' AND '2025-04-29 23:59:59'
  AND a.phone_mobile != ''

Explanation:

ps_address a and ps_customer c are joined via id_customer.

We filter using c.date_add to only get users registered within the date range.

a.phone_mobile != '' avoids empty phone entries.

Thank you, Patron. It worked!

[El Patron]

6 minutes ago, calichelozano said:

Thank you, Patron. It worked!

You are very welcome!  Please marked topic as solved.  Have a great weekend.

[sameerkhanar03]

To export Prestashop client phone numbers between specific dates, you can modify your SQL query like this:

SELECT phone_mobile FROM psne_address WHERE date_add BETWEEN '2025-03-01' AND '2025-04-29';

Make sure the psne_address table has a date_add column. If not, you may need to join with the psne_customer table.

This is especially useful if your moving company wants to target recent customers for follow-ups or promotions.

[calichelozano]

Hi Patron, I hope you're having a great week start!I don't know if I can do this request here, but since you're so good and were able to help me eith your code, here it goes: Is there an sql query to export just the users that bought "X" product with their phone numbers and selecting a date range?

 

Thank you very much!

[calichelozano]

2 hours ago, sameerkhanar03 said:

To export Prestashop client phone numbers between specific dates, you can modify your SQL query like this:

SELECT phone_mobile FROM psne_address WHERE date_add BETWEEN '2025-03-01' AND '2025-04-29';

Make sure the psne_address table has a date_add column. If not, you may need to join with the psne_customer table.

This is especially useful if your moving company wants to target recent customers for follow-ups or promotions.

Hi sameerkhanar03, thank you! I also need to just export the users that bought "X" product, do you know how can I specify that in the query you gave me? 

 

Thanks!

[El Patron]

22 hours ago, calichelozano said:

Hi Patron, I hope you're having a great week start!I don't know if I can do this request here, but since you're so good and were able to help me eith your code, here it goes: Is there an sql query to export just the users that bought "X" product with their phone numbers and selecting a date range?

 

Thank you very much!

SELECT DISTINCT
  c.id_customer,
  CONCAT(c.firstname, ' ', c.lastname)      AS customer_name,
  COALESCE(a.phone_mobile, a.phone, '')     AS phone,
  o.id_order,
  o.reference                               AS order_reference,
  o.date_add                                AS order_date
FROM ps_order_detail od
  INNER JOIN ps_orders    o ON od.id_order    = o.id_order
  INNER JOIN ps_customer  c ON o.id_customer  = c.id_customer
  LEFT  JOIN ps_address   a ON o.id_address_delivery = a.id_address
WHERE od.product_id = 123   -- ← replace 123 with your product’s ID
  AND o.date_add BETWEEN '2025-01-01' AND '2025-06-30'  -- ← set your start/end dates
ORDER BY o.date_add DESC;

Above is a template you can paste into your PrestaShop Admin → “SQL Manager” (replace the table prefix ps_ if yours is different). It will pull every customer who bought product “X” in a given date range, along with their phone number (preferring mobile if available).

Edited June 10 by El Patron (see edit history)

[calichelozano]

On 6/10/2025 at 8:15 AM, El Patron said:

SELECT DISTINCT
  c.id_customer,
  CONCAT(c.firstname, ' ', c.lastname)      AS customer_name,
  COALESCE(a.phone_mobile, a.phone, '')     AS phone,
  o.id_order,
  o.reference                               AS order_reference,
  o.date_add                                AS order_date
FROM ps_order_detail od
  INNER JOIN ps_orders    o ON od.id_order    = o.id_order
  INNER JOIN ps_customer  c ON o.id_customer  = c.id_customer
  LEFT  JOIN ps_address   a ON o.id_address_delivery = a.id_address
WHERE od.product_id = 123   -- ← replace 123 with your product’s ID
  AND o.date_add BETWEEN '2025-01-01' AND '2025-06-30'  -- ← set your start/end dates
ORDER BY o.date_add DESC;

Above is a template you can paste into your PrestaShop Admin → “SQL Manager” (replace the table prefix ps_ if yours is different). It will pull every customer who bought product “X” in a given date range, along with their phone number (preferring mobile if available).

Patrón, thank you! As always you've been great help!

[calichelozano]

Hey guys, @El Patron gotta new one for you, it would be awesome if you could help me out:

So I got this:

SELECT a.phone_mobile FROM psne_address a JOIN psne_customer c ON a.id_customer = c.id_customer WHERE c.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59' AND a.phone_mobile != ''

It's working perfect thanks to you, now I need to also add to that, clients who have purchased more than 1,000 USD between the selected dates, and also in the city of Miami.

Can you please help me out?

 

Thanks a million! you rock!

[calichelozano]

3 hours ago, El Patron said:

I see that I am suspension from forum is over...The French really  dislike us USA'ers....it aint my first time and I have to deal  with a lot of Euro Units as well.  @madpugger one that torlled an Oklahom redneck, got what he deserved from me but I am the one suspended, again.   

To be honest I really don't care, I am currently moving 'all' my clients to Shopify, I'm not interested in devleoping,  consulting or being a PrestaShop agency...but still happy to help those trying to remain on ps.

here you go:

SELECT DISTINCT a.phone_mobile
FROM psne_address a
JOIN psne_customer c ON a.id_customer = c.id_customer
JOIN psne_orders o ON c.id_customer = o.id_customer
WHERE c.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
  AND a.phone_mobile != ''
  AND a.city = 'Miami'
GROUP BY a.phone_mobile
HAVING SUM(o.total_paid) > 1000;
 

Hey Patron, sorry to hear about the hassle, and I always appreciate you helping a lot, too bad guys like you are moving away.

Thanks for the code, I think the query is searching for single orders of 1,000 usd or more, but is there anyway of exporting clients who have 1,000 usd adding their orders together between the chosen dates? Example: 3 orders of 333,00 USD together bought between jan 2025 and july 2025.

 

Thanks a million again!

[Knowband Plugins]

9 hours ago, calichelozano said:

single orders of 1,000 usd

Yes correct, because the group by is on the phone_mobile field

Confirm what the exact query you want? Customers of the city Minami who joined between particular days and who have placed orders of more than 1000 within a specific period?

[calichelozano]

10 hours ago, Knowband Plugins said:

Yes correct, because the group by is on the phone_mobile field

Confirm what the exact query you want? Customers of the city Minami who joined between particular days and who have placed orders of more than 1000 within a specific period?

Hi @Knowband Plugins! Thanks for replying! Yes, I need the customers of Miami, with their phone numbers who have placed multiple orders totaling a minimum of $1,000 USD between January 1st 2025 - July 1 2025.

Thank you again!

[Knowband Plugins]

Try this. 

SELECT c.*
FROM psne_address a
JOIN psne_customer c ON a.id_customer = c.id_customer
JOIN psne_orders o ON c.id_customer = o.id_customer
WHERE o.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
  AND a.phone_mobile != ''
  AND a.city = 'Miami'
GROUP BY o.id_customer
HAVING SUM(o.total_paid) > 1000;

 

[calichelozano]

9 hours ago, Knowband Plugins said:

Try this. 

SELECT c.*
FROM psne_address a
JOIN psne_customer c ON a.id_customer = c.id_customer
JOIN psne_orders o ON c.id_customer = o.id_customer
WHERE o.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
  AND a.phone_mobile != ''
  AND a.city = 'Miami'
GROUP BY o.id_customer
HAVING SUM(o.total_paid) > 1000;

 

Hi @Knowband Plugins, it seems to be doing almost all of the  job, but somehow it's not exporting the phone numbers, I see a lot of info on the export, customer id, shop group, gender, lang, risk, company, names, email, etc...but no phone numbers.

Are we missing something?

Thanks again!!! we're almost there!

[kerlin]

Hello, perhaps you don't use the field phone_mobile ? Try this :

SELECT c.*
FROM psne_address a
JOIN psne_customer c ON a.id_customer = c.id_customer
JOIN psne_orders o ON c.id_customer = o.id_customer
WHERE o.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
  AND a.phone_mobile != ''
AND a.phone != ''
  AND a.city = 'Miami'
GROUP BY o.id_customer
HAVING SUM(o.total_paid) > 1000;

 

[kerlin]

Sorry, I hadn’t looked well at the request. The phone number cannot be displayed like this because it is not in the select but only in the condition. Try this (if you only use the phone_mobile field)

SELECT c.*, a.phone_mobile
FROM psne_address a
JOIN psne_customer c ON a.id_customer = c.id_customer
JOIN psne_orders o ON c.id_customer = o.id_customer
WHERE o.date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
  AND a.phone_mobile != ''
  AND a.city = 'Miami'
GROUP BY o.id_customer
HAVING SUM(o.total_paid) > 1000;

 

[Knowband Plugins]

Use the query shared by kerlin to get the phone field as well. 

[calichelozano]

Hey guys! wow, I'm really impressed with all of the help! 

OK, so I'm getting all of the data now, but...it seems there's an error with the sum of all orders, because I went and viewed one of the clients the query exported (supposedly bought 1,000 usd or more), but this client only had bought a total of $342.29 USD in 4 orders. 

So maybe the query is not doing a correct sum?

We're almost there, you guys rock, thank you a million again!

[kerlin]

Pl

SELECT c.*, a.phone_mobile
FROM psne_customer c
JOIN psne_address a ON a.id_customer = c.id_customer
JOIN (
    SELECT id_customer
    FROM psne_orders
    WHERE date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
    GROUP BY id_customer
    HAVING SUM(total_paid) > 1000
) AS high_spenders ON c.id_customer = high_spenders.id_customer
WHERE a.phone_mobile != ''
  AND a.city = 'Miami';

ease try this

 

[calichelozano]

2 hours ago, kerlin said:

Pl

SELECT c.*, a.phone_mobile
FROM psne_customer c
JOIN psne_address a ON a.id_customer = c.id_customer
JOIN (
    SELECT id_customer
    FROM psne_orders
    WHERE date_add BETWEEN '2025-06-13 00:00:00' AND '2025-07-10 23:59:59'
    GROUP BY id_customer
    HAVING SUM(total_paid) > 1000
) AS high_spenders ON c.id_customer = high_spenders.id_customer
WHERE a.phone_mobile != ''
  AND a.city = 'Miami';

ease try this

 

That´s it, perfect! Thank you so much, really...you've been great!

[Hita]

This SQL query fetches only the phone_number field from the clients table where the registration_date is within the specified date range. The BETWEEN keyword is inclusive, so both start and end dates are included in the results.

SELECT phone_number
FROM clients
WHERE registration_date BETWEEN '2025-01-01' AND '2025-12-31';