SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    can you do the query without the random part?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cssExp View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    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.

  6. #6
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any suggestions? I have posted example entries as requested.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cssExp View Post
    I hope if anyone still understands what i am trying to achieve, ...
    sorry, i never did understand it and still don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    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.

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  10. #10
    SitePoint Addict
    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'");

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  12. #12
    SitePoint Addict
    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.

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  14. #14
    SitePoint Addict
    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.

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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'
    plus your random and limit bits
    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

  16. #16
    SitePoint Addict
    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?

  17. #17
    SitePoint Addict
    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.

  18. #18
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  19. #19
    SitePoint Addict
    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.

  20. #20
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  21. #21
    SitePoint Addict
    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.

  22. #22
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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

  23. #23
    SitePoint Addict
    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'
    eliminated the closing bracket as there seemed to be additional. umm i hope i am not bothering you too much.

  24. #24
    SitePoint Addict
    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?.

  25. #25
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •