How to count number of rows in MySQL table (PHP PDO)

Question

This is my code

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id, count (tmdb_id),GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

JOIN genres USING (tmdb_id)

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

HAVING find_in_set('$category1', genres_name) AND find_in_set('$category2', genres_name) 

LIMIT $limit OFFSET $start"); 


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

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

Since i cannot calculate total number of rows which include $category1 and $category2 in the same code, I added this code before it.

$sql = "SELECT count(tmdb_id),group_concat(genres.genres_name) AS genres_name

 FROM `tmdb_movies` 

JOIN genres USING (tmdb_id)
GROUP BY tmdb_movies.tmdb_id
HAVING find_in_set('$category1', genres_name) AND find_in_set('$category2', genres_name) "; 

$result = $conn->prepare($sql); 
$result->execute(); 
$totalrows = $result->rowCount();
echo $totalrows;

But $totalrows echo 3 here. But their a lot more rows than 3.

Possible duplicate of [Find total number of results in mySQL query with offset+limit](https://stackoverflow.com/questions/5928611/find-total-number-of-results-in-mysql-query-with-offsetlimit) — CBroe, Jun 30 '17 at 07:52

Exprator · Accepted Answer · 2017-06-30T07:47:36.877

1

you are overriding the $stmt after execute using fetchAll()

so instead try this

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

edited Jun 30 '17 at 07:47

answered Jun 30 '17 at 07:41

Exprator

26,992
6
47
59

It echo the movie title of the 1st row – Josh Poor Jun 30 '17 at 07:42
`Fatal error: Uncaught Error: Call to undefined method PDOStatement::fetchRow() in ` – Josh Poor Jun 30 '17 at 07:44
Here answer is 10, because Limit is 10. – Josh Poor Jun 30 '17 at 07:49
so what you want? it will return according to limit, if you need all remove the limit from query – Exprator Jun 30 '17 at 07:50
I cannot remove limit. I want to count all the rows in my database to make pagination system. So i need to make another select statement to count this without limit? – Josh Poor Jun 30 '17 at 07:51
yes, for pagination just use select * from tablename; and then count the rows totaal then use it – Exprator Jun 30 '17 at 07:53
I want to count only those rows which include $category1 and $category2 @exprator, see my code – Josh Poor Jun 30 '17 at 07:57
so bro write a query like that just dont keep limit to it, means another sql query for pagination and do the count. thats all – Exprator Jun 30 '17 at 07:59
I tried it but i have a problem with it also. Wait, i will update my question. – Josh Poor Jun 30 '17 at 08:00
use rowCount instead of fetchColumn and see how many it returns – Exprator Jun 30 '17 at 08:08
Done and Hmm...This is wired. It echo 9 rows more than total rows. I have 137 rows, it echo 146.. – Josh Poor Jun 30 '17 at 08:12
that you need to check with the sql. run the sql in phpmyadmin and check how many it returns – Exprator Jun 30 '17 at 08:13

score 0 · Answer 2 · answered Jun 30 '17 at 07:45

0

Try rowCount() for count number of rows

$totalrows = $stmt->rowCount();

answered Jun 30 '17 at 07:45

Sujal Patel

2,444
1
19
38

Here answer is 10, because Limit is 10. – Josh Poor Jun 30 '17 at 07:49
Updated my question, see now – Josh Poor Jun 30 '17 at 08:06

perseusl · Answer 3 · 2017-06-30T08:26:37.897

0

try this

$stmt = $conn->prepare("SELECT SQL_CALC_FOUND_ROWS tmdb_movies.movie_title,tmdb_movies.tmdb_id, count (tmdb_id),GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name

FROM tmdb_movies

JOIN genres USING (tmdb_id)

GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

HAVING find_in_set('$category1', genres_name) AND find_in_set('$category2', genres_name) 

LIMIT $limit OFFSET $start"); 


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

 // Pick up the result as an array
 $result = $stmt->fetchAll();
 $conn->prepare('SELECT FOUND_ROWS() as COUNT');
 $conn->execute();
 $count = $conn->fetchColumn();
 echo $count; //here's your total count

edited Jun 30 '17 at 08:26

answered Jun 30 '17 at 08:19

perseusl

348
1
14

`Fatal error: Uncaught Error: Call to undefined method PDOStatement::prepare() in ` – Josh Poor Jun 30 '17 at 08:25
`Fatal error: Uncaught Error: Call to undefined method PDO::execute() in ` . In my code $conn connects to the database – Josh Poor Jun 30 '17 at 08:29
did you add the `SQL_CALC_FOUND_ROWS` in your original statement? – perseusl Jun 30 '17 at 08:32
Now, i did...but `Fatal error: Uncaught Error: Call to undefined method PDO::execute()` in line 149. This is line 149 ` $conn->execute();` – Josh Poor Jun 30 '17 at 08:35

score 0 · Answer 4 · answered Jan 02 '22 at 19:58

    //This is the best answer ever to this questions since mysqli is deprecated     
    //this is the code to get the total number of rows
         $handler = new PDO(DB_HOST, DB_USERNAME, DB_PASSWORD); $stmt = $handler->prepare('SELECT COUNT(*) AS totalRows FROM table_name '); 
         $stmt->execute(); 
         $row = $stmt->fetch(PDO::FETCH_ASSOC); 
         $totalRows = $row['totalRows']; ?> 
         //this prints the total number of rows
        echo $totalRows;

How to count number of rows in MySQL table (PHP PDO)

4 Answers4

Related