Dear all,

This is my first post and hoping somebody could really help me on this.

I have a database with users and dealers table which contains certain columns as below:

Users

id
username
dealerid
registration_timestamp

Dealers

id
username
registration_timestamp
dealertype
dealerstatus
dealerachievement

I am setting a php cronjob. The system is to check (every hour) for all dealer with dealer type "dealer" and where user with it's id and user registration date between dealer registration date and 4 months later.

Below is just my draft for the MySQL query which does not work. Could somebody help me to write it?

SELECT * FROM dealers 
 WHERE dealertype=dealer 
 AND dealershipstatus=first4months 
 AND 
(SELECT COUNT(id) FROM users WHERE dealerid=<currentloopuserid> AND registration_timestamp BEtWEEN 
(SELECT registration_timestamp FROM dealers WHERE dealerid=<currentloopdealerid>) 
AND (SELECT registration_timestamp FROM dealers WHERE dealerid=<currentloopdealerid>) + 10368000) > 3000

If the found, update the dealerachievement value with "achieved".

Could somebody help me on this? Thanks.