Creating a online matrix

[psuplat]

Hi everyone,

I need to create a onine training matrix for the company's respective sections.

I got the database, forms for inputing of skill for each individual, and displaying them for each individual. However I'm stuck when it comes to show a matrix for the section with all the people on it.

I'm attaching 2 pics: database layout and what I want to achieve.

the sql query I'm using to get data is:

SELECT * FROM training_matrix
INNER JOIN skills ON training_matrix.skillID=skills.skillID
INNER JOIN users ON training_matrix.userID=users.uid
WHERE training_matrix.sectionID=$section

$section is a value passed from from

any idea how to do it, do I need to rewrite the query? anything that can help really.

Thanks

[johanafm]

In my opinion the matrix table is superflous, and the matrix is actually the result set you get by running your select query. You do however lack a user_skills table, relating users with their skills. Or perhaps this is what you had the matrix table, but at any rate, the sectionid doesn't belong there.

SELECT u.name, skill.description as skill
FROM user AS u
INNER JOIN user_skill AS us ON u.uid = us.uid
INNER JOIN skill ON skill.skillid = us.skillid
WHERE skilld.sectionid = @some_section

And if a skill could belong to more than one section, you'd remove the sectionid from the skill table and create a link table between skill and section, containing nothing but sectionid and skillid, with a compound primary key on both fields.