mysql logic

Expand Post »

here is my situation:

i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period.

now this is my table:

MySQL Syntax (Toggle Plain Text)

orders_id 	customers_id 	customers_name 	customers_company 	customers_street_address 	customers_suburb 	customers_city 	customers_postcode 	customers_state 	customers_country 	customers_telephone 	customers_email_address 	customers_address_format_id 	delivery_name 	delivery_company 	delivery_street_address 	delivery_suburb 	delivery_city 	delivery_postcode 	delivery_state 	delivery_country 	delivery_address_format_id 	billing_name 	billing_company 	billing_street_address 	billing_suburb 	billing_city 	billing_postcode 	billing_state 	billing_country 	billing_address_format_id 	payment_method 	cc_type 	cc_owner 	cc_number 	cc_expires 	last_modified 	date_purchased 	orders_status 	orders_date_finished 	currency 	currency_value

i don't have a sql logic to : select all orders depending on date_purchased field which is in '2008-11-21 21:47:45' format, then calculate total number of orders for each of the customers in that period, depending on customers_id and orders_id i guess, then make a list of the customers that have the number of orders above or equal to 5 and finally randomly select one.

any ideas?

Similar Threads

PHP Logic for Online Test in PHP
How to Connect MySQL from JSP Page in JSP
mysql+jsp in JSP
Regarding Mysql Query in MySQL

johnwayne77

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Sep 2008

Permalink

Dec 25th, 2008

Re: mysql logic

Click to Expand / Collapse Quote originally posted by johnwayne77 ...

... i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period. ... any ideas?

This is pseudo-codeish so you have to RTFM and google to make it work.

You need to use count() and group by. Something like this is kind-of close:

MySQL Syntax (Toggle Plain Text)
SELECT count(id), id FROM table WHERE count(id)>4 AND (date_purchased>='2008-11-24' AND  date_purchased<="2008-12-24") GROUP BY id ORDER BY count(id);
select count(id), id from table where count(id)>4 and (date_purchased>='2008-11-24' and  date_purchased<="2008-12-24") group by id order by count(id);

Last edited by Fest3er; Dec 25th, 2008 at 12:34 am.

Fest3er

Reputation Points: 51

Solved Threads: 35

Posting Whiz in Training

Offline

238 posts
since Aug 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Error 2003: Can't connect to MySQL server on 'localhost' (99)

Next Thread in MySQL Forum Timeline: Database set-up

DaniWeb Message

Cancel Changes

User Name
Password