Help me correct this simple query

PHP
1

Hello, lets imagine i have table ads and table adsClicks. …

table ads holds banner links
table adsClicks holds IP clicks on banner

I want to show user only link banners that user has not ever clicked or did click 24+ hours ago

Here is my code:

$time = time() + 86400; //PHP

SELECT a.*
FROM ads AS a

WHERE (
	(a.id NOT IN(SELECT ad FROM adsClicks WHERE ad=a.id AND ip='00.00.00.00'))
	OR
	(a.id IN(SELECT ad FROM adsClicks WHERE ad=a.id AND ip='00.00.00.00' AND time>'".$time."')
)

AND a.status='Active' AND clicksLeft>0

Query does work but still does give banner links that user has clicked in last 24 hours :frowning:

Whats the issue? Also maybe there are better way to write it (left join - could not figure out correct syntax)?

Thanks in advance!

2

$time = time() + 86400; - is a future. I think you would have…

$time = time() - 86400;

3

Wow thanks. . But it is not an issue i think :frowning: Its just a typo as i m not able to just copy code right now :frowning:

4

Query that retrieve never clicked banners looks like…

SELECT ads.id
FROM ads
LEFT JOIN adsClicks 
ON ads.id = adsClicks.ad
AND adsClicks.ip = :ip
WHERE adsClicks.ad IS NULL

Use it instead of your first subquery.

5

Why would i join that table again?

6

Sorry, that’s could be simpler…

$time = time() - 86400; //PHP

SELECT a.*
FROM ads AS a

WHERE (
	(a.id NOT IN (SELECT ad FROM adsClicks WHERE ip = :ip GROUP BY ad))
	OR
	(a.id IN (SELECT ad FROM adsClicks WHERE ip = :ip AND time > :time GROUP BY ad)
)

AND a.status='Active' AND clicksLeft>0
7

I have not tested yet. … But just to be sure. … What does GROUP BY do in this case? also for check if was clicked? Does it looks at latest click (they can be multiple clicks)?

8

Just because of performance. Or another variant…

$time = time() - 86400; //PHP

SELECT a.*
FROM ads AS a

WHERE (
	(a.id NOT IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip))
	OR
	(a.id IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip AND time > :time)
)

AND a.status='Active' AND clicksLeft>0
9

You sure that greater-than should be a greater than, igor?

(timefield > timestamp means ‘is NEWER than’… you’re looking for ads that are OLDER than 24 hours old…)

For that matter, do you really need that first OR clause, if you invert the logic of the second?

10

Before I get accused of not being helpful…
The original query, fixed:

$time = time() - 86400; //PHP

SELECT a.*
FROM ads AS a

WHERE (
	(a.id NOT IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip))
	OR
	(a.id IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip AND time > :time)
)

AND a.status='Active' AND clicksLeft>0

Why the logic should be inverted, and what that translates as:

The conditional, however, is overlapping as written:
“Has not ever clicked or did click 24 hours ago”
This can be better stated by stating what you’re NOT trying to find:
“Don’t find any ad that has been clicked in the last 24 hours.”
You’ve restated the condition, as a singular clause.

Which means the query can become:

SELECT a.*
FROM   ads AS a
WHERE  a.id NOT IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip AND `time` > :yesterday )
AND    a.status='Active' 
AND    clicksLeft>0

(Though i would heartily recommend a LIMIT clause… maybe an ORDER BY…)

1 Like
11

E-e… Your query finds all banners except of clicked by this user in last 24 hours. As I understood, TS needs some other result.

12

Hi, thanks for such a long answer!

In other words i need query that shows me result from ads that user has never been clicked or clicked 24 or more hours ago (if last click was 24 hours and 1 second ago user should see ad again)

Again i have not tested as i m not on my laptop yet. …

13

I think here could use order by id and limit 1 as i only need to look at last click (if any)

14

But this statement is the SAME as saying “I need query that shows me result from ads that are NOT user has clicked and clicked 24 or less hours ago”

Inverting logic: Stick “NOT” in front of the sentence. Change “or” to “and” (and vice versa), change “more” to "less’ (and vice versa), remove negatives, and you get equivalent statements.

Think of it this way: I have 5 oranges and 3 apples. The apples are 2 red and 1 green.

I could say “I need a query that finds fruits that are green and apples.” or, I could say “I need a query that finds fruits that are not red or oranges”. Both of them would find the same result.

15

Then query of @m_hutley is right for you…

SELECT a.*
FROM   ads AS a
WHERE  a.id NOT IN (SELECT UNIQUE ad FROM adsClicks WHERE ip = :ip AND `time` > :yesterday )
AND    a.status='Active' 
AND    clicksLeft>0
1 Like
16

@igor_g @m_hutley Yep it seems to be working now! Thanks allot!

17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.