select only 3 customers per month for last 3 months?

[austenozzy]

Hi there.

My client has asked me to put a feature on the customers page, and I’m a little stumped as to how to go about it.

The client has lots of customers, and plenty of new ones signing on every month. He wants a panel which displays the names, logos & join date for 3 customers per month, for each of the last 3 months (9 total). Also, customers are ranked 1 or 2. He only wants customers with a rank of 1 to be included.

In the customers table has a typical row looks like this:

Id 101
rank 1
accountName abc
accountCountry United States
accountIndustry Retail
logoURL /images/customers/abc.gif
joinDate 2004-07-30

How do I get 3 per month from the last 3 months with name, logo and date?

Thanks,

Oz.

[Dolemite50]

Hi,

Check into the datesub function. The query would look something like:

Last month:
SELECT accountName, accountCountry, logoURL FROM customers WHERE joinDate > date_sub(NOW(), interval 30 day) AND rank=1 LIMIT 3

Two months ago:
SELECT accountName, accountCountry, logoURL FROM customers WHERE (joinDate > date_sub(NOW(), interval 60 day) AND joinDate < date_sub(NOW(), interval 30 day)) AND rank=1 LIMIT 3..
etc...

That's coming from a newbie shooting from the hip but I hope it points u in the right direction.

[Installer]

This will also do it in three separate queries, putting each previous month into a subarray:

for ($m = 0; $m < 3; $m++) {
    $query = "SELECT accountName, logoURL, joinDate FROM my_table" .
             "WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(joinDate, '%Y%m')) = " . $m + 1 .
             " AND rank = 1 ORDER BY RAND() LIMIT 3";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result)) {
        $recent_joins[$m][] = array('accountName' => $row['accountName'],
                                    'logoURL'     => $row['logoURL'],
                                    'joinDate'    => $row['joinDate']);
    }
}

[austenozzy]

Thanks Installer (and thanks too, Dolemite50!),

I'll give that a try. It looks pretty good to me.

I'll post back when I've tried it - it will have to be tomorrow, now.

Thanks again, I really appreciate it.

Oz.