Can anyone please help me get the count of the filled MySQL columns?

For example: My Table Structure:

In the above table, I have two Rows, and both rows contain one Null and One Empty Value, I just need to get the Count of each row that doesn't have NULL OR EMPTY Value for a specific ID.

Expected Result:

WHERE ID=1, the result would be:
Count = 2 (ID, FName)

WHERE ID=2, the result would be:
Count = 2 (ID, LName)

**I want to use this in WordPress

CodePudding user response：

So, after searching for an hour or two.

I'm using this as my solution but I'm not sure if it's good regrading the optimization. Here is what I'm using:

function ranking_mechanism( $currentUserID ){
global $wpdb;
$rank = 0;

$results = $wpdb->get_row(
    $wpdb->prepare('SELECT * FROM TABLE WHERE ID=%d', $currentUserID)
); 

$finalArr = (array) $results; // Convert $results as Array(to perform iteration), it(WordPress DB Query) typically return Object by Default 
foreach ( $finalArr as $key => $Table ) {
    if( $finalArr[$key] == '' || $finalArr[$key] == 'NULL' ){
        // Nothing to do 
    }else{
        $rank = $rank 1; // Increase Rank If Found Value
    }
    return $rank;
}

CodePudding user response：

SELECT ID,   (ID IS NOT NULL) /* assuming that ID is numeric and cannot be empty string */
             (FName <> '' AND FName IS NOT NULL) 
             (LName <> '' AND LName IS NOT NULL) AS `Count`
FROM source_table
-- WHERE ID = ???