Results 1 to 5 of 5
  1. #1

    How to do this without querying MySQL in a loop?

    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?

  2. 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:
    Code:
    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.

  3. #3
    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?

  4. 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.

  5. #5
    Ok thanks for your help!

Similar Threads

  1. PHP/PEAR/MYSQL Loop Results?
    By pwalters09 in forum Programming Discussion
    Replies: 7
    Last Post: 02-10-2010, 12:11 PM
  2. need help with php/mysql foreach loop
    By mjfroggy in forum Programming Discussion
    Replies: 11
    Last Post: 07-22-2009, 09:39 AM
  3. PHP + mySQL - Querying database for "similar" results.
    By crEA-tEch in forum Programming Discussion
    Replies: 7
    Last Post: 07-03-2009, 12:16 AM
  4. MySQL: Querying on multiple rows
    By qbert220 in forum Programming Discussion
    Replies: 8
    Last Post: 08-08-2008, 11:16 PM
  5. need help omtimizing a php mysql while loop
    By mikey1090 in forum Programming Discussion
    Replies: 12
    Last Post: 08-30-2006, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •