Query problem, please help !

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gabrielp
Forum Newbie
Posts: 5
Joined: Fri Jul 23, 2004 12:29 pm

Query problem, please help !

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT COUNT( `id` ) `num` FROM `orders` WHERE `email_paypal` = '$email' OR `email_2checkout` = '$email'
:?:
gabrielp
Forum Newbie
Posts: 5
Joined: Fri Jul 23, 2004 12:29 pm

Post by gabrielp »

Thanks, but no, it's not that... I don't have a variable $email, just a mysql query.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.';
gabrielp
Forum Newbie
Posts: 5
Joined: Fri Jul 23, 2004 12:29 pm

Post 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!
Post Reply