setting up a database for tracking which users have clicked which links?

Question

I'll try and elaborate on my problem a bit more.

I recently got an entry-level part time developer position with my university, in efforts to sharpen my dev skills. While I have used MySQL in the past, it was only briefly covered in a single course, as I am mostly a front end guy (HTML/CSS/JS).

Anyways, the department that hired me has a website designed for incoming students, to get them acclimated to college. It has tutorials and videos for them to watch, etc. In order to access the site, they must log in to their university account (which uses LDAP). Account names are in the format of abc1234.

Now, my problem is I need to create a way for the staff to track which tutorials/videos the freshman have watched. They would like me to do this using databases. There will potentially be thousands of students, and they want to be able to see exactly which students have / have not clicked each link/watched each video.

How should I set up databases for this? There will be multiple links/tutorials/videos that they want to track. Bonus points if there was a way of tracking which users watched the videos through to the end, however not required.

I believe I will need to use PHP for handling the exchange between the browser and the database, correct?

Thank you for any helps or tips. :)

Answer 1

You could simply make a PHP script that would retrieve the requested link for them, while also adding a value to MySQL.

If I were going to do this, I'd probably do something like this:

<a href="getResource.php?res=video1.mpg&type=video">Video 1</a>

And in PHP, I'd simply get the resource, type and the userid from the session, put them into the database, then retrieve the resource they were looking for. To track if they watched the video the entire way through, you could use javascript to watch for the event of the player coming to an end, then just use a skin that doesn't have a scrub bar.

Answer 2

you need for example a table "users: id, name, etc..." and a table "clicks: user_id, url".

to track link clicks you could do something like this:

<a hreF="log_click.php?url=<?php echo urlencode("some_url?some=param&etc=anything"); ?>">

log_click.php

<?php
$url = $_GET['url'];
$user = /* ie. $_SESSION['user_id'] */
/* insert to database */
header('Location: '. $url); // maybe need urldecode($url) here
exit;
?>