PHP MySQL Query Select Count

Question

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";
    };

Why are you also selecting UserName? That will just pick an arbitrary username from the table. Why do you use a loop to print the results, since there's just one row with the total for the entire table. Did you mean to group the data in some way? — Barmar
– Barmar, Commented Apr 1, 2016 at 1:23
I am actually trying to count the values that are NOT NULL and NOT EMPTY. I just need a single 'total' number to display. I guess I don't really need a loop to print the results then. I don't need to group the data, I just need to count how many records in the table have the defined value 'UserName'. — Luke
– Luke, Commented Apr 1, 2016 at 1:33
I also just tried this but still no output $result=mysql_query("SELECT SUM(UserName IS NOT NULL OR TRIM(UserName) != '') FROM Employee"); $Count=mysql_fetch_assoc($result); echo "Total: $Count"; — Luke
– Luke, Commented Apr 1, 2016 at 1:40

Barmar · Accepted Answer · 2016-04-01 02:08:40Z

1

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";

edited Apr 1, 2016 at 2:08

answered Apr 1, 2016 at 1:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL Query Select Count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related