Counting the number of rows in MySQL table (PHP)

Question

This is my code to select and echo data

Select maintable.movie_title, group_concat(genres.genres_name) AS genres_name
FROM maintable
JOIN genres USING (tmdb_id)
GROUP BY maintable.tmdb_id,maintable.movie_title
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();

I have 2 tables

1.) maintable

2.) genres table

Both tables are linked to each other using tmdb_id

(Please do not ask to show, what I tried. Trust me, it will make the question more confusing)

You can use conditional aggregation to get a count of the matching rows. Unfortunately, your query is in a mess, so I can't give a formal answer. You are grouping by tmdb_id but you select movie_title, a non aggregate column. This doesn't make any sense. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 30, 2017 at 5:58
Fixed the question @TimBiegeleisen I just forgot to include that. My real code is 5x bigger than this, but i put only required code — Josh Poor
– Josh Poor, Commented Jun 30, 2017 at 6:01

ScaisEdge · Accepted Answer · 2017-06-30 06:51:46Z

1

if you need also the count of $totalrows, which have $category1 and $category2 You should use a where in clause

  Select maintable.movie_title, group_concat(genres.genres_name) AS genres_name, count(*) as total_rows
  FROM maintable
  JOIN genres USING (tmdb_id)
  where genres.genres_name in ('$category1', '$category2' )
  GROUP BY maintable.tmdb_id, maintable.movie_title

   LIMIT $limit OFFSET $start

if you need only tital_rows you could select only this value (and use $totalrows = $result->fetchColumn(); ) or in this fecth the column 2 using

 $totalrows = $result->fetchColumn(2);

or using fetchAll

$result = $stmt->fetchAll();

foreach($result as $key=>$row){

  echo $row['total_rows']  ;

}

edited Jun 30, 2017 at 6:51

answered Jun 30, 2017 at 6:06

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

6 Comments

Josh Poor Over a year ago

I want to calculate total rows before $limit variable. So i can give a value to it

ScaisEdge Over a year ago

@JoshPoor what do you mean ? .. i don't understand .. update your question add a proper data sample and the expected result ..

Josh Poor Over a year ago

$totalrows = $result->fetchColumn(); how to calculate it here?

Josh Poor Over a year ago

I mean how to calculate total rows and transfer the value of it to a variable?

Josh Poor Over a year ago

Bro the answer is 4316410442449

|

Chandana Kumara · Accepted Answer · 2017-06-30 06:58:32Z

0

Do you want this?

if ($stmt = $mysqli->prepare($query)) {

    /* execute query */
    $stmt->execute();

    /* store result */
    $stmt->store_result();

    printf("Number of rows: %d.\n", $stmt->num_rows);

    /* close statement */
    $stmt->close();
}

edited Jun 30, 2017 at 6:58

answered Jun 30, 2017 at 6:29

Chandana Kumara

2,6651 gold badge25 silver badges25 bronze badges

1 Comment

Josh Poor Over a year ago

Fatal error: Uncaught Error: Call to a member function rowCount() on array in

Collectives™ on Stack Overflow

Counting the number of rows in MySQL table (PHP)

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related