PHP Keeping Track of Process

Question

I´m running a code that saves the files submitted by users into a directory. I would like to keep track of the files submitted, specially the date, day of the week, the time, etc.

I want to be able to know at which day of the week, at which time of the day, which month, etc, the files submission has the higher rate, the current amount of files submited, etc. Of course plotting the data to a graph would be amazing.

I understand a simple Excel sheet can be created with phpExcel or something of the sort and this can be achieved by these means.

But I don't like the idea of having infinite rows with the data in them.

I haven't been able to find anything simpler or different rather than Excel sheets.

Does anyone happen to know a different method to record these data?

I´ sorry I forgot to mention, I´m not interested on keeping the database online, local will be okay.

Answer 1

When a user submits a file to their directory, in the upload script, make a call to a function like below:

function log_upload($user_id, $filepath){
    // check if user ID is valid using your own logic

    // Connect to your database
    $mysqli = new mysqli('host', 'user', 'pass', 'db_name');

    // Check connection is successful
    if($mysqli->connect_error){
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); 
    }

    $user_id = '"'.$mysqli->real_escape_string($user_id).'"';
    $datestamp = '"'. date("Y-m-d H:i:s") .'"';
    $filepath = '"'.$mysqli->real_escape_string($file_path).'"';


    $insert_stmt  = $mysqli->query("INSERT INTO logs (user_id, datestamp, file_path) VALUES ($user_id, $datestamp, $filepath)");
    if(!$insert_stmt){
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }
}

In your upload script call log_upload($user_id,$filepath); after it's uploaded successfully.

Note: You'll need to add your own logic or figure out how to pass the user_id and filepath of uploaded file yourself.

When you want to query this data, a simple MySQL query such as:

SELECT user_id, COUNT(user_id) as user_counted 
FROM logs 
WHERE datestamp BETWEEN '2015-01-01' 
AND '2015-01-31' 
GROUP BY user_id

Would return the highest using users on your site for the month January in 2015.

You can start to build up more complex SQL queries to determine other factors as you require them. Have a look at the MySQL manual for guidance and tips, also (obviously!) Stack Overflow.

If you absolutely want to view this data using Excel, you can download an Excel/CSV file from MySQL and use Excel until your comfort level with SQL queries increases. There's loads of SQL tutorials out there and this Stack Overflow question, although a bad question, does touch on how to generate graphs etc. from MySQL.