CODEWITHSUNDEEP
phpmysqlpdo
user2333968
user2333968

Reputation: 135

PHP - rowCount() giving the same value regardless if the string exists or not

I have the following code:

$queryMobileNumber = $dbh->prepare("SELECT mobile_number FROM $tableBusinessOwner WHERE business_owner_id = $businessOwnerIDTable");
$queryMobileNumber->bindValue( 1, $mobileNumberNew);
$queryMobileNumber->execute();

echo $businessOwnerIDTable;
echo '-';
echo $queryMobileNumber->rowCount();

I connect to my database using PDO.

The above checks whether a mobile number inserted by the user already exists in the table or not. Regardless if the number exists or not when I echo $queryMobileNumber->rowCount(); the value is always 1.

I am not sure what I'm missing. I am not getting any error in my error_log.

Upvotes: 0

Views: 322

Answers (3)

Amani Ben Azzouz
Amani Ben Azzouz

Reputation: 2565

You have always to fetch any query you execute in order to get the query's data.. Try this

$queryMobileNumber->execute();

$result = $queryMobileNumber->fetchAll();
echo $result->rowCount();

see documentation

The rowCount of the query you execute (select, insert, delete) will return how many times your query was executed

Upvotes: 0

t4nz
t4nz

Reputation: 59

As indicated in this example: http://php.net/manual/en/pdostatement.rowcount.php#example-1009

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {
/* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {
   ...

If you just want to check if a number exists, the procedure count is less demanding for mysql

Upvotes: 0

STT LCU
STT LCU

Reputation: 4330

As explained by the PHP documentation

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Since you're doing a select, rowCount() is not the function you're looking for.

What can you do instead?

<?php

$queryMobileNumber = $dbh->prepare("SELECT mobile_number FROM $tableBusinessOwner WHERE business_owner_id = $businessOwnerIDTable");
$queryMobileNumber->execute();

$res = $queryMobileNumber->fetch(PDO::FETCH_ASSOC);

echo $businessOwnerIDTable;
echo '-';
if(!empty($res['mobile_number')){
  echo $res['mobile_number'); //or whatever else
} else {
  echo 'N/A';
}

Upvotes: 2

Related Questions