phpmysqllaravel-5tracker

Making a visitor tracker in laravel 5.1


I've currently installed a plugin (https://github.com/antonioribeiro/tracker)

Which does the tracking thing, only I want to get unique visitors. I really don't know how to extract those from this package. Or how I should make one by my own.

I want them to return as a JSON object per month.

If someone could help me out with this?

I tried it using the tracker_sessions table, but that doesn't work well.

Route::get('admin/api', function(){

        $stats = DB::table('tracker_sessions')
          ->groupBy('created_at')
          ->orderBy('created_at', 'ASC')
          ->get([
            DB::raw('created_at as y'),
            DB::raw('COUNT(*) as b')
          ]);

          return json_encode($stats);
    });

That returns something like this:

[{"y":"2016-05-22 21:17:17","b":1},{"y":"2016-05-22 21:17:27","b":1},{"y":"2016-05-22 21:17:28","b":2},{"y":"2016-05-22 21:17:29","b":1},{"y":"2016-05-22 21:17:31","b":1},{"y":"2016-05-22 21:17:33","b":1},{"y":"2016-05-22 21:18:10","b":1},{"y":"2016-05-22 21:18:11","b":2},{"y":"2016-05-22 21:18:13","b":1}]

Which is not good at all.


Solution

  • Route::get('admin/api', function(){
    
        $stats = DB::table('tracker_sessions')
          ->groupBy(DB::raw('CAST(created_at as DATE)'))
          ->orderBy(DB::raw('CAST(created_at as DATE)'))
          ->get([
            DB::raw('CAST(created_at as DATE) as y'),
            DB::raw('COUNT(DISTINCT ip_column) as b')
          ]
        );
    
        return json_encode($stats);
    });