How to count the number of rows in MySQL using PHP PDO

Question

This is my code:

<?php

function result($conn) {
      global $number;

    $sql = 'SELECT movie_name FROM movie_info LIMIT 3 OFFSET 0';
    foreach ($conn->query($sql) as $row) {
        print " ". $row['movie_name'] . " <br> ";

    }
}
result($conn);
?>

I am really confused, how to count the number of rows in the table and print it?

Where are you trying to count? Where is `$number` used? `$conn->query` returns a result object, you should `fetch` it. — chris85, Jun 03 '17 at 10:44
I myself am new but I think https://stackoverflow.com/questions/1893424/count-table-rows might help you. — MadLordDev, Jun 03 '17 at 10:46
Possible duplicate of [PHP PDO - Num Rows](https://stackoverflow.com/questions/2700621/php-pdo-num-rows) — Jenne, Jun 03 '17 at 10:47
`SELECT COUNT(movie_name)` (most reliable), or PDOs `rowCount()` (this might not always work; read the manual), there are many possibilities. — Qirel, Jun 03 '17 at 10:47

score 1 · Accepted Answer · answered Jun 03 '17 at 11:04

1

If you have a PDO Object with a PDO connection to your database - (Let´s call it for example $con) You can prepare a statement like

     $stmt = $con->prepare('SELECT movie_name FROM movie_info LIMIT 3 OFFSET 0');

     // Then fire it up
     $stmt->execute();

     // Pick up the result as an array
     $result = $stmt->fetchAll();

     // Now you run through this array in many ways, for example
     for($x=0, $n=count($result); $x < $n; $x++){
         echo $result[$x]['movie_name'];
     }

The count($result) keeps your number of result

answered Jun 03 '17 at 11:04

J P

76
3

If you want to do your output and you need to know how many rows you have to do, I think this example is the easiest way. And a little side effect is that a for loop has a better performance than a foreach. – J P Jun 03 '17 at 11:50
You just need to use $count = $stmt->rowCount(); to get the affected rows back. – J P Feb 01 '21 at 17:56

score 0 · Answer 2 · edited Jun 03 '17 at 16:14

0

I guess you can do this

SELECT count(*) FROM movie_info

the code above will return the count of data stored in that table,

edited Jun 03 '17 at 16:14

Jorge Y. C. Rodriguez

3,394
5
38
61

answered Jun 03 '17 at 10:47

Joefrey Ramos

12
2

score 0 · Answer 3 · edited Feb 12 '19 at 08:31

0

Here’s my suggestion

$moviescount = $db->query('SELECT COUNT(`movie_name`) FROM `movie_info` LIMIT 3 OFFSET 0')->fetchColumn();

and

echo moviescount; // for example 10034

edited Feb 12 '19 at 08:31

Eric Aya

69,473
35
181
253

answered Feb 12 '19 at 08:19

Reham Fahmy

4,937
15
50
71

How to count the number of rows in MySQL using PHP PDO

3 Answers3