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 — C3roe
– C3roe, Commented Jun 30, 2017 at 7:52

Exprator · Accepted Answer · 2017-06-30 07:47:36Z

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, 2017 at 7:47

answered Jun 30, 2017 at 7:41

Exprator

27.8k6 gold badges54 silver badges64 bronze badges

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

12 Comments

Josh Poor Over a year ago

It echo the movie title of the 1st row

Josh Poor Over a year ago

Fatal error: Uncaught Error: Call to undefined method PDOStatement::fetchRow() in

Josh Poor Over a year ago

Here answer is 10, because Limit is 10.

Exprator Over a year ago

so what you want? it will return according to limit, if you need all remove the limit from query

Josh Poor Over a year ago

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?

|

Sujal Patel · Accepted Answer · 2017-06-30 07:45:51Z

0

Try rowCount() for count number of rows

$totalrows = $stmt->rowCount();

answered Jun 30, 2017 at 7:45

Sujal Patel

2,5402 gold badges21 silver badges40 bronze badges

2 Comments

Josh Poor Over a year ago

Here answer is 10, because Limit is 10.

Josh Poor Over a year ago

Updated my question, see now

perseusl · Accepted Answer · 2017-06-30 08:26:37Z

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, 2017 at 8:26

answered Jun 30, 2017 at 8:19

perseusl

3481 silver badge14 bronze badges

4 Comments

Josh Poor Over a year ago

Fatal error: Uncaught Error: Call to undefined method PDOStatement::prepare() in

Josh Poor Over a year ago

Fatal error: Uncaught Error: Call to undefined method PDO::execute() in . In my code $conn connects to the database

perseusl Over a year ago

did you add the SQL_CALC_FOUND_ROWS in your original statement?

Josh Poor Over a year ago

Now, i did...but Fatal error: Uncaught Error: Call to undefined method PDO::execute() in line 149. This is line 149 ` $conn->execute();`

People · Accepted Answer · 2022-01-02 19:58:58Z

0

    //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;

answered Jan 2, 2022 at 19:58

People

13 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

12 Comments

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related