After I query and print my results I have the following user 1 article1 comment1 article2 comment2 article3 comment3 article4 user 2 article1 comment1 article2 comment2 article3 comment3 what I would like to do is count each row (which i can do) so it will read user 1 4 artilces 3 comments user 2 3 articles 3 comments I would like to add the two counts but then be able to sort the results of this user 1 4 articles 3 commments 7 points user 2 3 articles 3 comments 6 points so the I guess what i am asking is how can I sort the points without resubmitting it back to the database which would call for some sort of action. sorry if this is not clear , but there is not points table and i am trying to do this without have to manually call a function.
Can you give me a small example? I will also need to add the counts of several rows in order to total a user points..... it is the user points that I will need to sort. i want to do it in php so that i do not have to put the user points into the db this will be like a faux userpoints system
let me try this first......so my database would be something like this after i join the tables user id username articles 1 aa tomato 2 bb ham 2 bb egg 2 bb tea i would like the print the result of my query and php as username: bb # of artilces: 3 username: aa # of articles: 1 so the articles are counted and then sorted...... so like which author submitted most articles....
Assuming: Users -> UserID, Username, etc etc. Articles -> ArticleID, UserID, Title, Content etc etc. select Users.UserId,Username,count(ArticleID) from users,articles where Users.UserID=Articles.ArticleID group by Users.UserID A JOIN would be better but I'm feeling too fat and lazy to write one. A proper join will give you the users with a zero article count.
How big are the tables? In general, avoiding "order by", "group by" and "join"s whenever necessary is better if you want your site to scale well. If you provide more information, I might be able to offer a few more efficient alternatives, such as serializing your tables, sorting in php etc.
hey thanks that would be cool. i am fairly new to this so i am not exactly sure how to post this on the forums...... here are the basics user id name 1 henry 2 john content id (but this is article id) title created_by 1 why i rock 1 2 why you rock 2 3 we all rock 2 I am making two modules. one will list in descending order the author with eth most articles John (2 articles) Henry (1 articles) As I was learning this on my own I ran into an interesting problem. because the user table has an id and the content table has an id when i use $row['id'] I get an error. If add the table name i get the same error. In practicing which you can see at http://www.simplyjoomla.com on the left.... I created a list of user id , names and articles. I am very interested in having the id and name display once followed by all the articles for that author. so it would be like 63 Quantum Bordeom artilce 1 article 2 articles 3 Thanks for whatever help you can give.
What kind of traffic do you expect? What about table sizes? You could take the simple approach with a basic left join Or if you're like me, I like to develop for maximum scalability in which case you would want to serialize a table you will use for search & display purposes, mirroring all the data you need into that table.
well i am willing to learn but i have no idea what you are talking about LOL right now i would just like to learn how to display the name once followed by all the articles they have published