Matching result from one mysql table to another

[sonoton345]

I have 2 tables, one for candidates and the other for employers with the same question. I want to match a candidate's answer to all employers answers, count number of matches and list which employer is being matched to. Can anyone suggest how to go about this? I know the results come in arrays if I query the database, just don't know how to use the result from candidate's table and see how many matches all the results from the employers table.

Candidate's table has

ID ANSWER1 ANSWER2 ANSWER3 CANDIDATE_ID

Employer's table has

ID ANSWER1 ANSWER2 ANSWER3 EMPLOYER_ID

[ldougherty]

You will want to use a query with JOIN, this allows you to select data from multiple tables based upon certain criteria.

 

http://www.webdesign.org/web/web-programming/php/mysql-join-tutorial.14876.html

[sonoton345]

I believe to be able to do a JOIN, the primary key of one table would be a secondary key in the other. There's no relationship between these 2 tables. The answers to the questions are the same for both tables, I just want to compare a candidate's answer to all the employers and see which matches.

[Ken2k7]

The relationship in your case would be the rows in which the answers are the same. Join using that.

[sonoton345]

hmm..so in a situation where I have 9 answers and a table like this.

ID ANSWER1 ANSWER2 ANSWER3 ANSWER4 ANSWER5 ANSWER6 ANSWER7 ANSWER8 ANSWER9 EMPLOYER_ID

ID ANSWER1 ANSWER2 ANSWER3 ANSWER4 ANSWER5 ANWER6 ANSWER7 ANSWER8 ANSWER9 CANDIDATE_ID

how do i query this?

 

SELECT * FROM EMPLOYERS WHERE......????

[arimakidd]

Another way to do this based on how you have described the tables would be to use arrays. Here would be the perfect case for a 2 dimensional array. The key or identifier for each answer in the candidate table would be the respective employer answer(or is that a normal array). Thus you would have index:value pairs being represented by (employee_answer:candidate_answer). You would have to write a function to build this array though which would be easy. You can then store this array and massage the data however like. If you want more detail, let me know I would be glad to help.  If this is not the direction you want to go, then you can use a sql statement. However I don't have enough detail on the relationship of the tables. I would use join as a last resort as it is memory intensive. Is there a relationship between the tables? ID for employer is the same as candidate for example? If a relationship between any of the columns exist ie. one to one, one to many then you have alot of options with respect to using sql.

[sonoton345]

Thanks so much for the response arimakidd. There is no relationship between the tables. I am trying to match a candidate to a prospective employer by asking different question but same answers on both part. I used candidate_id to identify answers of a particular candidate and same applies to the employer_id. I kinda thought of your option but just didn't know how to go about it.

It has even got more complicated now because I have 2 more steps of questioning. Step two is just a multiple selection from checkboxes and so also is step 3. So in total I have 11 answers, 9 of which are from radio button (1 answer each) and the other 2 from multiple checkbox selection ( a list of answers in each column).

[arimakidd]

Sounds like you have alot of work to do. I would start by creating another table that would link the answers of both employer and candidate. With respect to you second part, that shouldn't be much of a problem as you can use $_POST variable to get the value of any form element. If you have to loop through multiple form elements then you use JavaScript to pull out element:value pairs and then use php to do the rest of processing. Sorry for being so vague but I am not entirely sure of the scope of your problem. No doubt though, php can solve anything. Add mysql to that or some db, then you are just fine.

[sonoton345]

Thanks once again arimakidd. I already did the database entry for all steps, worked fine. If I get you right, I should have a table that's like this..

 

tableID c_answer_id e_answer_id

where c_answer_id is the candidate answers id and e_answer_id is the id of the employers answers, then do the array thing you suggested before. Now on that I am clueless on using array. I'd appreciate a sample code to try if that is possible...Many thanks.

[arimakidd]

Quick question before I answer, the third table, "c_anwser_id" and "e_answer_id", I am assuming they are numbers. Are they the same? Have a relationship? I am trying to work out a "where" clause that would pull out the info. This third table, is it that each tableID represents an instance where the answers for employer and candidate are similar? In addition You description of the tables employer and candidate is not entirely clear. How many columns are there? You said in a situation where there are 9 answers the tables look like...ANSWER1,ANSWER2,ANSWER3....ANSWER9......

Now I would hate to think that your columns keep growing with answers! I am hoping you have one column for answer in each table for employer and candidate. Describe for me the columns only, not how many rows you have. If your table is set up the way I hope it is then you will execute a query

[sonoton345]

[*]There are 11 questions each for candidates and employers, so I have 13 columns: tableID,answer1,...,answer11,candidate_id on the candidates table and same on the employers table - tableID,answer1,....,answer11,employer_id

[*]Nine questions are just radio button answers, so answer1....answer9 contains one answer each and the last two questions are multiple answers from a checkbox

[*]The column grows with the no of questions, but the questions are static. I'm only storing answers because the options are the same on both sides only the questions are rephrased.

[*] c_answer_id in the third table would the same as tableID in the candidate table and e_answer_id  the same as tableID in the employer table.

I hope this makes sense, thanks for ur help.