By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,705 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

mysql random field (condition, multiple table, compare)

P: 59

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 22 '08 #1
Share this Question
Share on Google+
1 Reply

P: 59
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 "", 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 "", 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`="", `timestamp`="1209065053"
`accid`='"25G6", `ip`="", `timestamp`="1208971109"
`accid`='"25G6", `ip`="", `timestamp`="1208472890"
`accid`='"25G6", `ip`="", `timestamp`="1207173386"
`accid`='"45Z4", `ip`="", `timestamp`="1207173381"
`accid`='"35K2", `ip`="", `timestamp`="1207173372"
`accid`='"15A1", `ip`="", `timestamp`="1207173363"
`accid`='"25G6", `ip`="", `timestamp`="1207173357"
`accid`='"25G6", `ip`="111.153.442.19", `timestamp`="1208971345"
`accid`='"15A1", `ip`="", `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 25 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.