SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 25
-
Apr 23, 2008, 22:47 #1
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
mysql random field (condition, multiple table, compare)
Hi,
Assume i have two tables. "accounts" and "accounts_logs"
"accounts" (accid, type)
"accounts_log" (accid, ip, timestamp)
Here i want to choose a radom 3 accids from "accounts" if available, but each of them should be compared with "accounts_log".
Lests say a one of the accid chose is A1, then take users IP and current time - time(). now compare A1 exists in "accounts_log" with the ip, this can exist multiple times but pick one with latest timestamp. Compare the difference in hours in the timestamp and current time, if its less than 12 hours then choose a different accid from "accounts" which goes through the same check, and while taking new accid from "accounts" or if it is greater than 12 hours continue to be A1, this is same even if there is no entries of A1 in "accounts_log".
I hope my explanation was enough. I been trying for weeks and yet can't seem to find a solution.
I'll summarize the steps
1) have to take 3 random `accid` from `accounts` with `type`='normal'
2) while taking, check each `accid` from `accounts` exists in `accid` feild of table `account_logs` with number '123' in `ip` field of `accounts_log`.
3) if exists compare the latest `timespamp` field of `accounts_log` associated with `ip`='123' to current time to see if it is greater than 12 hours.
4) return available entries.
-
Apr 24, 2008, 04:11 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
can you do the query without the random part?
-
Apr 24, 2008, 06:58 #3
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
This is what someone else suggested me to do, however it does not seem to return any entries.
Code MySQL:mysql_query("SELECT * FROM `accounts` AS a, `accounts_log` AS b WHERE a.`type`='normal' AND a.`accid` = b.`accid` AND DATE_ADD(timestamp, interval 12 hour) <= NOW() ORDER BY rand() LIMIT 3");
I want something like above but should take `accid` from `accounts` table even if it doesnot exist in `accounts_log` table and compare `timestamp` of `accounts_log` only if the selected `accid` is already exists in `accounts_log` where `ip` of `accounts_log` table as '123'.
Here '123' ip is just an example.
-
Apr 24, 2008, 12:26 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Apr 24, 2008, 13:43 #5
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Alright I will explain and illustrate things clearly as possible. I have two tables, `accounts` and `accounts_log`.
`accounts` has fields `accid` and `type`.
`accounts_logs` has fields `accid`, `ip` and `timestamp`.
1) Prior to selection i get user's ip address through php into $ip_address variable.
2) I want to get 3 available `accid` from `accounts` where feild `type`='normal'.
3) while selecting the `accid` values, I want to check it if it exists in `accounts_log` where `ip`='$ip_address' and latest `timestamp`. Candider `accid` is "25G6" and `ip` is "123.168.56.2", it can have multiple entries in `accounts_log` table but with different `timestamp`. That is why I want to take the latest `timestamp` and see if there is an intervel of 12 hours from it was last added in `accounts_log` table, if yes then "25G6" is the chosen `accid` from `accounts` table but if it has not been over 12 hours then choose a different `accid` if available.
4) The above comparison with `timestamp` etc. should only takes place if the `accid` exists in `accounts_logs` table and with the ip "123.168.56.2", otherwise it should just choose `accid` if available from `accounts` table.
Example `accounts` table entries.
`accid`='"25G6", `type`="normal"
`accid`='"55D6", `type`="special"
`accid`='"15A1", `type`="normal"
`accid`='"45Z4", `type`="normal"
`accid`='"35K2", `type`="normal"
Example `accounts_log` table entries.
`accid`='"25G6", `ip`="123.168.56.2", `timestamp`="1209065053"
`accid`='"25G6", `ip`="165.172.51.10", `timestamp`="1208971109"
`accid`='"25G6", `ip`="123.168.56.2", `timestamp`="1208472890"
`accid`='"25G6", `ip`="123.168.56.2", `timestamp`="1207173386"
`accid`='"45Z4", `ip`="123.168.56.2", `timestamp`="1207173381"
`accid`='"35K2", `ip`="123.168.56.2", `timestamp`="1207173372"
`accid`='"15A1", `ip`="123.168.56.2", `timestamp`="1207173363"
`accid`='"25G6", `ip`="123.168.56.2", `timestamp`="1207173357"
`accid`='"25G6", `ip`="111.153.442.19", `timestamp`="1208971345"
`accid`='"15A1", `ip`="155.167.0.1", `timestamp`="1208971109"
`accid`='"25G6", `ip`="202.152.505.3", `timestamp`="1208971003"
(the ip adress are fake for illustration and may not belong to anyone.)
I hope if anyone still understands what i am trying to achieve, you can consider this as unique visitor check in each 12 hour or so. If i still am not being clear - I am at a loss as i cannot explain any better than this.
-
Apr 26, 2008, 03:58 #6
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
any suggestions? I have posted example entries as requested.
-
Apr 26, 2008, 04:02 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Apr 26, 2008, 04:06 #8
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I am really surprised if anyone still doesn't understand it. ummm... I think i could make a flash presentation showing what i want but if it is not possible then it will be a wasted effort.
-
Apr 26, 2008, 20:53 #9
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
i think this might do what you're looking for:
Code:select a.accid from accounts a join accounts_log al on a.accid = al.accid and al.ip = '$ip_address' and al.timestamp = (select max(timestamp) from accounts_log where accid = a.accid) and not exists (select 1 from accounts_log where timestamp < al.timestamp and timestamp > (al.timestamp - 61200)) where a.type = 'normal'
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 27, 2008, 13:29 #10
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I tried what you suggested, but it is not returning any `accid` from `accounts` table when `accounts_log` is completely empty.
Code MySQL:mysql_query("SELECT a.`accid` FROM `accounts` a JOIN `accounts_log` al ON a.`accid` = al.`accid` and al.`ip` = '$ip_address' and al.`timestamp` = (select max(`timestamp`) FROM `accounts_log` WHERE `accid` = a.`accid`) and not exists (SELECT 1 FROM `accounts_log` WHERE `timestamp` < al.`timestamp` and `timestamp` > (al.`timestamp` - 61200)) WHERE a.`type` = 'normal'");
-
Apr 27, 2008, 17:26 #11
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
change the JOIN to a LEFT OUTER JOIN
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 28, 2008, 02:17 #12
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Almost everything is working perfectly except the timestamp hour interval check, its not choosing entries that has more than 'specified' hour interval in `accounts_log`. its choosing entries with even few seconds intervals.
Last edited by cssExp; Apr 28, 2008 at 05:36.
-
Apr 28, 2008, 07:36 #13
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
sorry, don't follow you. can you post an example?
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 28, 2008, 11:27 #14
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
This is the code
Code MySQL:mysql_query("SELECT a.`accid` FROM `accounts` a LEFT OUTER JOIN `accounts_log` al ON a.`accid` = al.`accid` and al.`ip` = '$ip_address' and al.`timestamp` = (select max(`timestamp`) FROM `accounts_log` WHERE `accid` = a.`accid`) AND NOT EXISTS (SELECT 1 FROM `accounts_log` WHERE `timestamp` < al.`timestamp` and `timestamp` > (al.`timestamp` - 61200)) WHERE a.`type` = 'normal' ORDER BY rand() LIMIT 3");
For test there is only one entry `accid`='4gp5' with `type`='normal' in `accounts` table.
Each time the page loads, after taking the `accid` it adds details to `accounts_log` table. Next time when page loads again the same `accid` should not be selected if 12 hours have not passed.
Now it loads the `accid`, then adds details to `accounts_log` table through other tasks and then i reload page, it selects '4gp5' again even if 12 hours have not passed, since '4gp5' is the only row in `accounts` table, it should not choose any `accid`. Hope I explained clearly enough.
-
Apr 28, 2008, 11:39 #15
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Code:select a.accid from accounts a left outer join accounts_log al on a.accid = al.accid and al.ip = '$ip_address' and al.timestamp = (select max(timestamp) from accounts_log where accid = a.accid) and (select count(*) from accounts_log where timestamp < al.timestamp and timestamp > (al.timestamp - 61200)) > 0 where a.type = 'normal'
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 28, 2008, 13:11 #16
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I haven't tried it yet but will it solve the 12 hour interval check problem and take only maximum of 3 `accid`, yes?
-
Apr 28, 2008, 14:03 #17
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I tested the query, again it works exactly same as the previous query, loads `accid`='4gp5' again before 12 hour interval.. ummm.
-
Apr 28, 2008, 20:26 #18
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
ah, i see what the problem is. you'll need to add another subquery to eliminate rows where there is a log entry in less than 12 hours. can you do that based on the query i've already provided?
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 28, 2008, 22:40 #19
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm good at scripting in general but I'm a novice when it comes to mysql querys like that, i'm not sure how i should write the subquery.
Last edited by cssExp; Apr 29, 2008 at 03:37.
-
Apr 29, 2008, 14:33 #20
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
ok, try this:
Code:select a.accid from accounts a left outer join accounts_log al on a.accid = al.accid and al.ip = '$ip_address' and al.timestamp = (select max(timestamp) from accounts_log where accid = a.accid) and exists (select 1 from account_log where `timestatmp` <= (al.timestamp - 61200)) and not exists (select 1 from account_log where `timestamp` > (al.timestamp - 61200)) where a.type = 'normal'
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 30, 2008, 00:51 #21
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
odd, it doesn't seem to work now. it still does not check 12 hour interval.
Last edited by cssExp; Apr 30, 2008 at 01:51.
-
Apr 30, 2008, 05:45 #22
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
doh, i forgot a condition:
Code:select a.accid from accounts a left outer join accounts_log al on a.accid = al.accid and al.ip = '$ip_address' and al.timestamp = (select max(timestamp) from accounts_log where accid = a.accid) and exists (select 1 from account_log where `timestatmp` <= (al.timestamp - 61200)) and not exists (select 1 from account_log where `timestamp` > (al.timestamp - 61200)) and `timestamp` < al.timestamp) where a.type = 'normal'
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Apr 30, 2008, 06:37 #23
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
problem, it still does not check 12 hour, when one `accid` is chosen and details added in `accounts_log`, when i reload the page, it still chooses the same `accid` (as mentioned before, for testing, there is only 1 `accid` in `accounts` table.
I changed the code to the following and still same results.
Code:select a.accid from accounts a left outer join accounts_log al on a.accid = al.accid and al.ip = '$ip_address' and al.timestamp = (select max(timestamp) from accounts_log where accid = a.accid) and exists (select 1 from account_log where `timestatmp` <= (al.timestamp - 61200)) and not exists (select 1 from account_log where `timestamp` > (al.timestamp - 61200)) and `timestamp` < al.timestamp) where a.type = 'normal'
-
May 1, 2008, 23:32 #24
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I have an idea, is it possible to do like the following for the timestamp interval check? how?
1) I get the current timestamp with php time() into $timestamp like i got $ip_address.
2) compare max(`timestamp`) in the query with $timestamp to see if 24 hours have passed (I decided to make it a one day for better uniqueness).
Would this be more viable?.
-
May 2, 2008, 13:41 #25
- Join Date
- Jun 2007
- Posts
- 358
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Something like the following, it is not quite working, maybe the syntax is wrong.
Code MySQL:mysql_query("SELECT a.`accid` FROM `accounts` AS a, `accounts_log` AS al WHERE a.`type` = 'normal' AND (IF (SELECT count(*) FROM `accounts_log` WHERE `accid` = a.`accid` AND `ip` = '$ip_address') > 0 THEN (hour('$timestamp') - hour(max(al.`time`))) > 24) ORDER BY rand() LIMIT 3");
Bookmarks