Getting the Highest number from a large array..

[monkeytooth]

Alright.. I'm intent on building a Referal like System for my site and the articles on it..

Its a simple referal type thing, you give your name, your email, their name, their email, add an additional messsage should you so choose.. and it goes on its way..

 

However on the backend i want it to keep a tally of how many times any given article has been refered. I am thinking a simple count, each time its sent out to someone the count goes up +1

 

Now what I am gathering to be the tricky part is.. when i query the counts in the DB, I want to have it determin which on is the highest count and show it to me. May also eventually want to list it out decending from highest to lowest but first I just want to get the highest count over all, ill save the listing all out from another post (unless someone wants to throw that out there anyway)..

 

Now the part I should mention is that the DB I am quering currently is 268 articles large.. and once i refinish the site the db should grow steadily making the queries array larger and larger. Don't know if that has any relevence or not but wanted to mention it anyway.

 

So any takers on helpin me out a bit.. :-)

[hitman6003]

SELECT article_name, COUNT(*) 
FROM article_referred_table 
GROUP BY article_name
ORDER  BY COUNT(*) DESC
LIMIT 1

 

Change the limit clause to see the top X referred articles

[monkeytooth]

im gonna have to give this a try.. thank you, unfortunately will be awhile til I can put it to a big test but concidering its more then what I was thinkin on the SQL end Im going to assume it will work.. thank you

[haku]

Similar to the above, though I kind of think it will be a faster query:

 

"SELECT viewed_count FROM table ORDER BY viewed_count DESC LIMIT 1"

[hitman6003]

Similar to the above, though I kind of think it will be a faster query:

 

"SELECT viewed_count FROM table ORDER BY viewed_count DESC LIMIT 1"

 

Depends on how the values are stored in the database.  If every page view == an insert to the table, then your query won't work.  If every page view increments a "views" counter in the database (i.e. each page has a single row in the database which has a counter), then my query won't work.

[AV1611]

can't he do select max() ?

[monkeytooth]

Essentially I am going to be adding a column to the existing rows, as well as future rows that will act as the container for the count. As its only added to if and when someone says hey I like this article let me share it with a friend, and uses the little quick referal thing I have made up for the articles.

 

They go through the process of giving there name there email.. the friends name and email.. and then it just composes a message with a link to the article telling the friend that they sent the mail via the site.. then it goes into the database when the mail is sent and just +1 to the count for that article.

 

we will call the column name article_count for samples sake, and the actual table article_storage

Again right now theres 263 (was off by 5 with my orginal post) articles.. and if the site picks up anything like it used to be before I took it down for so long and then decided to rebuild it the articles will grow fairly rapidly thus increasing the over all database side.. If i didn't loose a key backup file over the last year the current database would be well over 1300 entries (built over a couple months..) to give you a gauge on how big this thing can get and will again im sure get and keep building from there..

[BlueSkyIS]

can't he do select max() ?