PHP Counting problem...

[smartads]

I really need some help on this one...

I would like to post the "top 10 authors" for a specific category and how many articles they have.

I can't seem to get anything to work properly. Everything I've tried in PHP has blown up and delivered something completely different.

Here's the MySQL table structure...

1) Table for articles (Category code & author ID)
2) Table for categories (Main & Sub-Categories)
3) Table for author acounts (Name, author ID, etc)

I need to show the top 10 authors for a specific category by the amount of articles they have within that category.

For ex. (IT Tech Top 10 Authors)

1) Martin has 100 articles in IT Tech
2) Joe has 88 articles in IT Tech
3) Sam has 49 articles in IT Tech
4) and so on...

If anyone could help, I would appreciate it!

[smartads]

If anyone helps to accomplish this small but useful script, I will add a website of their choice within every page that this script is used. I will keep your website listed and active for up to 1 year.

Ex. This "Top 10 Authors" script was provided by: yoursite.com

[Easywebdev]

I'm off to bed so I havent time to write out a complete script for you but heres the query you need.


mysql_query("SELECT COUNT(*) AS num_articles, user_id FROM articles WHERE cat_code='it_tech' GROUP BY user_id ORDER BY num_articles DESC LIMIT 10"); adjust fields to their proper spelling for your table data.

That will give you your top ten article posters in descending order, you can do another query on the author accounts table to match id's to names while you iterate through and output the result set.

If you have any problems post your code and I'll sort it out for you in the morning.

Hope that helps get you started.

[smartads]

Hey There...

I tried it out but I might need a little more explanation on some variables.


mysql_query("SELECT COUNT(*) AS num_articles, user_id FROM articles WHERE cat_code='it_tech' GROUP BY user_id ORDER BY num_articles DESC LIMIT 10");

^^ SELECT COUNT(*) AS "article ID?", "Author ID?" FROM ... GROUP BY "Author ID?" ORDER BY "Article ID?".

I don't understand the "num_articles". Is it the number of articles?, or ... If you could explain a little more detail, I would appreciate it.

Here's a better connection tree from my DB. My Articles table carries an "Article ID", "Author ID", "Category Code".

In order to show articles, author info, and category, I use a command like so (just an example)...

Select * FROM Articles, Authors, Categories WHERE Articles(Author ID)=Authors(Author ID) AND Articles(Cat Code)=Categories(Cat Code) ORDER BY blah blah...

Let me know if this helps.

Martin

[Easywebdev]

num_articles is just a temporary variable, it does not exist in the table structure and is just used to store the number of articles by each author.

Going by your table structure then try this.



mysql_query("SELECT COUNT(*) AS num_articles, `Author ID` FROM Articles WHERE `Cat Code`='it_tech' GROUP BY `Author ID` ORDER BY num_articles DESC LIMIT 10");

You did not specify the exact temr of your it tech field name so you need to put the correct field name in there.

You might need to copy/paste the query as there are backticks in there for fields with spaces. You really should not use spaces in field/table names, use hyphens or underscores instead.

The query will give you the top 10 Author ids and the number of articles. You can get at the data with


$result = mysql_query("SELECT COUNT(*) AS num_articles, `Author ID` FROM Articles WHERE `Cat Code`='it_tech' GROUP BY `Author ID` ORDER BY num_articles DESC LIMIT 10");
while ($row = mysql_fetch_row($result))
{
echo "Author ID = " . $row['Author ID'] . " Articles = " . $row['num_articles'] . "
";
}


Regards, Eamonn.

[smartads]

Hey Eamonn,

I tried the script you have and i'm still getting

"Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in..."

RE: "You really should not use spaces in field/table names, use hyphens or underscores instead."

I do use hyphens. My sample was just an example made easy to read.

Any idea why i'm getting the error? I've tried a few different things with the sample code you gave me and it still won't show any results.

Martin

[crdesign]

I'd love to help but I would need to know the actual table structure that holds the records you are trying to count.

[smartads]

I'd love to help but I would need to know the actual table structure that holds the records you are trying to count.

OK here's a table mock up of what you need...

Articles

- ArticleID
- CategoryCode
- AuthorID

Authors

- AuthorID

Categories

- CategoryCode
- MainCategory
- SubCategory

================================================

I need something that counts the "Articles" table for a specific "CategoryCode" (That also matches the MainCategory specified from the CategoryCode), displaying the author with the most articles in the main category like so...

Articles.CategoryCode=Categories.CategoryCode
AND
Articles.AuthorID=Authors.AuthorID
AND
Categories.MainCategory LIKE "SOMETHING"

That's a simple way for me to show you how to fetch the articles in a MAIN category, not a sub-category. Now I need to find out how to count the articles in a category and display the top 10 authors for that category, and how many articles they have.

The output would be like so...

=================================

Category: Internet Marketing

Top 10 Authors:

Martin has [101] articles
Jim has [89] articles
Sam has [22] articles
and so on...

Any ideas, above and beyond what we've already tried?

[imported__sam_]

SELECT count(*) AS num_articles, Aut.Name FROM articles AS Art, Authors AS Auth, Categories AS Cat WHERE Auth.AuthorID = Art.AuthorID AND Art.CategoryCode=Cat.CategoryCode AND Cat.MainCategory LIKE "SOMETHING" GROUP BY Aut.AuthorID


That should do the trick, assuming that author names are stored in Authors.Name.

best regards

Sam

btw. I don't have 22 articles