CODEWITHSUNDEEP
phpmysqliprepared-statement
Luke
Luke

Reputation: 527

PHP MySQL Query Select Count

I am having a problem with the following query that should count Null and Not Empty records in database. I need to use a prepared statement to execute. I have the following code but I cant get the correct output. Any help would be greatly appreciated! 

$query = "SELECT UserName, COUNT(NULLIF(TRIM(UserName), ''))
FROM    Employee";
    $stmt = $db->prepare($query7);
    $stmt->execute();
    $stmt->store_result();
    $numrows = $stmt->num_rows;
    $stmt->bind_result($Count);
    for ($i=0; $i <$numrows; $i++) {
        $stmt->fetch();

        echo "Count: $Count";
    };

Upvotes: 1

Views: 881

Answers (1)

Barmar
Barmar

Reputation: 780909

To count non-null and non-empty records, you can do:

SELECT COUNT(*)
FROM Employee
WHERE UserName IS NOT NULL AND UserName != ''

You don't need to use TRIM(UserName), because trailing spaces are ignored when comparing strings.

The full PHP code should be like this:

$query = "SELECT COUNT(*)
            FROM Employee
            WHERE UserName IS NOT NULL AND UserName != ''";
$stmt = $db->prepare($query);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($Count);
$stmt->fetch();
echo "Count: $Count";

Upvotes: 1

Related Questions