rowCount in mysql dealing with certain columns with values

Question

Can someone help me rowCount() when I have values and empty values in mysql

for example:

id        column1          column2           idcolumn
1           2                                   1  
2           3                                   1
3           1                                   1
4           0                                   1


$CountQuery = "SELECT column1 FROM follow WHERE idcolumn= ?";
$Count = $dbh->prepare($CountQuery);
$Count -> execute(array($idcolumn)); //where $idcolumn = 1
$num_1 = $Count->rowCount();
echo $num_1;

this should give me a count of 4, but I'm trying to distinguish between column1 and column2, so I can find counts where if I wanted to create a query with $num_2, it should give me a count of 0.

Answer 1

From manual, ROW_COUNT() doesn't work for SELECT queries.

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful.

Alternatively you can use:

SELECT SQL_CALC_FOUND_ROWS column1 FROM follow WHERE idcolumn= ?

SELECT FOUND_ROWS();

you can use this query to get count of non-null values:

SELECT column(column) FROM follow WHERE idcolumn= ?;

or you can use this query to get count of all rows:

SELECT column(1) FROM follow WHERE idcolumn= ?;

Answer 2

Use COUNT(column) to count the number of rows with non-NULL values in that column.

$CountQuery = "SELECT COUNT(column1) AS `count1`, COUNT(column2) AS `count2` FROM follow WHERE idcolumn = ?";
$Count = $dbh->prepare($CountQuery);
$Count->execute(array($idcolumn)); //where $idcolumn = 1
$row = $Count->fetch(PDO::FETCH_ASSOC);

$num_1 = $row['count1'];
$num_2 = $row['count2'];