Number of submissions

SureFire

I have a page on my website that lists all the contributors to the site.

It pulls the NAME, CONTRIBUTOR SINCE, and LATEST SUBMISSION details from a table called authors.

I also need it to pull NUMBER OF SUBMISSIONS from a table called articles, by adding up all the articles that were written by the same person.

The table(articles) has a row called authors, that I can use for the code, but I don't know what code I should use to put it in my loop.

So far I have this:

$query = "SELECT author FROM articles WHERE ='$variable'";

all I need is the $variable to equal something that would do this, i think.

Thanks

laserlight

Well, one way would be to use the authors table to get the unique "author_id" of the author in question.

Then, having a corresponding foreign key "author_id" in the submissions table, SELECT article_id, article_title FROM articles WHERE author_id='$author_id', after which you can count the number of rows returned, and possibly display the article titles for the visit to click on and read the article.

werkkrew

you should probably also add a column to the table including the total submissions just to make it more simple in the future

however,

you could just do a query like (like laserlight says):

mysql_query("SELECT * FROM table WHERE author="$author"");
$submissions = mysql_num_rows($sql)

this is of course assuming the authors name is unique...

SureFire

Werkkrew,I had it originally set up like that, but I thought it would be easier if I just add a new article, and it automatically shows their contribution number on the authors page.

Thanks to both of you for possible solutions to this problem, I'll go try them out right now!

I appreciate how quick you both replied!!

and yes, all the usernames will be unique!:-)

laserlight

I would advise not using the usernames as to search up the articles table.
Rather, use the author_id, which could be an INT NOT NULL AUTO_INCREMENT PRIMARY KEY field in the authors table, and an INT NOT NULL field in the articles table.

werkkrew

if its an auto increment field that means that every article posted by said author would have a different id...he could use a search by the username since its unique, or create another column with an author id that is also unique but is an integer for redundancy and perhaps more accuracy and speed...but an auto increment wouldnt do what he needs it to do...the only difference would be that youd look through the table and match an integer value instead of a string, the result would be the same

laserlight

um, read carefully:

an INT NOT NULL AUTO_INCREMENT PRIMARY KEY field in the authors table, and an INT NOT NULL field in the articles table