Easy way to compare values with same keys in multidimensional array

PHP
1

Lets say I do a fetch:All MySQL query via PDO and I get an array of students and their inst, day, stime, and etime values. As you can see, each student is unique but the other values can either match the descending student or be different according to what is stored in the database. For example:


$slessons = $myPDO->query("SELECT student,inst,day,stime,etime FROM ltimes WHERE user_id='10' ORDER BY student ASC");

$slesson = $slessons->fetchAll(PDO::FETCH_ASSOC);

What I want to do is compare the inst, day, stime, and etime values of the the array and if inst, day, stime, and etime all match from one student to the next, then count it as one unique value and assign a value of 1 to a variable. If there are any differences like the inst or day, then count it as two different values and assign a value of 2 to a variable.

This can apply to whether I have one student or up to 5 if needed. I’m guessing I would have to assign a count() to the key matches.


Array
(
    [0] => Array
        (
            [student] => 67
            [inst] => 10
            [day] => mon
            [stime] => 17:00:00
            [etime] => 18:00:00
        )

    [1] => Array
        (
            [student] => 68
            [inst] => 10
            [day] => mon
            [stime] => 17:00:00
            [etime] => 18:00:00
        )

)
2

I’m not sure of what you’re trying but here goes…


SELECT student, inst, day, stime, etime,
FROM ltimes 
GROUP BY inst
HAVING count(student) > 1

that gets the ltimes with matches among inst. For all 4 to match we get this beast…


SELECT student, inst, day, stime, etime
FROM ltimes
WHERE inst IN (SELECT inst FROM ltimes GROUP BY inst HAVING count(student) > 1 )
  AND day IN (SELECT day FROM ltimes GROUP BY day HAVING count(student) > 1)
  AND stime IN (SELECT stime FROM ltimes GROUP BY stime HAVING count(student) > 1)
  AND etime IN (SELECT etime FROM ltimes GROUP BY etime HAVING count(student) > 1)

This will return the ltime records with at least two students with all 4 fields matching. From here modify the query further to get the other corner cases you need.

I know this isn’t an exact answer to your question - but as a general rule of thumb if you can get SQL to handle the data sorting you should do so, it will always be more efficient than sorting using PHP.

3

Michael, that will select only the rows from student for which there are other students with the same info. It will not select single instances.

How about this?


SELECT
   s1.student
 , s1.inst
 , s1.day
 , s1.stime
 , s1.etime
 , COUNT(s2.student) AS COUNT
FROM
   student s1
      LEFT JOIN
   student s2
      ON
   s1.inst=s2.inst AND s1.day=s2.day AND s1.stime=s2.stime AND s1.etime=s2.etime
GROUP BY
   s1.student

I couldn’t agree more! :slight_smile:

4

Ah. Well, I was having difficulty parsing intent from the original post last night. I shot that message off just before going to sleep.