Welcome to WebmasterWorld Guest from 54.162.93.137

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

temp table or array?

temp table or array

     
1:13 am on Mar 29, 2010 (gmt 0)

5+ Year Member



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?
5:53 pm on Mar 29, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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.
8:00 am on Apr 7, 2010 (gmt 0)

5+ Year Member



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.
2:00 pm on Apr 7, 2010 (gmt 0)

5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month