[Resolved] Compare MySQL result arrays for unmatched values?

Joseph_Sliker

Sometimes I'm kinda proud of what I've learned so far...and sometimes I feel like B'rer Rabbit punching the tar baby...

Okay. New area of ignorance to take on...

Is there a simple way to compare contents of two Result arrays?

Here's the concept:

I've got a CLASS_LIST table... (each kid has a unique student_ID ...that table's primay key)

I've got a TEST_DATA table...There is a record row for each kid who has been tested so far. The student_ID is the field that can link one table to the other.

So, I call for the existing data records for the kids already tested that resides in the TEST_DATA table.

I would like to follow the above with a list of students (from the CLASS_LIST table) who have NOT been tested/have no TEST_DATA record yet.

I suppose this would involve a separate MySQL query that uses the result from the initial (existing test results) query somewhere in the WHERE part of the statement?

LordShryku

I'd think this would be perfect for a left join. Then you wouldn't have to do any outside comparison since you're be doing it in the SQL.

   SELECT c.student_id
     FROM class_list c
LEFT JOIN test_data t
       ON c.student_id = t.student_id
    WHERE t.student_id IS NULL

Joseph_Sliker

...found this approach in another thread.

([Resolved] SELECT unused Reference Table Records)

and wrote the following query:

$Query = "SELECT CLASS_LIST.* FROM CLASS_LIST LEFT JOIN TEST_DATA ON CLASS_LIST.student_ID = TEST_DATA.student_ID  WHERE CLASS_LIST.SCHOOLID = '$SCHOOLID' AND CLASS_LIST.GRADE = '$GRADE' AND CLASS_LIST.ROOM = '$ROOM' AND TEST_DATA.student_ID is null ORDER BY FIRST, LAST";

This does exactly what I want it to.

Sxooter

Normally you'd use a SQL query of the form:

select id from table where id not in (select id from another table)

But you're likely using MySQL. MySQL uses an alternative syntax to get around not having subselects:

select t1.id
FROM t1
LEFT JOIN t2
ON t1.id=t2.id
WHERE t2.id
IS NULL;

Something like should be able to give you what you want without having to do it in PHP.

However, if you still want to do it in PHP, what you do is basically like this:

$query1 = "select id from t1";
$res1 = mysql_query($query1);
$count1 mysql_num_rows($res1);
for ($i=0;$i<$count1;$i++){
  $id1[]=mysql_result($res1,$i,'id');
}
$query2 = "select id from t2";
$res2 = mysql_query($query2);
$count2 mysql_num_rows($res2);
for ($i=0;$i<$count2;$i++){
  $id2[]=mysql_result($res2,$i,'id');
}
print implode(":",array_diff($id1,$id2));

Joseph_Sliker

Thanks LordShryku and Sxooter;

We all came up with pretty much the exact same thing at about the same time.

Take that, tar baby!