Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Location
    UK
    Posts
    774

    MySQL - 'WHERE OR' clause question

    Hi all,

    I'm developing a forum-style script, and have hit a problem. I'd like to make it run really fast, so i'm trying to use as little MySQL queries as possible.

    When a whole thread is listed (i.e. multiple posts), the script first grabs the content of each post from my "posts" table (this includes the user's ID).

    Each user can include a little information about themselves, such as location, avatar, postcount, etc. These fields are in the "users" table.

    For each post that's listed, the script looks at the UserID of who created the post. It then grabs the user's information from the user table.

    An example of how to do this, would be similar to the following pseudo code:

    Code:
    -Grab posts where thread-id = whatever
      -loop through all rows (posts)
        -Grab user where user id = creator of post
          -Show user information and post
    Obviously the above method uses LOADS of queries, and i'd like to use as little as possible.

    I have tried using a 'WHERE OR' clause to do this, however obviously when a user posts in the same thread twice then things get messed up (for example, WHERE id='3' OR id='3') only returns one result.

    I hope I explained this OK,

    If anyone has any ideas of how to achieve this (e.g. how is it done in VB and SMF) any help would be greatly appreciated.

    Thanks.

  2. #2
    Have you looked into JOIN queries as you will be able to retrieve data from multiple tables in the same query.

  3. #3
    A JOIN is probably the best way to go. It will depend on your database structure, but from the method you listed there, you could likely cut that all down to 1 query.
    Ubersmith Datacenter Edition
    Complete Billing and Facilities Management for Enterprise Hosts
    Now featuring fully integrated Bandwidth Billing, Remote Rebooter Support and IP Management
    http://www.ubersmith.com

  4. #4
    If you find that you absolutely require multiple queries to retrieve related data your tables probably need some optimization.. You should find that you can almost always grab ALL the data you need in just one or maybe two queries.
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  5. #5
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Yes, you definately need to use a JOIN query. Try something like:
    Code:
    SELECT p.id,p.title,p.date,p.body, u.username,u.location,u.avatar,u.postcount,u.signature FROM tbl_posts AS p LEFT JOIN tbl_users AS u ON p.userid=u.id ORDER BY p.date DESC
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  6. #6
    Join Date
    Nov 2005
    Posts
    268
    Make sure you are using the LIMIT arguement to limit the amount of returns to the number of posts you are going to display.

    Using the above example:
    Code:
    SELECT p.id,p.title,p.date,p.body, u.username,u.location,u.avatar,u.postcount,u.signature FROM tbl_posts AS p LEFT JOIN tbl_users AS u ON p.userid=u.id ORDER BY p.date DESC LIMIT 10 OFFSET 0
    The number after offset would be determined by this equasion 10*(p-1)

    where p is the page number.

    Also assuming you are using an autonumber you can order by p.id to get the exact same order as p.date but it will probably be faster than using a date field in the order operator.

  7. #7
    Join Date
    Nov 2004
    Location
    UK
    Posts
    774
    Thanks for the help!

    Looks like JOIN is what I need. I'll start reading up on it, on first glance it looks complicated

    Cheers.

Posting Permissions

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