MYSQL question

[Anon]

I trying to nail a sql call, but i need some help.

I am attepting to capture who(submitting_user_id) has sumbitted the most amount of stories(article) and how many stories that user has sumbitted. I don't think i can do this with a WHERE in the submitting_user_id field. any help?

+--------------------+---------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------------------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| submitting_user_id | mediumint(9) | YES | | NULL | |
| article | text | YES | | NULL | |
| date_submitted

[ravikumar]

Hi

I am using lotus notes database if i want to replace that with My SQL what are the things i need to take care.

Thanks
Ravi

[Anon]

wow.

[Anon]

The senseable way would be to add a field that has userposts. When a new user gets in there mark it as 0. Then when they make a post have it add 1, Update the table, Add 1 Update table, ECT..ECT.. Then you can search for it by saying , Assuming that your field is name userposts.

"Select * from tablename order by 'userposts' DESC";

Check the syntax on it... it might not have a ' ' in it. I can't remember. That is simply what I would do. There are probally better ways to do it with a script. I don't know.
Ziggs

[[deleted]]

"The senseable way "

uhm.. no, not sensible at all.

The sensible way is to let SQL do the counting:

SELECT count(1) AS cnt, submitting_user_id
FROM posts
GROUP BY submitting_user_id
ORDER BY cnt DESC
LIMIT 1;

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

I said there are other ways of doing it. I never said mine was the easiest! So explain the script you wrote. I don't really get whats going on.

Ziggs