Count items in array requiring data from multiple tables

PHP
1

I have the following function to count total number of ‘activity’ posts in the users_activity table of my database;

////////////////////////////////////////////////////////////////////////////////////////////////////////
// ACTIVITY FEED: COUNT NUMBER OF POSTS IN ENTIRE ACTIVITY FEED
function count_totalactivityitems(){
    require 'includes/db.config.php';
    $sql = "SELECT count(*) FROM `user_activity`";
    $result = $db->prepare($sql); 
    $result->execute(); 
    $TotalActivityItems = $result->fetchColumn();
    return $TotalActivityItems;
}

And this code to grab specific data from the users table;

/////////////////////////////////////////////////////////////////////////////////////////////////////// 
// BEGIN QUERY TO GET ESCORT USER DATA
    function get_specificuserinfo($uid,$un,$col) {
        require 'includes/db.config.php';

    $query = "SELECT 
                *
            FROM users
            WHERE users.id = '$uid' AND username = '$un'
        "; 
    try 
    { 
        $stmt = $db->prepare($query); 
        $stmt->execute(); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: " . $ex->getMessage()); 
    } 
    $users_data = $stmt->fetchAll(PDO::FETCH_ASSOC); 

     $user_data = array();

    foreach ($users_data as $key => $value){
        return $user_data[$key] = $value[$col];
    }

}

and this code to display ‘NEW’ if the user_lastlogin column in the users table date is less than the activity_timedate column in the users_activity table.

<?php
          $logintime = strtotime(get_specificuserinfo($_SESSION['user']['id'],$_SESSION['user']['username'],'user_lastlogin'));
          $activity_date = strtotime($user_activity['activity_timedate']);

          if( $logintime < $activity_date ) { 
            echo "<span class='pull-right' style='color:red;'>NEW</span>";
          }
        ?>

In the footer and site navigation I want to display a count of ‘new’ activity items but I can only seem to show a count for total number of posts in the users_activity table.

How can I count only items that are ‘younger’ than the user’s last login time?

3

Slightly O/T, but this seems a long-winded way to do this:

    $users_data = $stmt->fetchAll(PDO::FETCH_ASSOC); 

     $user_data = array();

    foreach ($users_data as $key => $value){
        return $user_data[$key] = $value[$col];
    }

Would it not be easier to use $col in the query, rather than get all columns, call fetchAll() and get a 2-d array back, then iterate through it to find the column you wanted in the first place? Or as I imagine the query only returns a single row, get that single row using fetch() and return it as

return array("key"=>$users_data['id-or-whatever-it-is'], $col=>$users_data[$col]);

On topic, is there a column in users_activity that contains the date of the activity? If there is, just call your first query but with that date as the specification to compare with the users last login date.

4

Thanks for the post @droopsnoot.

I’m specifying the column as I wanted a function I could call on to only return specific data as and when it was required. Rather than constructing a new query each time for different pieces of the table.

There is an activity_timedate column in the users_activity table wich stores a timestamp of the activity - but I 'm not sure how to do the comparison - I tried first to merge the two bits of code together but that just chucked out errors.

5

I get that, but I just thought it might be easier if the query was something like

$query = "select " . $col . " from users where users.id = '$uid' and username = '$un'";
//... and then after executing it
$data = $stmt->fetchColumn(); // you could use this as it's only a single column
return $data; // after checking it returned something

OK, so first you need to retrieve the users last login date/time, then use it in the query:

$logintime = get_specificuserinfo($_SESSION['user']['id'],$_SESSION['user']['username'],'user_lastlogin');
$sql = "SELECT count(*) FROM `user_activity` where activity_timedate > '$logintime'";

I may have quotes not quite correct in there - I use prepared statements so I have lost track of what needs to be quoted and what does not.

1 Like
6

Thanks.

Yes, your right, it is cleaner to do as you suggest here.

Can I call this as part of another function? The count is current called in the footer with <?php echo count_totalactivityitems() ?> and I’d like to keep it that simple if possible.

I know it is a weird way to work but I’m building this as I’m comfortable doing, mostly PDO, then I intend to go back and tweak it so it is cleaner and uses prepared statements etc.

7

I can’t see why not.

8

Great. Thanks, I’ll give it a shot :thumbsup:

9

All works perfectly - thanks.

I have just spotted an issue though - probably more to do with how I built it, and maybe not something that can be solved here - the last login time is updated on each login of the user so new posts would only show as new if the user was already logged.

If the user was logging in again after 24 hours, posts in those last 24 hours wouldn’t show as new (even though the user hadn’t seen them yet) because their login time would be greater than the activity post time…

10

All you can do with something like that is to have another date, “last time activity was looked at”, and compare that. Only update that when you’ve delivered the latest new activity. That might not make any sense, depending on how the site is laid out. Or you could have a “latest login” and “last but one login” - when they login, move latest to be in last but one, and put now() into latest. Then use last-but-one to compare.

It’s a difficult thing to do with any great accuracy. How many times do you sign into a commercial forum and find that it has some stuff marked “read” that you haven’t read, or vice versa?

1 Like
11

Thanks for giving it some thought. I think for now I’ll stick to just showing for things that occur within that session then maybe rather worrying too much about anything else.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.