homepage Welcome to WebmasterWorld Guest from 54.234.228.64
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Nested query and update
x86phre3x




msg:4093115
 2:42 am on Mar 8, 2010 (gmt 0)

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.

 

Dijkgraaf




msg:4099103
 10:02 pm on Mar 16, 2010 (gmt 0)

Can you be a bit more specific how it is not working?
Are you getting a syntax error, or is in not returning the expected results.

If it is a syntax error, the bold items below either need to be quoted, or need to be defined as parameters.

WHERE dealertype=dealer
AND dealershipstatus=first4months

If the syntax is correct, but you aren't getting the expected results, try removing one of the where conditions, and see if the remaining conditions work as expected. Once you narrow down which part of the where clause isn't behaving as expected it makes it easier to diagnose.

syber




msg:4100379
 3:03 pm on Mar 18, 2010 (gmt 0)

(SELECT COUNT(id) FROM users WHERE dealerid=<currentloopuserid> AND


The above code doesn't do anything in the WHERE clause

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved