Page 1 of 1

Query problem, please help !

Posted: Fri Jul 23, 2004 12:29 pm
by gabrielp
I have one problem.

Table:

orders
----------------------
id
email_paypal
email_2checkout


I need to know how many times the people have bought from me. I have the table orders with 2 different emails, depending of the paying system. Some people have bought many times, some other just one. How to know how many people bought from us 1, 2 ,3, 4, etc times???

I can't figure out the correct query for this... THANKS A LOT FOR your help on this.

Gabriel.

Posted: Fri Jul 23, 2004 12:36 pm
by feyd

Code: Select all

SELECT COUNT( `id` ) `num` FROM `orders` WHERE `email_paypal` = '$email' OR `email_2checkout` = '$email'
:?:

Posted: Fri Jul 23, 2004 1:44 pm
by gabrielp
Thanks, but no, it's not that... I don't have a variable $email, just a mysql query.

Posted: Fri Jul 23, 2004 1:50 pm
by infolock
well, the $email variable is what you define. So, for whatever customer it is you are looking for (say their email is bob@hotmail.com), you would have something like this before the query :

Code: Select all

$email = 'bob@hotmail.com';
$sql = mysql_query("SELECT COUNT( id ) as num FROM orders WHERE email_paypal = '".$email."' OR email_2checkout = '".$email."'") or die(MySQL_Error());
$row = mysql_fetch_assoc($sql);
echo 'Customer was found '.$row['num'].' times.';

Posted: Fri Jul 23, 2004 6:24 pm
by gabrielp
well... in fact the final echo should be something like this>

There are 32 users that bought 5 times
There are 44 users that bought 4 times
There are 88 users that bought 3 times
There are 112 users that bought 2 times
There are 234 users that bought 1 times

THis will be great to achieve. Remember, I don't have one particular email as a starting variable... just a table complete of some paypal_email and 2checkout_email columns. And many users have bought MANY times at our shop, others just once. What we need is exactly the above stats.

THanks to everyone for the responses!