40982

Number of entries in DB PHP

I am creating a function to show how many users are online now. This is based on who has opened a page within the last 5 min. Each page load is saved to my DB, below:

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/w5vfD.png" alt="Database Table">

At the moment I have the following code

$query = mysql_query("SELECT user_id, timestamp FROM user_actions WHERE timestamp > date_sub(now(), interval 5 minute)"); $onlineUsers = mysql_num_rows($query);

This is simply totalling the number of rows, how can I do this so it only counts a user_id once? (so in the above database snippet it should be 2 not 5)

Answer1:

Since mysql_* is deprecated (php 5 onward) and removed in (php 7). So a mysqli_* example is here:-

<?php error_reporting(E_ALL); ini_set('display_errors',1); $conn = mysqli_connect('localhost','username','password','db name');//change credentials here $online_users = array(); if($conn){ $query = mysqli_query($conn,"SELECT DISTINCT(user_id), timestamp,page FROM user_actions WHERE timestamp > date_sub(now(), interval 5 minute)"); if($query){ while($row = mysqli_fetch_assoc($query)){ $online_users[] = $row; } }else{ echo "query error:-".mysqli_error($conn); } }else{ echo "db connection error:-".mysqli_connect_error(); } ?> <table> <tr> <thead> <th>User Id</th> <th>timestamp></th> <th>Page Visited</th> </thead> </tr> <tbody> <?php foreach($online_users as $online_user){?< <tr> <td><?php echo $online_user['user_id'];?></td> <td><?php echo $online_user['timestamp'];?></td> <td><?php echo $online_user['page'];?></td> </tr> <?php }?> </tbody> </table>

Note:- If you want to show online user name also then you have to do JOIN query.

change table code accordingly.

It's a sample code. modify it accordingly.

Answer2:

use DISTINCT keyword

$query = mysql_query("SELECT DISTINCT(user_id), timestamp FROM user_actions WHERE timestamp > date_sub(now(), interval 5 minute)"); $onlineUsers = mysql_num_rows($query);

Answer3:

You may use group by, e.g

SELECT user_id, timestamp FROM user_actions WHERE timestamp > date_sub(now(), interval 5 minute) group by user_id;

Recommend

  • cannot pass session variables
  • PHP query stop LIKE stop records duplicating?
  • Limit Pagination pages in PhP
  • Get specific date range
  • Removing Last Comma within while loop - PHP
  • Create a by Date and hour breakdown from TIMESTAMP field MYSQL
  • Best approach to select most viewed posts from last n hours
  • PHP form returns Notice: Array to string conversion
  • Check Username Availability Issue
  • Android login activity using mysql database
  • Complicated Query + Pagination Script
  • show message when no rows selected mysql php
  • How to get the 10 max and 10 min of rows with this query?
  • Number of entries in DB PHP
  • Offset from end / reverse offset?
  • How to set repeating alarm using setExact and how to cancel the same?
  • Get highest value from a file using mSL and mIRC
  • How to override JAXB @XMLAccessorType(XMLAccessType.FIELD) specified at a Class level with @XMLEleme
  • Facebook Open Graph Story Custom Actions Keep Getting Rejected - Advice Please?
  • finding symmetric difference/unique elements in multiple arrays in javascript
  • How can I write a where clause in SQL to filter a DATETIME column by the time of day?
  • Timer once a minute on the minute
  • Firestore - Checking The Connection Status Of The Module To The Server
  • MySql - get days remaining
  • data.table replicate rows after join?
  • Getting syntax error in mysql-php. You have an error in your SQL syntax;
  • Runtime.exec() gives Error: Could not find or load main class
  • WPF ICommand CanExecute(): RaiseCanExecuteChanged() or automatic handling via DispatchTimer?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • Is there a javascript serializer for JSON.Net?
  • Javascript Callbacks with Object constructor
  • Where to put my custom functions in Wordpress?
  • vba code to select only visible cells in specific column except heading
  • How can I use Kendo UI with Razor?
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • Buffer size for converting unsigned long to string
  • Change div Background jquery
  • Sorting a 2D array using the second column C++
  • Binding checkboxes to object values in AngularJs