mysql random field (condition, multiple table, compare)

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • cssExp
    New Member
    • Jun 2007
    • 59

    #1

    mysql random field (condition, multiple table, compare)

    Hi,

    Assume i have two tables. "accounts" and "accounts_l ogs"

    "accounts" (accid, type)
    "accounts_l og" (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_l og".

    Lests say a one of the accid chose is A1, then take users IP and current time - time(). now compare A1 exists in "accounts_l og" 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_l og".

    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.
  • cssExp
    New Member
    • Jun 2007
    • 59

    #2
    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_addre ss' 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.5 6.2", `timestamp`="12 09065053"
    `accid`='"25G6" , `ip`="165.172.5 1.10", `timestamp`="12 08971109"
    `accid`='"25G6" , `ip`="123.168.5 6.2", `timestamp`="12 08472890"
    `accid`='"25G6" , `ip`="123.168.5 6.2", `timestamp`="12 07173386"
    `accid`='"45Z4" , `ip`="123.168.5 6.2", `timestamp`="12 07173381"
    `accid`='"35K2" , `ip`="123.168.5 6.2", `timestamp`="12 07173372"
    `accid`='"15A1" , `ip`="123.168.5 6.2", `timestamp`="12 07173363"
    `accid`='"25G6" , `ip`="123.168.5 6.2", `timestamp`="12 07173357"
    `accid`='"25G6" , `ip`="111.153.4 42.19", `timestamp`="12 08971345"
    `accid`='"15A1" , `ip`="155.167.0 .1", `timestamp`="12 08971109"
    `accid`='"25G6" , `ip`="202.152.5 05.3", `timestamp`="12 08971003"

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

    Comment

    Working...