Fehrant

I thought COUNT required GROUP BY. ???

The problem is that the current query pulls up all the users, and all their corresponding post counts. Say, in this very post, the only users are you and me. So why would I bother stuffing an array with every single user post count for this post, if it's only you and me that posted? That's inefficient. However, I can't seem to find the right query, or maybe I'm not facing the issue properly.

*bump* Sorry to resort to bumping like this, but I'm still having issues.

*bump*

Consider the following tables (for simplicities' sake I have used only the fields that are relevant): threads (fields: threads_id) posts (fields: posts_id, fk_threads, fk_users) users (fields: users_id, username) The issue: while posts are looped with all their respective information accordingly to which thread they belong (fk_threads -> threads_id), all the user information is also listed in each post box. Having successfully completed this, I decided to add more piece of user information, namely post count. Obviously, in order to obtain the total post count of a specific user, one would have to use COUNT on all the posts that have a matching fk_users value. After trying for a while to figure out if I could make both the listing of posts with the user information, and the post count altogether in the same query, I gave up, and realized the post count would merit a separate query of its own. But even with this, I quickly came into some issues. The best query I could came up with at the moment was this one: SELECT users.users_id, COUNT(posts.posts_id) AS u_postcount FROM posts LEFT JOIN users ON posts.fk_users = users.users_id GROUP BY users.users_id; The result of this query is all the users, with all their respective post counts. However, this would be a waste of resources, as in a single thread, not every single user in the forum necessarily posted. Storing the results in an array and then using an if to check if the user_id matches to show (or not) the post count was the best I could come up with at the moment. Got any better ideas?

Oh, I did. Believe me, I had an intricate SQL query to get all the informations from all the tables I need. Let me try to use some pseudo-code to depict my issue. [connection to db, and selection of table] [html, head, title, body, some divs] [navigation bar would go here] [looping of posts: while $row = mysql_fetch_array] And that's my issue. Hence I was thinking of storing all the stuff from $row in an array, and then I would be able to refer to them without having to worry about the pointer (or is it called head?) moving one row ahead. Umm... sorry if this is confusing. I have the knowledge to make it work anyway, but I want to do it efficiently.

Yeah, that's my concern. That my lack of knowledge has led me to an option that might not be the best, however doable it is. Let me tell you the scenario. Look at the forum for a moment. You see above the posts (that have obviously been populated by a loop of a posts table) a navigation bar. The navigation bar has been processed before the looping of posts. The bar contains not only the ids of the thread these posts belong to, but also the name, and for that particular thread, it has the id of the forum it belongs to, and the name. And I don't suppose the table that has the posts has any information on the forum the posts of the thread belongs to. I suppose I could do just a query and processing of the bar, then another query, and the looping of the posts but... it is my understanding one has to keep queries to a minimum. Was I clear? ???

I have a pretty straightforward question. I have run into a case where I need to do all the PHP processing before injecting variables into the HTML code. This includes queries. I decided to store the result set into an array. The array will be multi-dimensional, and will have very long text, not to mention eventually it will have large amounts of data. Is this acceptable? Will the array do just fine?

PhREEEk, thanks for the reply. It's kind of embarrassing now that put it that way ("programmer's choice"). I'm partly glad though that's the case. Since I'm a newbie, the solutions to the problems I find could be due to lack of knowledge rather than legitimate ways, so I was rather skeptical. You're dead-on on pointing out I'll be using arrays anyway. I am not very comfortable with OOP, I suppose I'll end up doing arrays.

I am building a forum, and I already got the basics working (registering, signing in, showing and posting in both threads and posts, section->forums->threads->posts architecture displaying properly). However, right now I'm starting to struggle over the details. Particularly this post will be about the navigation links (in this forum, the navigation bar would be: PHP Freaks Forums > PHP and MySQL > PHP Help -> Title of thread). Each of the pages that display any information from the db, use 2 queries tops, and I'd like to leave it at that to minimize hammering the db with multiple queries from multiple users. However, notice how the navigation bar is processed before the actual listing of either threads or posts. That would merit another query in itself, but I want to avoid it, so I thought of two possible solutions. I want to know however if the trouble would be worth it. 1) Creating a class to create all the HTML, that will be able to inject variables' content into the HTML thus created. This would enable me to first do all the PHP processing, and afterwards simply pouring the content conveniently as I see fit. 2) Storing all the threads/posts in an array beforehand, and then simply looping the array. I hope I was clear enough. Any questions, just ask.

Thanks for the replies. So you guys are basically saying it's ok sometimes to have more than one query, and you're also saying that joining two tables is easier on the db than doing two queries. Awesome. I'll keep this in mind.

Hello all. I am sort of an amateur to PHP, so some of my questions might come off as rather naive; please bear with me. I am currently working on coding a forum from scratch. I have never done any massive access website, but I am aware one should always try to optimize the code so as to not have the db do much work (among other optimizations). Yet, I have some across some cases where, either because of ignorance or because of not addressing the issue properly, I have to do two different sql queries in one page. Now, is this common? Is it bad? Let me give you something concrete to work with. In a forum, when you make a new thread, not only do you add (INSERT INTO...) a thread entry to the thread table, but you also need to add a post entry to the post table (well, of course this depends on how your db is sorted out). Thus, I would have to make two sql insert statements once the form with all the information is submitted. And on the subject of multiple queries, what is more memory effective? Two sql statements, or one with a JOIN clause?