PHP PDO - SELECT COUNT(*)

[Berk]

Hey,
So I'm trying to get messages from contact table and where is_read = 0.
My code is:

$countMSG = $db->prepare("SELECT COUNT(*) FROM contact WHERE is_read = 0"); 
$countMSG->execute();
$countMSG = $countMSG->fetchAll();

foreach ($countMSG as $row) {
   $numMSG = $row[0];
}

But when I

 echo $numMSG;

returns nothing.

Any clues why?

 

[JayC]

Why are you putting a foreach loop around a count variable? And you know you can google too right?

You must be registered for see links

<?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) {

        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}
?>

 

[Berk]

Why are you putting a foreach loop around a count variable? And you know you can google too right?

You must be registered for see links

<?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) {

        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}
?>

Do you think I didn't google?

You must be registered for see links

..
I need to get number of rows, don't need to check it.

 

[JayC]

Do you think I didn't google?

You must be registered for see links

..
I need to get number of rows, don't need to check it.

$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) {

Look at that.. Right there on google...

 

[MayoMayn]

Hey,
So I'm trying to get messages from contact table and where is_read = 0.
My code is:

$countMSG = $db->prepare("SELECT COUNT(*) FROM contact WHERE is_read = 0");
$countMSG->execute();
$countMSG = $countMSG->fetchAll();

foreach ($countMSG as $row) {
   $numMSG = $row[0];
}

But when I

 echo $numMSG;

returns nothing.

Any clues why?

What exactly are you trying to receive with this script? As far as I can see your SQL is entirely different from the rest. Basically you're counting all columns. If you want to return the amount of rows where is_read = 0, you should just use:

$query = $db->query("SELECT `id` FROM `contact` WHERE `is_read` = 0");

$countMSG = $query->rowCount();

echo $countMSG;

No need to use a prepared statement as it's an inwritten SQL value and not a value from an input.

 

[Berk]

What exactly are you trying to receive with this script? As far as I can see your SQL is entirely different from the rest. Basically you're counting all rows. If you want to return the amount of rows that is not read, you should just use:

$query = $db->query("SELECT `id` FROM `contact` WHERE `is_read` = 0");

$countMSG = $query->rowCount();

No need to use a prepared statement as it's an inwritten SQL value and not a value from an input.

Was trying this,

You must be registered for see links

and it worked perfectly. Thanks!