[NOOB] [PS 1.7] export customer with number of orders

Discussion in 'MySQL' started by rushlloyd, Jan 17, 2019.

0
  1. #1
    Hello Every one
    so yes, im a total noob,
    i have access to my DB thru SSH and the database tool in prestashop 1.7.5

    I would like to export and be able to filter my customers and import again the clean list.
    the idea is to remove all customers that did not order at least twice.
    i have no idea how to export with those parameters. anyone will be kind enought to point me in the right direction ?
     
    Solved! View solution.
    rushlloyd, Jan 17, 2019 IP
  2. #2
    Why not just run a query to delete all customers where the order count is less than 2?

    and surely you have phpMyAdmin supplied by your host?
     
    sarahk, Jan 17, 2019 IP
  3. rushlloyd

    rushlloyd Member

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    Hi, thanks for the fast answer,
    i dont have phpmyadmin all is done via ssh, with is not a big issue for me till now.

    Why not just run a query to delete all customers where the order count is less than 2?
    >> this sound like a great idea ! how do i do that ?
     
    rushlloyd, Jan 17, 2019 IP
  4. rushlloyd

    rushlloyd Member

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #4
    if anyone can help me our writting the right query ?
    @sarahk

    Thx
     
    rushlloyd, Jan 18, 2019 IP
  5. rushlloyd

    rushlloyd Member

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #5
    Hi ,

    i still need help with this please @sarahk

    so far i got

    select id_customer, count( * ) from ps_orders group by id_customer order by count( * ) DESC ;


    but that is giving me all order attempts,
    i want to do your suggestion can you help with that ?
     
    rushlloyd, Jan 21, 2019 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,759
    Likes Received:
    4,519
    Best Answers:
    123
    Trophy Points:
    665
    #6
    I've been thinking... rather than doing one fancy query which could mess up if there was a typo do it in steps.

    1. select `id_customer`, count(`id_customer`) as `order_count`
      from `ps_orders`
      group by `id_customer`
      order by `order_count` DESC
      having `order_count` < 2 ;
    2. chuck the results into a spreadsheet
    3. create a column with a formula like this:
      ="delete from `ps_customers` where `id` = " + A1 + ";"
    4. paste those commands back into the database
    all much easier if you can use phpMyAdmin, SqlYog, TeamSQL or something similar. A database without a decent interface is like going back to the '90s
     
    sarahk, Jan 21, 2019 IP
  7. rushlloyd

    rushlloyd Member

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #7
    awesome @sarahk

    im going to do this in my local with sql server!

    i keep you posted.
     
    rushlloyd, Jan 21, 2019 IP