PHP complex MySQL query Counting from different table

1
votes

I'm working on a networking/social website and I'm trying to create a function to return the users that are visiting the same page at the moment you visit it.

How can one select the users from the table "USERS" by counting and grouping the user_id from the table "READING"?

Database structure is the following :

USERS

  • user_id
  • username
  • ...

READING

  • id
  • user_id
  • update_timestamp
  • ...

What should be returned is a array like this

user
  - user_id
  - user_name
  - user_first_name
  - ...
user
  - user_id
  - ...

This is the query that I have so far

SELECT r.user_id, u.* FROM reading r, users u GROUP BY r.user_id AS count WHERE u.user_id = r.user_id ORDER BY count DESC

Example tables

USERS 

// user_id // user_name // user_email
-------------------------------------
    123    /   mentos   /  [email protected]
-------------------------------------
    321    / freshmaker / [email protected]
-------------------------------------
    231    /  hubba     /  [email protected]

READING

//    id  //  user_id   //  timestamp
--------------------------------------
      1   /    123      /   201501050420
      2   /    321      /   201501050420

Example of what the output should be

-ARRAY
  -user
     - 123
     - mentos
     - [email protected]
   -user
     - 321
     - freshmaker
     - [email protected]
1
phpmysql
A few questions: You mention "counting and grouping" but your sample output doesn't include any sort of aggregation like COUNT(). What do you mean by that? And how do you determine the "at the same time" criterion? You have reading.update_timestamp -- should there be some threshold, say update_timestamp has a value within the past 60 seconds? – Michael Berkowski
Forgot to place it in the example, the reading.update_timestamp will be updated every 5 minutes so for example if you are on page A and i'm on page A, I would have to see that u are on the same page as me. I hope this explains it a little bit better. – joker stream
Please post a small sample of rows from the two tables - enough to illustrate a group of users reading at the same time, and a group not reading at the same time. Given that data, also please post a table of what the query's output would be. The group/count is still not well explained. – Michael Berkowski

1 Answers

0
votes

Are you looking for something like this?

SELECT u.*
  FROM 
(
  SELECT DISTINCT user_id
    FROM reading
   WHERE update_timestamp >= NOW() - INTERVAL 5 MINUTE -- or any other appropriate time interval
     AND user_id <> 4 -- depending on the logic you might want to exclude the current user itself
) q JOIN users u
    ON q.user_id = u.user_id

Here is a SQLFiddle demo