mysql random field (condition, multiple table, compare)

[cssExp]

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.

[r937]

can you do the query without the random part?

[cssExp]

This is what someone else suggested me to do, however it does not seem to return any entries.

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.

[r937]

... 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'.

i read that a couple of times and i still don't know what it means

maybe you will need to show us several rows of example data which illustrate what you're trying to do


show related rows from each table, and then show what you want the query to produce

[cssExp]

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.

[cssExp]

any suggestions? I have posted example entries as requested.

[r937]

I hope if anyone still understands what i am trying to achieve, ...

sorry, i never did understand it and still don't

[cssExp]

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.

[longneck]

i think this might do what you're looking for:

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'

[cssExp]

I tried what you suggested, but it is not returning any `accid` from `accounts` table when `accounts_log` is completely empty.

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'");

[longneck]

change the JOIN to a LEFT OUTER JOIN

[cssExp]

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.

[longneck]

sorry, don't follow you. can you post an example?

[cssExp]

This is the code

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.

[longneck]

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'

plus your random and limit bits

[cssExp]

I haven't tried it yet but will it solve the 12 hour interval check problem and take only maximum of 3 `accid`, yes?

[cssExp]

I tested the query, again it works exactly same as the previous query, loads `accid`='4gp5' again before 12 hour interval.. ummm.

[longneck]

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?

[cssExp]

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.

[longneck]

ok, try this:

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'

[cssExp]

odd, it doesn't seem to work now. it still does not check 12 hour interval.

[longneck]

doh, i forgot a condition:

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'

[cssExp]

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.

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'

eliminated the closing bracket as there seemed to be additional. umm i hope i am not bothering you too much.

[cssExp]

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?.

[cssExp]

Something like the following, it is not quite working, maybe the syntax is wrong.

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");