[SOLVED] Counting Certain Entries in a Database

[defonic]

Hello, first off I would like to say hello!

I have a mySQL question regarding counting and getting a sum.

I run a streaming cartoon video site, I paid a freelancer to make the script now he is MIA. I have a database table named 'categories' inside 'categories' is:

id, title

eg; (id)0001 (title)Comics, (id)0002 (title)Funnies

 

 

Is there a way to count how many "id's" there under a specific title?

I want to be able to display how many videos are in each category.

Can someone help me with accomplishing this, or maybe tell me where to look? I am not looking for a free handout I am just trying to figure out where to begin. Thanks!

[Ken2k7]

SELECT COUNT(*) cnt
FROM categories
GROUP BY title;

 

Try that.

[pkedpker]

SELECT COUNT(*) cnt
FROM categories
GROUP BY title;

 

Try that.

 

Lol must be your brain fart again  I think you met

 

SELECT COUNT(*) AS cnt FROM categories GROUP BY title;

 

 

not to be a asshole just helping you out

[Ken2k7]

They're both the same. Both work.

[defonic]

Thanks for everyones help, I am new to SQL so heres what I have, I am trying to output it now like so:

 

<?php
include("includes/config.php");
$result=mysql_query("SELECT COUNT(*) AS cnt FROM category GROUP BY title;");


while($row=mysql_fetch_assoc($result)){
?>
     <title><?=$row['title']; ?></title> <id><?=$row['id']; ?></id>
<?
}
?>

 

Does not seem to work, am i doing something wrong here?

[Ken2k7]

Is the table category or categories?

[defonic]

the template is using smarty here is the line I have to reference:

{foreach from=$array_categories item=category name=categ}

<a href="./categ/{$category.id}/{$category.title}/" class="link"><b>{$category.title}

 

I am assuming it is category

[Ken2k7]

Uh.. no, completely wrong. You'll have to find where $array_categories is defined and from there, find the SQL it uses.

 

For example -

<?php
$array = array(1,2,3,4,5);
foreach ($array as $num) {
     // something here
}

 

item=category is like $num up there. It just represent the item. It doesn't tell you what the item is or how it was derived.

[fenway]

I don't see why a simple count() and group by won't suffice.

[defonic]

Ok thanks for everyone's help, again I am new at this and need to figure this out on my own since my programmer went MIA. After better research this is what I have.

 

Table videos

under the videos table I have

id, title, date_added, category

 

So the category is a number, so I may have "comic book #4, 76" 76 being a category. So i need to count how many 76 there are and so on.

This is I need to output this in a simple fashion.

[Ken2k7]

SELECT COUNT(*) cnt
FROM videos
GROUP BY category;

[defonic]

Yes this gives me an output off the count in each category, this is what I have.

 

<?php
include("includes/config.php");
$result=mysql_query("SELECT COUNT(*) cnt FROM videos GROUP by category;");

while($row=mysql_fetch_assoc($result)){
?>
<?=$row['cnt']; ?> videos in category <?=$row['category']; ?>
<?
}
?>

 

 

<?=$row['category']; ?> above doesn't show up.

How would I do 2 select statements to be able to capture 'category'

I want it to display like "112 videos in category 72"

[J.Daniels]

You need to return the category field:

 

SELECT COUNT(*) cnt, category
FROM videos
GROUP BY category;

[defonic]

Thanks for everyone's help!