How to log user actions with php and mysql?

Basic Answer

Instead of doing this yourself, from scratch, check out how some existing systems do it, and if their license allows, use their design and code (make sure you document what code you've used and add a copyright notice to your CMS somewhere).

Possibly Helpful Example

I'm not sure about PHP CMS's which do this, but I know Django's admin app does. Django is implemented in Python, but it should be fairly straightforward to port this code over to PHP. Even if the code isn't a straight port, the design could be ported.

The file which contains the logging is in the admin module in models.py.

Some key aspects:

The data model for the logging table:

class LogEntry(models.Model):
  action_time = models.DateTimeField(_('action time'), auto_now=True)
  user = models.ForeignKey(User)
  content_type = models.ForeignKey(ContentType, blank=True, null=True)
  object_id = models.TextField(_('object id'), blank=True, null=True)
  object_repr = models.CharField(_('object repr'), max_length=200)
  action_flag = models.PositiveSmallIntegerField(_('action flag'))
  change_message = models.TextField(_('change message'), blank=True)
  objects = LogEntryManager()

And the LogEntryManager, which saves the actual log entries:

class LogEntryManager(models.Manager):
  def log_action(self, user_id, content_type_id, object_id, object_repr, action_flag, change_message=''):
    e = self.model(None, None, user_id, content_type_id, smart_unicode(object_id), object_repr[:200], action_flag, change_message)
    e.save()

I use two tables for activities, one that gives each activity an id, and another one that just logs the user id, activity id, and a timestamp. I do this because an int takes up less space than a string, so why log the same strings over and over? The second one isn't really necessary, you just just as easily keep the action codes in a text file for your own reference, but the db just seems like a easier place to remember.

In the past I've used a function to handle the actual logging actions, but the next time I do it I'm going to be using the Observer pattern. It appears to be a lot more flexible, and I've already had to edit out logging function calls from older code I have that wasn't going to log anything. I much prefer reusing code with no editing required.

PHP AND MYSQL

Create a Table to save the logs

CREATE TABLE `test_loq` 
(
   id int(11) PRIMARY KEY AUTO_INCREMENT,
   page varchar(255) NOT NULL,
   username varchar(255) NOT NULL,
   log_time datetime DEFAULT CURRENT_TIMESTAMP,
   log_action longtext NOT NULL,
   log_name varchar(255) NOT NULL,
   user_id int(11) NOT NULL,
   ip int(11) NOT NULL
)

Explain:

• log_action is the kind of action made here you can write a lot of information about the action that has been made.
• page is the page that the action was made of, the name of the php file
• log_name is the name of the action that was done
• username is the name of the user that hade made this action
• user_id is the id of the user that made this action
• ip is the ip address of the user

Create a class:

class log
{  
    CONST ENVIRONMENT = 'developemnt';

    private $id;
    protected $log_action;
    protected $username;
    protected $page;
    protected $ip;
    protected $log_name;
    private $user_id;

    public function __construct(string $log_action, string $username, string $log_name)
    {
        if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
            $ip = $_SERVER['HTTP_CLIENT_IP'];
        } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
        } else {
            $ip = $_SERVER['REMOTE_ADDR'];
        }
        
        if(!empty($_SESSION['id'])){
            $id = $_SESSION['id'];
        } else {
            $id = 0;
        }
        $this->log_action = $log_action;
        $this->username = $username;
        $this->log_name = $log_name;
        $this->user_id = $id;
        $this->page =  basename($_SERVER['PHP_SELF']);
        $this->ip = $ip;
    }

    public function createAction()
    {
        global $conn;

        if(!$conn) {
           echo mysqli_error($conn); die;
        }
        $sql = "INSERT INTO test_log (`log_action`,`username`,`log_name`,`page`,`user_id`,`ip`) values ('".$this->log_action."','".$this->username."','".$this->log_name."','".$this->page."','".$this->user_id."','".$this->ip."')" ;
        $sql_query = mysqli_query($conn,$sql);
        if(!$sql_query){
            echo mysqli_error($conn); die;
        }

        if(ENVIRONMENT == 'development'){
            $_SESSION['msg'] = 'A new log was created ' . $this->log_name;
        }
        
    } }

Explanation:

• ENVIRONMENT can be development or production, if it's in development it will show flash messages about the log that has been made

Log an action!

Example: log action for login attempts

Create a php file logincheck.php:

<?php 
session_start();
include("include/configurationadmin.php");
//include_once('../include/classes/config.inc.php');

$username = $_REQUEST['username'];
$password = $_REQUEST['password'];
$sql  = mysqli_query($conn,"select * from ".$suffix."admin where username='".$username."'") ;


// HERE HOW TO LOG ACTION

$log = new log("Logging in attempt from $username" , $username ,'Login Attempt' );
$log->createAction();
 
//SIMPLE AND COOL RIGHT?

if(mysqli_num_rows($sql) > 0)
{
    $rows = mysqli_fetch_assoc($sql);
    if(md5($password) == $rows['password']) {
        $_SESSION['id'] = $rows['id'];
        $_SESSION['username'] = $rows['username'];
        $_SESSION['usertype'] = $rows['type'];
        mysqli_query($conn,"update ".$suffix."admin set lastlogin='".date('Y-m-d')."' where id = '".$rows['id']."' and username='".$rows['username']."'") ;
        $domain = ($_SERVER['HTTP_HOST'] != 'localhost') ? $_SERVER['HTTP_HOST'] : false;
        setcookie('rrdssrdda', $rows['id'], time()+120, '/', $domain, false);
        header("Location: http://localhost/test/admin-new/dashboard");
        exit();
    } else {
        $_SESSION['message']="<div class='alert alert-danger' role='alert'>Invalid userid/password!</div>";
        header("Location: http://localhost/test/admin-new/");
        exit();
    }
} else { 
    $_SESSION['message']="<div class='alert alert-danger' role='alert'>Invalid userid/password!</div>";
    header("Location: http://localhost/test/admin-new/");
    exit();

} ?>

Happy coding!

Its very simple to do with PHP/JAVA FUNCTION JQUERY and its AJAX data posting method... Before posting the solution -- Lets read these two lines

Why and What we want to record ? --- As we know only to record transaction with in the database --not all the clicks and checks -- but yes its possible with this solution....

Here is the solution step by step: -

1. create a DB Table -- to record these things
     a) Page Name.
     b) logged in user name
     c) session details (To record all the sessions).
     d) POST/GET data details (To record all the post/get data for the       
        page)
     e) Record Created Date.

or any other thing that you want to record. 2. Create a Jquery function or PHP function -- which will be auto triggered with every page. 3. This function will collect all the session of that page,user logged in details and what data passed to that page. Apart from this - you can also record -- from which page this new page is called -- Its pretty simple and best way to implement loggs recording features even in already running old software's :)

If you want all the Code i mentioned above to use -- Search it over the NET the mechanism i have defined just you need FUNCTION CODE -- AUTO execute function code -- simple