1

I'm trying to create a Users Online page for my site with just php. I have a table column called lastactive which is updated as a DATE/TIME (example: 2014-12-13 21:04:15) each time they visit a page.

Now I want my Users Online page to show all of the users who have been active within the last 10 minutes of the current time. So if it was 10pm, I'd want a users active from 9:50pm-10pm to show.

I found a previous StackOverflow question (can't find it now) that gave me some tips so I tried that and I ended up with this:

$timequery = mysqli_query($con,"SELECT times.lastactive FROM times WHERE lastactive >= NOW() - INTERVAL 10 MINUTE ORDER BY lastactive");

and

$whoisloggedin = mysqli_query($con,"SELECT username FROM users LEFT JOIN times ON times.id=users.id WHERE lastloggedin >= NOW() - INTERVAL 10 MINUTE ORDER BY lastloggedin");

But this just shows every user. Here's what I use to show the user

echo "Shows all active players from the last 10 minutes.";

while ($row = mysqli_fetch_assoc($timequery)) {
   $row2 = mysqli_fetch_array($whoisloggedin);
    $usersArray2 = $row2['username'];
    echo $usersArray2;
}
Paweł Tomkiel
  • 1,974
  • 2
  • 21
  • 39
Ben
  • 101
  • 1
  • 11
  • Please post the structures of the `users` and `times` tables, along with a small sample of rows from each. – Michael Berkowski Dec 13 '14 at 21:24
  • Just guessing, but `times.id` seems like the wrong join column, at least by convention. If you have something like `times.user_id` that would be more appropriate. But please post the structures so we can understand the relationship. – Michael Berkowski Dec 13 '14 at 21:36
  • 3
    @MarkM But the `WHERE` condition on a `times` column essentially turns it into an inner join anyway because there's no `OR lastloggedin IS NULL`, no? – Michael Berkowski Dec 13 '14 at 21:37
  • @MichaelBerkowski +1 Yup you're right. Nice catch. – Mark Miller Dec 13 '14 at 21:46
  • @MichaelBerkowski `times.id` is correct, all of my tables have an `id` column which is the primary column. A user with a ID of 3 would use the times.id of 3 – Ben Dec 14 '14 at 10:17
  • @Ben please post a sample born rowan from both tables. It does not look like you need 2 queries with nested loops – Michael Berkowski Dec 14 '14 at 12:30
  • @MichaelBerkowski Rather just leave it tbh, my tables are quite big and I know the columns are correct...It's to do with `lastloggedin >= NOW() - INTERVAL 10 MINUTE` as it works if I was change it to a specific DATE/TIME – Ben Dec 15 '14 at 09:32
  • @Ben You don't need to post the whole table, just a small sample of representative rows. We often set them up at http://sqlfiddle.com. If in a MySQL client you simply do `SELECT NOW() - INTERVAL 10 MINUTE` you will find that expression does indeed work to select the time 10 minutes ago. – Michael Berkowski Dec 15 '14 at 11:47
  • @MichaelBerkowski Is this not all you need? http://imgur.com/HW1qSqn. As you can see it selects one from 10 o'clock :/ EDIT it was around 12.35 that I did that – Ben Dec 15 '14 at 12:39
  • What is the server's time? That's what `NOW()` will use. Just do `SELECT NOW()` to find out. If your server's time is hours in the past from your expected time, you would get the 10:54 row because that still is >= 10 minutes ago. – Michael Berkowski Dec 15 '14 at 13:59
  • It is not clear why you are making nested queries here? What is the diferrence between last loggedin and lastactive? Which are you actually trying to show the last values for. If you step back and explain what data values you are actually trying to get along with samples of you table data/schema, I am sure someone will give you a MUCH better approach to this which doesn't involve nested queries. – Mike Brant Mar 24 '15 at 19:19

0 Answers0