Need help building advanced query

[mathieumg]

Ok I have two tables:

members

id - int(15)
link - varchar(8)
credits - int(15)
given - int(15)
vacation - tinyint(1)
account_state - tinyint(1)

and

clicks

id - int(15)
click_time - int(15)
by_id - int(15)
for_id - int(15)
ip_addr - varchar(70)


This is a link trading site (each member's link is contained in link field of the members table)

What I want the query to do is list everybody (with their id) you can click.

Rules

-You cannot click the same link two times in 24 hours with the same IP

That means you can click someone 2 times or more in the same 24 hours if your IP changes, or you can wait 24 hours for the link to re-appear in the list if your IP don't changes.

-User must have credits (credits > 0)

-User's account must be active (account_state = 2)

-User must not be on vacation mode (vacation = 0)


Each time a click is done, a log entry is put in the clicks table,

click_time : Timestamp when the click was done
by_id : User who did the click's id
for_id : User who was clicked's id
ip_addr : User who did the click's IP


It must also works if the user was not clicked yet, because he just registered.

Here is my query with few PHP lines before it, I think I'm on a good way but I'm missing something, because it isn't working


//Query to get clickable users
$click_time=time()-86400;
$click_ip=$_SERVER["REMOTE_ADDR"];
$clicklist=new mysql();
$clicklist->query("SELECT members.id FROM members LEFT JOIN clicks ON clicks.for_id=members.id WHERE credits>'0' AND account_state='2' AND vacation='0' AND (clicks.click_time<'".$click_time."' OR clicks.click_time IS NULL) AND (clicks.ip_addr='".$click_ip."' OR clicks.ip_addr IS NULL) ORDER BY members.credits DESC, members.given DESC");

[mitchind]

What do you mean by "not working"?

Do you get a syntax error on the query?

Does the query return the wrong information?

[mathieumg]

the query return the wrong information

If another member clicked the list and each click got logged, then the list return no Ids for other members who would like to click.

So, only one member can click the list, thats not really what I want :/

[RTH10260]

the query return the wrong information

If another member clicked the list and each click got logged, then the list return no Ids for other members who would like to click.

So, only one member can click the list, thats not really what I want :/
$clicklist=new mysql();aside of the sql question, I was wondering what level of PHP 4/5 you are using for OO programming, and what mysql class you are referencing.SELECT members.id
FROM members
LEFT JOIN clicks
ON clicks.for_id=members.id
WHERE credits>'0'
AND account_state='2'
AND vacation='0'
AND (clicks.click_time<'".$click_time."' OR clicks.click_time IS NULL)
AND (clicks.ip_addr='".$click_ip."' OR clicks.ip_addr IS NULL)
ORDER BY members.credits DESC, members.given DESC;- I read this code as saying, don't consider any clicks of today (not your first rule saying at most one),
- and I read your code as to select only members who have been using the same IP address as the current one (and I believe the NULL on IP address makes no sense cause you should have one for every click).

[mathieumg]

I'm using PHP4

And I know my query is wrong because I am very confused with the LEFT JOIN statement, because its the first time I "try" to use it, because in that situation I really need to work with both tables (members and clicks).

This is my class function:


//Function to execute the query
function query($query) {
$this->result = mysql_query($query);

return $this->result;
}

[riskynil]

Try playing around with this SQL:

SELECT members.id
FROM members LEFT JOIN clicks ON clicks.for_id=members.id AND clicks.click_time>DATE_SUB(NOW(), INTERVAL 24 HOUR) AND clicks.click_time.ip_addr='".$click_ip."'
WHERE clicks.id IS NULL AND credits>'0' AND account_state='2' AND vacation='0'
ORDER BY members.credits DESC, members.given DESC

I changed it so the SQL statement itself will calculate the 24 hour period--seems kind of disjointed to me to have the logic of the query seperated into PHP and SQL when it wasn't necessary, but it's a largely personal preference.

The logic for what you are trying to do seemed easier to figure out if I did the LEFT JOIN *looking* for any clicks that would disqualify it from the listing, then in the WHERE clause selecting all of the clicks that returned NULL (that is, those members without clicks that would disqualify it from the listing).

I'm not *entirely* sure I understood what you're trying to do, but if I understood you properly, I think this query would work so give it a whirl and see what happens. =)

-- Ryan

[mathieumg]

SELECT members.id
FROM members LEFT JOIN clicks ON clicks.for_id=members.id AND clicks.click_time > ".$click_time." AND clicks.ip_addr='".$click_ip."'
WHERE clicks.id IS NULL AND credits>'0' AND account_state='2' AND vacation='0'
ORDER BY members.credits DESC, members.given DESC


This is the winning combination!!!! :D

THANK YOU !!!! :D

If I encounter another problem further, I'll come post here.

[riskynil]

This is the winning combination!!!!
THANK YOU !!!!

Glad I could help. =) It was a nice mental puzzle you had given us--definitely qualifies as an advanced query in my book!

-- Ryan