What is the best practice for logging users activity?

For logging, you want to have a table logs, where you log the session id $_SESSION['id'] (presuming you have sessions?) and the user's activity. You then insert it using a delayed MySQL query (because the logs are not high priority):

INSERT DELAYED INTO table (session_id, activity) VALUES ('1234', 'blah');

Refer to this link for more information on DELAYED inserts.

In other words, put all the logic on the PHP side, just have a MySQL table in which you log any activities using delayed. This would be a function log_activity($session_id, $activity) that you can call from anywhere where there is a loggable activity.

As always, "it depends".

If the evolution over time of your core business concepts is important, it should be a first-class concept in your database design, as well as your PHP logic. Fowler writes about this in "Analysis patterns". This allows you to capture who made which changes to your business objects and answer questions like "who changed the project from type x to y on date z?", "how often did user x change product y?" etc. It does make the domain model more complex.

If you don't need this level of integration, my preference is to put the logging functionality in PHP; triggers have a horrible way of slowing down a database, or leading to unexpected side effects, or being forgotten by developers making changes to the schema. So, in PHP, I'd include explicit "log this" statements, possibly using an aspect-oriented framework (though I've never used that in PHP, only in Java).