php统计某些作者在表中发布

Probably pretty simple, but I am having a few issues.

All I am trying to do is "count" how many posts a "certain" "author" has from a certain "category"

Table set up:

id | author | category | full | short

1      jim       2,3      ...     ...
2      josh      5,9      ...     ...
3      jim       3,9      ...     ...
4      jim       1,9      ...     ...
5      josh      3,4      ...     ...
6      trina     2        ...     ...

I know how to query..

But, how do I code a "foreach"

to count how many posts if I query (example query)

WHERE category = '9' AND author = "$author"

So it would show me the posts by that author, which is no big deal, but how would I show :

if $author = "jim"

Jim (2 posts in Category 9)

or if $author = "josh"

Josh (1 post in Category 9)

Thanks in advance!!

Here is what I used, thanks !!

<?php echo $row_Recordset1['author']; echo '(' . $totalRows_Recordset1 . ')'; ?>

you can do it by sql query only no foreach to calculate. not tested by you can do like this

(not tested)

SELECT * FROM table_name WHERE category LIKE '%,9' OR category LIKE '9,%' OR category LIKE '9' OR category LIKE '%,9,%'

then you can directly count it by

mysql_num_rows

You could create a multi-dimensional array that has the author as an index, and each author index can be another array of categories and how many posts.

$postCounts = array();
foreach($results as $result) {
    if (!array_key_exists($result['author'], $postCounts)) {
        $postCounts[$result['author']] = array();
    }

    if (!array_key_exists[$result['category'], $postCounts[$result['author']])) {
        $postCounts[$result['author']][$result['category'] = 1; // first post seen
    } else {
        $postCounts[$result['author']][$result['category'] += 1; // increment count
    }
}

From looking at your category results though, you may need to explode them by comma and add the second array_key_exists block in a loop. But that could do what you want in a PHP foreach.

In the end you get an array like this:

$postCounts = Array(
    'jim' => Array(9 => 2) // 2 posts in category 9 for jim
    'josh' => Array(9 => 1) // 1 post in category 9 for josh
    'bob' => Array(9 => 3, 2 => 5) // 3 posts in category 9, 5 posts in category 2.
)