PHP PDO计算包含一行值的MYSQL列

I currently have a table called register containing a user_id followed by an changing list of dates (columns) with a prefix of date. Example: date14102018.

I need to count all of the columns containing 'Attended' owned by a user, the user loop is being handled by the while statement at the beginning of the below script.

I will need to have a loop to do this for each user defined.

Note: This isn't a complete script and does NOT work.

while ($row2 = $stmt->fetch(PDO::FETCH_ASSOC)) {

        $attendCount = 0;

        foreach ($Date as $item) {
            $value1 = 'Attended';

            $stmt = $db->prepare('SELECT COUNT('.$item.') FROM register WHERE (user_id = :value2 AND '.$item.' = :value)');
            $stmt->execute(array(':value2' => $row['ID'], ':value' => $value1));
            $attendedCount = $stmt->fetchColumn();

            $attendCount = $attendCount + $attendedCount;
        }
        echo '<td style="font-weight:600;">' . $attendCount . '</td>';
        echo '</tr>';
    }

Variables:

$Date is an array containing all the column names (dates) from the register table.

$row['ID'] is taken from the users table containing the ID which corresponds to the user_id in the register table.

Two parts here, the first is more of a suggestion: Rather than have a column for each date, why not set your register table up to have just a user_id and date column. whenever you need to keep track of an attended date you just add a new row to the table so that there can be multiple rows with the same user_id, then to get the count all you would have to do is:

select count(user_id) as attendCount from register where user_id = :value2

You could then join it with the user table in your first statement and only make one query to the database for the entire process.

The second part, assuming the above is not an option, would be to loop through all the date columns and build the sql statement, then run it once in the loop. something like this:

$parts = array();
foreach ($Date as $item) {
        $parts = "if( " . $item . " = 'Attended', 1, 0)";
}

$sql = 'SELECT (' . join(' + ', $parts) . ') as itemCount FROM register WHERE (user_id = :value2)';
$stmt = $db->prepare($sql);
$stmt->execute(array(':value2' => $row['ID']));
...

This query should just check each column for "Attended" if its there it uses 1, otherwise 0. Then each of these values are added together to get the number of columns with the value of attended. The nice part is you are only running one query in the loop instead of one per column name.