36 users online (1 members and 35 guests)  


  Results 1 to 4 of 4

Related

  1. Count and rows code    Forum: PHP Forum
    Replies: 0
  2. Replies: 0
  3. how to count clicks/guesses?    Forum: Javascript Forum
    Replies: 3
  4. How can count visits?    Forum: PHP Forum
    Replies: 1
  1. #1
    ramis55's Avatar
    New User

    Status
    Offline
    Join Date
    Jan 2005
    Posts
    8

    Can’t count how users and pages in the tables

    Hello
    I’m having two tables ap_user and read_page. I want write query that returns how user is logged from ip address and how pages read from ip addres. In table read_page one record is one page readied.
    ap_user-------------------------
    | user_id | ip | device_model |
    | 1 | 195.14.172.97 | Nokia3510i |
    | 2 | 195.14.172.97 | Nokia7650 |
    | 3 | 194.176.32.169 | SIE-SX1 |
    | 4 | 195.14.172.97 | Nokia7650 |

    read_page------------------
    | date | user_id | page_id |
    | 2005-01-07 | 1 | 1 |
    | 2005-01-07 | 1 | 8 |
    | 2005-01-07 | 2 | 3 |
    | 2005-01-07 | 1 | 4 |
    | 2005-01-08 | 3 | 9 |
    | 2005-01-08 | 1 | 5 |
    When using the second query get how users are logged from ip. SELECT ap_user.ip, COUNT(ap_user.user_id), FROM ap_user GROUP BY ap_user.ip
    But when execute second query returns only good data how pages readied from ip and bad data how users are logged from ip.
    SELECT ap_user.ip, COUNT(ap_user.user_id), COUNT(read_page.page_id) FROM ap_user LEFT JOIN read_page USING(user_id) GROUP BY ap_user.ip

    Maybe can write good query, that can get how user is logged from ip address and how pages read from ip address? Please!

  2. #2
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Hi, welcome to the forums.

    The question is phrased slightly confusingly, I am guessing english is not your first language. Thats fine, If I have misunderstood you can tell me.

    At a guess, you want to select the user from the ap_user table by their user_id, and also select which articles they are reading from the read_page table?

    You can do this in two queries, which is messy. Or you can use something in mySQL called JOIN which lets you SELECT from a second table based on data in the first.

    Rather than write your query for you, I am going to link you to the mySQL manual page on JOIN. On the left, you can choose the same manual in French, German, Japanese, Portugese and Russian. Hopefully, one of these languages suits you.

    Now, JOIN syntax is hard. Its one of the hardest parts of mySQL to learn. However, its not that difficult once you have tried it a few times. Plus, the manual can explain it a lot better than me!

    http://dev.mysql.com/doc/mysql/en/JOIN.html
    Last edited by HTML; 03-09-2012 at 10:55 AM.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  3. #3
    ramis55's Avatar
    New User

    Status
    Offline
    Join Date
    Jan 2005
    Posts
    8
    Thanks for answer. the now with JOIN syntax my query returns good data.
    Yes, my english is not first language!

  4. #4
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    Your english is very good though. I could only tell through some small idioisms.


    Glad that helped.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?



Tags for this Thread