Comparing Two Tables Row By Row

[O'brien5]

Larry I like reading your forums and tutorials though am a newbie, using your yii tutorials I have been able to build an online admission application in a small period of time. I have two tables in my database which I want to use to select applicants who submitted valid results.

 

The first table (HS_results) has attributes((index_number, subjects)pk , grade, valid) which contains results entered by the applicant.

 

The second table(Exams_Board_results) which has the same structure as the first table( minus valid) but its data is already populated with the results from the exams board.

 

I want to build a function that checks that for every row in first table has the same grade with a row in the second table if the primary key is the same

 

for matching rows let the attribute "valid" =1 otherwise 0 .

 

And lastly. I already have Cgridview for. first table . how can I implement that function so that the values for valid automatically show in the gridview

[Larry]

Thanks for the nice words and sorry for the delayed response. Was traveling. Are you still needing help with this?

[O'brien5]

Thanks for the reply larry. Yes I still need your help.

[Larry]

Okay, let me look this over and I'll get you a good answer, hopefully on Wednesday.

[matthuisman]

Hi O'brien5.

 

Maybe some example data may help?

eg:

HS_results
EXAMPLE ROWS

Exams_Board_results

EXAMPLE ROWS

 

[Larry]

So I've read this a couple of times now. Before I try to offer a solution, I should say that I'm not clear why you have/need two separate tables. It would seem that having one table would be better, and then you'd have a "valid" column that differentiates between the two types of records. Would that not work?

[O'brien5]

This project is a prototype of a school admission system where by we can just import in the table of results from the examinations board database into the school admission database. So having already results from the applicants in one table structured to be identical with the imported table, we just compare the two tables to check who is faking results by comparing if "grades" are identical for simillar composite primary key ("index_number" and "subject").

 

I real thank you for taking the time to look into it. I appreciate.

[Larry]

Okay. I think it'd be most efficient if you create a script that performs the check (e.g., upon import of new records). I would add a flag column to the one table (that's used in the grid view) and have your script set that flag column's value based upon the results. Then you could do this using DAO, which would be fastest.