Creating % match between inputted sequence and sequence stored in database (questionnaire) PHP MySQL

[Trainor91]

I was wondering if anyone could help me.

A customer completes an assessment, each answer is a radio value between 1-5 and there are 10 questions. So therefore;

 

Assessment Table

assessmentid | studentid | q1 | q2 | q3.....q10

1 - 1001 - 1 - 5 - 3 - 2

 

The careers table has 10 careers which are each rated between 1-5 (this is set for each career)

 

Careers Table

careerid | careername | q1 | q2 | q3.....q10

1 - Doctor - 5 - 2 - 1 - 3

 

Im trying to create a results page which will check one sequence against another such as q1 in the assessment table against q1 in the careers table.

To explain further, the user will input a value between 1-5 for question 1. This will be matched for the q1 value for career1. This will be repeated for each question up until question 10. The overall result (% match) will be displayed - i.e. the %match for the user and that particular career. This process will then be repeated for all remaining careers.

I want the results to be shown in descending order and only display the top 5.

 

The results should be shown as

 

Career Name (result % match) Hyperlink (to external website)

At the minute I have it working but it has quite a lot of queries such as:

 

<?php
$career1sequence1 = mysql_result (mysql_query ("SELECT `q1` FROM `careers` WHERE `careerid ` = '1'"), 0):
$question1 = mysql_result (mysql_query ("SELECT `q1` FROM `assessment` WHERE `studentid` = $session_studentid"), 0);
?>
<?php
$career1result1 = $question1/$career1sequence1;
if ($career1result1>1) {
$career1result1 = $careersequence1/$question1
?>

 

This query is repeated for every question input/career sequence value. Would anyone know if there is an easier way to do this that is more straightforward? Any help is appreciated.

 

[lemmin]

I'm not 100% sure I understand, but you should be able to load all of the associative information into arrays instead of querying each relationship individually. The goal would be to turn the logic into more of a math problem than a decision tree. Maybe consider the differences between the career numbers and the answers to come up with a linear relationship.

 

For example (using your example data), the student answers differed from the Doctor answers by 4,3,2, and 1 respectively for a total of 10. Let's say a second career has the question numbers that match EXACTLY what the student entered (which would be the highest scored, right?). The differences are 0,0,0, and 0 for a total of 0. Now, if you do the same for all of the other careers and arrange them in ascending order, you have the the careers related to the student's answers. The percentage number depends on what it is based off of. You could use the range of 0 to the highest possible difference per question, or base it off of all the total ranges collected.

 

If I'm wrong about your end goal, maybe post one example scenario with real data to go off of.

[Barand]

Remember to use the ABS() of the differences, or a 3 and -3 would cancel each other giving a perfect 0 match