I am trying to write my own basic forum script. One of the problems I've come upon is on the script that handles showing a thread. I can't figure out how to get the info for the user associated with each post without having to query MySQL in a loop. Is querying MySQL inside a loop ok or is there a better way to do this?
You can use a JOIN query to get the information from the user table together with the post information. I don't know the exact layout of your database but it would look something like this:
SELECT * FROM posts p INNER JOIN users u ON p.user_id = u.id WHERE thread_id = 1
But if your database keeps growing this will start hitting performance. I'm not saying that this is always the case but in some cases it's a lot faster to query the database in a loop than using a JOIN query. The more rows/data MySQL has to process the slower it gets, especially with JOIN queries. So it's okay to query a database in a loop.
Most forum software use a separate query to get a user data from the database.
When would you recommend using a join over a loop? I am planning to use my basic script to replace a full-blown forum that has about 50K posts right now and I think I'm going to display 20 posts per page. Would that be an appropriate application for a join?
It's really up to you. A JOIN is the preferred way to this but I had performance hits on different occasions when my database was getting bigger. I'm talking about millions of records btw, not just a few thousands. But if you set the correct indexes it will run just fine when using a JOIN query.