CODEWITHSUNDEEP
phpmysqlcountcategories
Zokhid Juraev
Zokhid Juraev

Reputation: 39

Count posts as categories PHP & MYSQL

I've this code...

$lesson = mysql_query("SELECT * FROM categories WHERE gr='exrcs' ORDER BY title");
$flesson = mysql_fetch_array($lesson);
do {
    printf("<a href='exrcs?cat=%s'>%s</a>",$flesson['id'],$flesson['title']);
   } while ($flesson = mysql_fetch_array($lesson));

This code will be "HTML, CSS, PHP" and other...

But I want this "HTML (2), CSS (14), PHP(8)" and other...

Here (2),(14),(8) amount posts on categories

Upvotes: 1

Views: 277

Answers (3)

Shahid Ahmad
Shahid Ahmad

Reputation: 774

"SELECT CONCAT(C.title,'(',COUNT(*),')') FROM categories C INNER JOIN post P ON C.ID= P.cat_id GROUP BY c.title"

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Use count and group by 

"SELECT cat as title , count(*)  FROM categories 
WHERE gr='exrcs' group by title  ORDER BY title"

Upvotes: 0

Dipanwita Kundu
Dipanwita Kundu

Reputation: 1667

Update your query:

SELECT title, count(id) total FROM categories WHERE gr='exrcs' group by title ORDER BY title

or 

SELECT concat(title,' (', count(id) ,')') as title FROM categories WHERE gr='exrcs' group by title ORDER BY title

Upvotes: 1

Related Questions