CODEWITHSUNDEEP
phpmysql
Josh Poor
Josh Poor

Reputation: 533

Counting the number of rows in MySQL table (PHP)

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)

Upvotes: 1

Views: 76

Answers (2)

Chandana Kumara
Chandana Kumara

Reputation: 2655

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();
}

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

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']  ;

}

Upvotes: 1

Related Questions