temp table or array?

[drooh]

Ive got a website that matches up workers and employers based on 10 question form of what is most important to them on a scale of 1 to 10.

The object is to find an worker and employee that have similar answers to the questions.

There will be hundreds of entries.

So Im thinking that there would be a way to click on a particular worker (find similar) and have it start querying all the employers and finding the total difference (absolute value) between the two. Then Im thinking I would sort those by the lowest to highest in difference.

My biggest question is that Im thinking I need to use mysql's temporary table feature to store the following

id
worker_id
employer_id
total_diff

So Im thinking it would go like this

// query worker db for single row info
select * from workers where id=45
$worker_answer1 = 5
$worker_answer2 = 3
$worker_answer3 = 2

// query each employer and store result in temp table
select * from employees
$employer_answer1 = 2
$employer_answer2 = 6
$employer_answer3 = 3

// find total diff
$total_diff = abs($worker_answer1-$employer_answer1)+abs($worker_answer21-$employer_answer2)+abs($worker_answer3-$employer_answer3);

// insert into temp table
insert into temp_table
id,worker_id,employer_id,total_diff
values
null,45,$employer_id,$total_diff

// query temp table order by total diff asc
select * from temp_table order by total_diff asc


Does this seem like a good approach? Or would using arrays be more ideal?

[Readie]

I think several arrays should be fine.

First: build 2 multi dimensional arrays like this:

$employers = array(
'employer id' => array('values', 'for', 'each', 'question');
'employer id' => array('values', 'for', 'each', 'question');
);

$workers = array(
'worker id id' => array('values', 'for', 'each', 'question');
'worker id id' => array('values', 'for', 'each', 'question');
);

The foreach though them, doing something like this:

foreach($workers as $worker => $w_answers) {
$w_total = array_sum($w_answers);
foreach($employers as $employer => $e_answers) {
$e_total = array_sum($e_answers);
if($e_total <= $w_total) {
$diff = $w_total - $e_total;
} else {
$diff = $e_total - $w_total;
}
$final[$worker][$employer] = $diff;
}
asort($final[$worker]);
}

Should do the trick, a print_r($final); should show you an array built like

array
(
[worker id] => array
(
[closest employer id] => difference
[next closest employer id] => difference
[next closest employer id] => difference
)
[worker id] => array
(
[closest employer id] => difference
[next closest employer id] => difference
[next closest employer id] => difference
)
)

so, assuming none of your employer id's ARE 0, calling:

$output = '<u>Best matches:</u>';
foreach($workers as $worker => $ignore) {
$diff = $final[$worker][0];
$best_match = array_search($diff, $final[$worker]);
$output .= '<br />' . $worker . ' => ' . $best_match;
}
echo $output;

Will output a list of workers, with their closest match.

[bizminder]

You have to first write a query to find the difference then using ORDER BY clause order the differences. You must create a multi dimensional array for this.

[drooh]

I actually ended up using a temporary mysql table and that seemed to work great. didnt take to much strain on my mind to put it together.