UserSpice Auditor Suggestion

// Simple auditor for UserSpice 3.0.4b

Provides auditing for:
UserID of the user making the action
Field for remote IP (not implemented here)
UserID of the OTHER user (eg admin changed title for user 12)
32char text for the event (eg the relevant LANG entry)
128char text for the action
int reference to an item (presumably the pivot point of your killer app)

Create the table:

// note table prefix.

CREATE TABLE IF NOT EXISTS `uc_audit` (
  `audit_id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_userid` int(11) NOT NULL,
  `audit_ip` varchar(16) NOT NULL,
  `audit_othus` int(11) NOT NULL,
  `audit_event` varchar(32) NOT NULL,
  `audit_action` varchar(128) NOT NULL,
  `audit_itemid` int(11) NOT NULL,
  `audit_timestamp` int(11) NOT NULL,
  PRIMARY KEY (`audit_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


In models/funcs.php (or your user defined funcs)

// Audit
 function writeUSAudit($userid,$othus,$event,$action,$itemid=0)
    {
    global $mysqli,$db_table_prefix;
    $time = time();
    $stmt = $mysqli->prepare("INSERT INTO ".$db_table_prefix."audit (
    audit_userid,audit_othus,audit_event,audit_action,audit_itemid,audit_timestamp
    )
    VALUES (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?
    )");
    $stmt->bind_param("iissii", $userid,$othus,$event,$action,$itemid,$time);
    $result = $stmt->execute();
    $stmt->close();
    return $result;
    }


// Usage eg admin_user.php

 if (updateDisplayName($userId, $displayname)){
    $successes[] = lang("ACCOUNT_DISPLAYNAME_UPDATED", array($displayname));
    writeUSAudit($loggedInUser->user_id,$userId,"Changed name ",lang("ACCOUNT_DISPLAYNAME_UPDATED", array($displayname)));


// Read and display it as required
// In models/funcs.php (or your user defined funcs)

//Retrieve information for admin audit
// no error checking for empty results.

    function fetchAllAudit($since=0)
        {
        global $mysqli,$db_table_prefix;

        $datemod = ($since == 0) ? '' : $since; // not implemented yet - notifications(!)

        $stmt = $mysqli->prepare("SELECT
                id,
                display_name,
                audit_id,
                audit_userid,
                audit_ip,
                audit_othus,
                audit_event,
                audit_action,
                audit_itemid,
                audit_timestamp

            FROM ".$db_table_prefix."audit LEFT JOIN ".$db_table_prefix."users ON audit_userid = id ORDER BY audit_id DESC") ;
            $stmt->execute();
            $stmt->bind_result($userid, $displayname,$auditid, $audituserid, $ip, $module, $event, $action, $itemid, $timestamp);

            while ($stmt->fetch()){
                $row[] = array('id' => $userid,'display_name' => $displayname,'audit_id' => $auditid, 'audit_userid' => $audituserid,  'audit_ip' => $ip, 'audit_othus' => $module, 'audit_event' => $event, 'audit_action' => $action, 'audit_itemid' => $itemid, 'audit_timestamp' => $timestamp);
            }
            $stmt->close();
            return ($row);
        }


<?php
//show audit for admin only
if ($loggedInUser->checkPermission(array(2))){
?>
      <div id="audtable" class="table-responsive ">
            <table class="table">
              <thead>
                <tr>
                  <th>Name</th>
                  <th>Event</th>
                  <th>Action</th>
                  <th>Item</th>
                  <th>For</th>
                  <th>Date</th>
                </tr>
              </thead>
              <tbody>
              <?php
                $auditData = fetchAllAudit();
                //Cycle through audit data
                  foreach ($auditData as $v1)
                  {
                  $audate = date("D jS M Y G:i:s", $v1['audit_timestamp']);
                  $adisp_name = ($v1['display_name'] == "") ? "Unknown" : $v1['display_name']; // eg failed login - no userID
                  $adisp_rowc = ($v1['audit_othus'] == '666') ? "alert alert-danger" : ''; // eg failed login - code 666
                    echo '
                    <tr class="'.$adisp_rowc.'">
                    <td><a href="admin_user.php?id='.$v1['id'].'">'.$adisp_name.'</a></td>
                    <td>'.$v1['audit_event'].'</td>
                    <td>'.$v1['audit_action'].'</td>
                    <td>'.$v1['audit_itemid'].'</td>
                    <td>'.$v1['audit_othus'].'</td>
                    <td>'.$audate.'</td>
                    </tr>
                    ';
                    }
                ?>
              </tbody>
            </table>
          </div>
<?php } ?>