0
$sql = "SELECT count(name) FROM `TABLE` WHERE name='$name' ";
$sth=$conn->prepare($sql);
$totalrows = $sth->fetch(PDO::FETCH_ASSOC);

echo "$totalrows";

This is my code to count the total number of rows using PHP PDO Prepare statement, but $totalrows echo nothing, it has no value. What is the mistake in this code?

Josh
  • 121
  • 2
  • 9
  • 1
    You need to `$sth->execute();` (see manual http://php.net/manual/en/pdostatement.execute.php Example #2) and you may need to bind parameters...depends on where `$name` comes from. Also use `print_r($totalrows);` not `echo $totalrows;`. `$totalrows` is going to be an array with a key like `$totalrows['count()']` or similar. – Rasclatt Jun 12 '17 at 03:36
  • Where is this horrid code coming from? https://stackoverflow.com/questions/44485101/how-to-count-rows-from-mysql-with-pdp-pdo-prepared-statement – chris85 Jun 12 '17 at 04:36

5 Answers5

2

You need:

# USE "as count" here so it's easy to reference
$sql = "SELECT count(name) as count FROM `TABLE` WHERE name = :name";
# prepare as you have
$sth = $conn->prepare($sql);
# Bind parameters while executing
$sth->execute(array(':name'=>$name));
# Fetch the associate array
$totalrows = $sth->fetch(PDO::FETCH_ASSOC);
# Echo the count
echo $totalrows['count'];

Review Example #2 from the manual on preparing, binding, and executing.

Rasclatt
  • 12,498
  • 3
  • 25
  • 33
  • The better approach is to use the `GROUP BY ` clause with `COUNT()` if you only want to get the total number of records. – itzmukeshy7 Jun 12 '17 at 04:05
1

Try 

$sql = "SELECT count(name) FROM `TABLE` WHERE name=? ";
$stmt=$conn->prepare($sql);
$stmt->execute(array($name));
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$rows = $stmt->fetchAll();

Now $rows is an array containing the result so 

echo count($rows);
itzmukeshy7
  • 2,669
  • 1
  • 21
  • 29
Dennis Nolan
  • 39
  • 3
0

If you are interested in total number of records only then you should not fetch all the records from the database try the GROUP BY clause and then use COUNT()

$sql = 'SELECT COUNT(name) AS totalRecords FROM `TABLE` WHERE name = ? GROUP BY name';
$stmt = $conn->prepare($sql);
$stmt->execute(array($name));
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$row = $stmt->fetch(); /* This will return a single record. */

echo 'Total records: ' . $row['totalRecords'];
itzmukeshy7
  • 2,669
  • 1
  • 21
  • 29
-2

To get the count modify the code as: Replace

$totalrows = $sth->fetch(PDO::FETCH_ASSOC);

With

$totalrows = $sth->fetchColumn();
Rohan Julka
  • 1
  • 3
-2

Use count after the query and use bind_param so you don't receive input directly from user:

$stmt = $conn->prepare("SELECT 'something' FROM 'somewhere' WHERE 'name' =  :name;

//get the user input thru a method and bind it to :name

private $name;

getName($name){

$this->name = $name;
}

$stmt->bindParam(':name');

 $stmt->execute();
Rasclatt
  • 12,498
  • 3
  • 25
  • 33
bcrypt
  • 56
  • 3