php and mysql user tracking and reporting

Question

Hi, I currently have a table which consists of user information and lesson id; the table layout looks like:

----------------------------------------------------
|employeeID|numVisits|lessonID1|lessonID2|lessonID3|
----------------------------------------------------
|33388     |2        |1        |0        |3        |

and a lessons table which contains the information about the lesson:

------------------------------------------------------
|lessonID  |cateogry |title    |filepath |numberviews|
------------------------------------------------------
|1         |beginner |lesson   |file://  |10         |

Within the lessonID fields in the user table is an integer which tracks how many times someone has clicked on a lesson. Now what I am trying to do is in a report I have the top 5 people who have visited the site and would like to then be able to drill down into what lessons they have clicked on. Can anyone help with this? Or would restructuring the way the database is be an easier task?

Thanks

---

The way i have been looking at it so far is:
1 - get all the lessonID columns for a specific employeeID
2 - check which ones have a value greater than 0
3 - using the list in step 2 then query the lessonID on the user table for the corresponding title.
Step 1:

$sql = mysql_query("SELECT * FROM users 
            WHERE employeeID = 15110") or die(mysql_error());

$columns = mysql_num_fields($sql);
for($i = 0; $i < $columns; $i++) {
    if(substr(mysql_field_name($sql, $i),0, 8) == "lessonID"){
        $lessons[] = mysql_field_name($sql,$i).", ";
    }
};  
$lessonID = array_unique($lessons);

$l = substr("SELECT ".implode($lessonID)."", 0, -2)." FROM users WHERE employeeID = 15110";

This is where I am now at a loss, the above $l constructs the query to select all lessonID columns in the user table with a specific employeeID. However I am at a loss as to where to go next with the query result.

Answer 1

Currently your employee can only ever take 3 lessons. You'd do better to normalize the data. Perhaps something like this:

employees
---------
emp_id
emp_name
etc.

visits
------
visit_id
visit_timestamp
emp_id

lessons
-------
lesson_id
lesson_title
etc.

emp_lessons
-----------
emp_id //FK to employees table
lesson_id //FK to lessons table
lesson_date

Then, when you want to know how often someone has visited,

SELECT count(*) FROM visits WHERE emp_id=x

And if you want to know how many times someone took lesson 1:

SELECT count(*) FROM emp_lessons WHERE lesson_id=1 AND emp_id=x;

Answer 2

You need to normalise your database design.

Consider a link table between your employee and lesson tables. This allows you to relate many employees to many lessons.

E.g.:

employee table

employee_id, num_visits

lesson table

lesson_id, category, title, filepath, numberviews

employee_lesson table

employee_id, lesson_id

The employee_id and lesson_id create a composite key.

Answer 3

Adding to the answers here's my two cents:

Considering the table structure I added below, you need the following queries to extract the data you need:

If you're tracking down the visits through table employee use the following, the result is the top 5 employees who visited the site:

SELECT * FROM employee ORDER BY visits ASC 0,5

if you're using the visit table:

SELECT employee.*, count(visit.visitId) as visits FROM employee, visit WHERE employee.employeeId = visit.employeeId GROUP BY employee.employeeId ASC 0,5

finallly, to check what lessons each employee has accessed, just use this:

SELECT * FROM employee WHERE employeeId = (SELECT employeeId FROM class GROUP BY lessonId)

You should reconsider the design, personally I'd do something like this:

**employee**
employeeId
employeeName
employeeLName
visits
lastVisit

If you want to track down each visit's date and time you should add a new table, visit, if you're not interested in the date and time, or just the last one, just add the fields to the employee table:

**visit**
visitId
visitDate
employeeId FK

**lesson**
lessonId
lessonName
lessonPath
lessonViews

**class** (or any name you see fit)
employeeId FK
lessonId FK
lessonDate (Last time the employee accessed the lesson)

Didn't have enough time to test the queries but I think they should at least point you in the right direction, hope it helps a bit :)