CODEWITHSUNDEEP
phpmysqlcount
Shane
Shane

Reputation: 753

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:

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)

Upvotes: 2

Views: 111

Answers (3)

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72299

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.

Upvotes: 0

Kalaivani M
Kalaivani M

Reputation: 1290

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);

Upvotes: 1

Qammar Feroz
Qammar Feroz

Reputation: 718

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;

Upvotes: 0

Related Questions