计算单词的所有实例并存储在变量中

Question

I have these tables

users {id,fname,lname}

class1 {id,user_id,l1,l2,l3...}

In the class table in the l1 etc fields it can either be Present/Absent/-.

I want a php function that will select all users, find them in the class1 table, and count how many times they are present and store this.

I know its going to need at least two while loops. One for the users and one for the class.

I have started the users, but dont know how to do the class section.

    $user = mysqli_query($connection, "SELECT * FROM users ORDER BY fname");

      while($fetch = mysqli_fetch_array($user))
      {

    $uid = $fetch['id'];

    // Next Query and While Loop

}

Answer 1

class1 table might benefit from normalization into class1 (id, user_id, l_id, present) with separate row rather than column for each Present/Absent/- value. As it is now this should work:

$user = mysqli_query($connection, "
    Select *
      , (Select Count(*) From class1 Where user_id = users.id AND l1 = 'Present')
      + (Select Count(*) From class1 Where user_id = users.id AND l2 = 'Present') 
      + (Select Count(*) From class1 Where user_id = users.id AND l3 = 'Present')
      /* ... and so on for each l ... */
        As present
    From users
");

while($fetch = mysqli_fetch_array($user))
{
    $uid = $fetch['id'];
    $present = $fetch['present'];
}