I'm trying to get the count the number of fields (AAA - ZZZ) that have a value other than 'N/A' from a single row to display alongside the basic info. I have 2 queries that separately work find as SQL commands, but I'm looking to merge them together to work in the foreach statement.
Table structure:
ID UserID Date Assignment AAA BBB CCC DDD
1 1 1/27/2014 Test 5.25 N/A 4 N/A
2 4 1/27/2014 Test2 N/A N/A 3.5 2.75
3 1 1/29/2014 Test3 1.25 N/A N/A 4.5
For example, using the info above, the count would be:
ID 1, Count = 3
ID 2, Count = 2
ID 3, Count = 1
PHP Table Code:
foreach ($pdo->query($sql) as $row) {
echo '<tr>';
echo '<td>'. $row['Date'] . '</td>';
echo '<td>'. $row['UserName'] . '</td>';
echo '<td>'. $row['Assignment'] . '</td>';
echo '<td>'. $row['Count'] . '</td>';
echo '</tr>';
}
}
Retrieval query:
$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
FROM `db_log`
INNER JOIN `db_users` ON
db_log.UserID=db_users.ID
ORDER BY `ID` DESC LIMIT 0,20";
Alternative query:
$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
FROM `db_log`, `db_users`
WHERE db_log.UserID=db_users.ID
ORDER BY `ID` DESC LIMIT 0,20";
Count Query:
Note that the ID
=1 should be the id from the first query:
SELECT COUNT(AAA) FROM (
SELECT `AAA` FROM `db_log` WHERE `AAA` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `BBB` FROM `db_log` WHERE `BBB` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `CCC` FROM `db_log` WHERE `CCC` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `DDD` FROM `db_log` WHERE `DDD` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `EEE` FROM `db_log` WHERE `EEE` <> 'N/A' AND `ID`=1) AS A
I've looked into joins, and other workarounds, but to little luck. Thanks in advance for your help :)