PHP count number of entries in a SQL table

Question

I have a launch page that accepts emails. I am trying to count the total number of emails in the table. However, I am struggling to get it to work. The sql seems to be accurate, because I ran the sql script in phpmyadmin and it returned the correct value. However, when I do a var_dump of the variable that I assigned the sql script it does not return the correct value. Any suggestions?

PHP

require(ROOT_PATH . "inc/database.php");
try {
    $query = $db->prepare("REPLACE INTO launch_email VALUES ('$email')");
    $query->execute();
    $editEmail = $db->exec("SELECT COUNT(email) FROM launch_email");
    var_dump($editEmail);
} catch (Exception $e) {
    echo "Data could not be submitted to the database.";
exit;
}

Sidenote: You have a missing closing brace } after exit; --- Try and show the error message itself using exit( $e->getMessage() ); — Funk Forty Niner
– Funk Forty Niner, Commented Apr 14, 2014 at 18:08
You can use AS value_name to assign a name and access, otherwise it will be returned as key-pair COUNT(email). So: SELECT COUNT(email) AS ttl_emails FROM launch_email — Patrick Moore
– Patrick Moore, Commented Apr 14, 2014 at 18:09
@Fred-ii- Sorry I didn't highlight and select all of the code from my text editor. It has a closing bracket. — Ryan Salmons
– Ryan Salmons, Commented Apr 14, 2014 at 18:18

Parag Tyagi · Accepted Answer · 2014-04-14 18:11:41Z

1

$editEmail = $db->query("SELECT COUNT(email) as email_count FROM launch_email");    
$fet = $db->fetch();

if($fet['email_count'] > 0)
{
   // Email exists
}
else
{
    // Email does not exist
}

answered Apr 14, 2014 at 18:11

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Salmons Over a year ago

PHP Fatal error: Call to undefined method PDO::fetch()

Shaunak Kashyap · Accepted Answer · 2014-04-14 18:12:22Z

1

I'm assuming $db is an instance of PDO.

The problem is that you are using the exec method of the PDO class, which simply returns the number of rows affected by the specified SQL query.

You will need to use the query method instead. That will return an instance of PDOStatement. If you call the fetchColumn method on this instance, you will get the desired result.

answered Apr 14, 2014 at 18:12

Shaunak Kashyap

5983 silver badges6 bronze badges

Comments

Mureinik · Accepted Answer · 2014-04-14 18:28:59Z

1

PDO::exec() does not return results from a select statement - you should use PDO::query instead:

require(ROOT_PATH . "inc/database.php");
try {
    $query = $db->prepare("REPLACE INTO launch_email VALUES ('$email')");
    $query->execute();

    // dummy traversable - it'll only loop once
    for ($db->query("SELECT COUNT(email) AS cnt FROM launch_email") as $row) {
        print $row['cnt'];
    }
} catch (Exception $e) {
    echo "Data could not be submitted to the database.";
}
exit;

edited Apr 14, 2014 at 18:28

answered Apr 14, 2014 at 18:11

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

Ryan Salmons Over a year ago

I got this error on the print $row['cnt'] line; PHP Fatal error: Cannot use object of type PDOStatement as array...

Mureinik Over a year ago

@RyanSalmons arg, yeah, forgot query doesn't return an array but a traversable PDOStatement. Fixed.

Ryan Salmons · Accepted Answer · 2014-04-14 20:36:44Z

1

So I was able to figure it out...This is what I needed:

PHP

function get_count($count_id, $count) {

    $output = "";

    $output = $output . $count["email_count"];

    return $output;
}


try {
    $totalEmail = $db->query("SELECT COUNT(email) as email_count FROM launch_email");
} catch (Exception $e) {
    echo "Date could not be retrieved from database.";
    exit;
}
$countTotal = $totalEmail->fetchAll(PDO::FETCH_ASSOC);

foreach($countTotal as $count_id => $count) { 
    echo get_count($count_id,$count);
}

answered Apr 14, 2014 at 20:36

Ryan Salmons

1,46910 gold badges23 silver badges44 bronze badges

Comments

Parimal · Accepted Answer · 2014-04-14 18:24:42Z

0

If you have to return count then you can use "print_r($editEmail)" also please check it.

Or try this one

$sql = "SELECT COUNT(email) FROM launch_email"; 
$result = $db->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn(); 
echo $number_of_rows;

edited Apr 14, 2014 at 18:24

answered Apr 14, 2014 at 18:18

Parimal

1661 silver badge11 bronze badges

Collectives™ on Stack Overflow

PHP count number of entries in a SQL table

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related