CODEWITHSUNDEEP
phpmysqlsql
Goro
Goro

Reputation: 499

Counting items in table

How can I count how many items have in each category. I use this to show them. 

$q = mysqli_query($con, "SELECT  id, name FROM cat_gif ORDER BY name");
        while ($res = mysqli_fetch_assoc($q))
        {
            echo '<div id="cat"><a href="gif_cat.php?id='. $res['id'] .'">&raquo '.$res['name'].'</div></a><br/>';
        }

I know I can use COUNT but I don't know where and how to use it.

update: Table cat_gif rows are

id
name

Table gifs rows are

id
caption
name
size
type
file_path
gif_cat

Upvotes: 0

Views: 165

Answers (7)

Jason Paddle
Jason Paddle

Reputation: 1103

One way to do it

        $q = mysqli_query($con, "SELECT gif_id, gif_name, (SELECT COUNT(*) FROM gifs WHERE gifs.gif_cat = gif_id) AS count FROM cat_gif ORDER BY gif_name");
        while ($res = mysqli_fetch_assoc($q))
        {

            echo '<div id="cat"><a href="gif_cat.php?gif_id='. $res['gif_id'] .'">&raquo '.$res['gif_name'].' ('.$res['count'].') </div></a><br/>';
        }

Upvotes: 0

IGT
IGT

Reputation: 21

Hope this will help,

SELECT category_name, COUNT(id) FROM cat_gif GROUP BY category_name;

Please use the category name in the category_name(i.e., the column name).

Upvotes: 1

Shrikant Gupta
Shrikant Gupta

Reputation: 127

Use this. it's would works sure.

SELECT category, count(id) FROM cat_gif group by category;

Upvotes: 1

i100
i100

Reputation: 4666

try something like this

SELECT  count(name) FROM cat_gif group by __your_category_column__;

replace your_category_column with actual name of the column.

Upvotes: 2

Rakesh Sharma
Rakesh Sharma

Reputation: 13728

count rows like

$row_cnt = $q->num_rows;

for more info https://www.php.net/mysqli_num_rows

also you can use count()

SELECT category, count(id) FROM cat_gif GROUP BY category

Upvotes: 1

lalameat
lalameat

Reputation: 754

SELECT category, count(id) FROM cat_gif GROUP BY category

Upvotes: 2

Satish Sharma
Satish Sharma

Reputation: 9635

you can use count(name) with GROUP BY name in your query as follows which gives you count for each categroy

$q = mysqli_query($con, "SELECT   name, count(name) as cnt FROM cat_gif GROUP BY name");
while ($res = mysqli_fetch_assoc($q))
{
    $count = $res['cnt']; // your count  
    $name = $res['name']; // your category name

    echo "Category Name : ".$name." --> ".$count."<br/>";
}

Upvotes: 2

Related Questions