CODEWITHSUNDEEP
phpmysql
RegEdit
RegEdit

Reputation: 2162

MySQL count number of matching rows always returns 1

I've checked the SQL below in mySQL and it returns the correct count of products that the user has. I'm trying to move the statement into a PHP to execute conditional code based on the product count. However, the echo always returns 1. What am I missing?

$sql = "select count(product_id) from dap_users_products_jn where product_id not in(13,34) and user_id = 1";
$stmt = $dap_dbh->prepare($sql);
$stmt->execute();
$stmt = $stmt->rowCount();
echo "the product count is: ".$stmt;

Upvotes: 0

Views: 228

Answers (2)

Subin
Subin

Reputation: 3563

What you should do is, get the column. PDO have the fetchColumn function which gets the columns. You should use it instead of $stmt->rowCount()

$sql = "select count(product_id) from dap_users_products_jn where product_id not in(13,34) and user_id = 1";
$stmt = $dap_dbh->prepare($sql);
$stmt->execute();
$stmt = $stmt->fetchColumn();
echo "the product count is: ".$stmt;

Since SQL already fetched the row count why use PDO's rowCount() ?

Upvotes: 1

John Conde
John Conde

Reputation: 219804

You will always get one row returned when using COUNT() this way as you have to get a result set even if the result of COUNT() is zero. Or else how will you know what the results are? If you want the results of COUNT() you need to get the results of your query as normal and then check the value of the COUNT() operation.

$sql = "select count(product_id) AS prod_count from dap_users_products_jn where product_id not in(13,34) and user_id = 1";
$stmt = $dap_dbh->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_OBJ);
echo "the product count is: ".$result->prod_count;

Upvotes: 3

Related Questions