How do I create a user history?

Question

I want to create a user history function that allows shows users what they done. ex: commented on an ad, posted an ad, voted on an ad, etc.

How exactly do I do this?

I was thinking about... in my site, when they log in it stores their user_id ($_SESSION['user_id'])

1. so I guess whenever an user posts an ad(postad.php), comments(comment.php), I would just store in a database table "userhistory" what they did based on whenever or not their user_id was activate.
2. When they comment, I store the user_id in the comment dbc table, so I'll also store it in the "userhistory" table.
3. And then I would just queries all the rows in the dbc for the user to show it

Any steps/improvements I can make? :)

Answer 1

Look at the statistics and logging section of media wiki schema. You can implement something similar to this.

http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.png

Answer 2

Similarly, what you could do is have mySQL based logging, ie every page hit is logged in the mySQL database, which tracks the IP, userid, datetime and page requested.

Then for the page that you view history, you could have a script like this. This is just pseudo code, so take it at face value.

<?php
    $actions = array('comment.php' => 'posted a comment', "postedad.php" => "posted an ad");

    $query = mysql_query("SELECT * FROM logHits JOIN users ON users.id = logHits.userid WHERE loghits.userid = $userid");

    while ($row = mysql_fetch_array($query)) { 
         echo $row['username']." ".$actions[$row['pagename']."<br />"; 
   }
?>

Again, this is just pseudo code and you can most certainly improve the concept by it. You could possibly use a combination of printf();/sprintf(); and make it so, for example, "a comment" in the action text hyperlinks to the actual comment.

There are quite a few ways to go about this. This probably isn't the best way.

You could also just do an insert query to a table like userHistory whenever the persons does an action like you specified. This is probably the best way to go about it. You could have that able with fields like id, userid, actiontype, actionid

actiontype would be "comment" or so, and actionid would be the id of the posted entry. Then you could easily map actiontypes to the comment page (passing the actionid) to view the actual posted comment.