The SQL error was a typo by me: I meant to use AS ff as the alias for the friend_friend table.

    OK I have tried both versions now and the speed seems to be around the same for both of these, both give me an error when I try to use submit_date from friend_bulletin though

    SELECT auto_id, user_id, bulletin, subject, color, spam
      FROM friend_bulletin, friend_friend
      WHERE friend_friend.status = 1 
      AND friend_friend.friendid = 1
      AND friend_bulletin.user_id = friend_friend.userid
      Order by friend_bulletin.auto_id DESC
      LIMIT 0 , 20

    AND

    SELECT auto_id, user_id, bulletin, subject, color
    FROM friend_bulletin AS fb
    LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
    WHERE ff.status =1
    AND ff.friendid =1
    ORDER BY fb.auto_id DESC
    LIMIT 0 , 20 
      if($_SESSION['spam']=='1'){
      	$spam=" AND fb.spam =1";
      } else {
      	$spam='';
      }
      
      
      
      SELECT auto_id, user_id, bulletin, subject, color, submit_date
      FROM friend_bulletin AS fb
      LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
      WHERE ff.status =1
      AND ff.friendid =1
      $spam //this part is not working
      ORDER BY fb.submit_date DESC
      LIMIT 0 , 20 
      

      Im having 2 problems

      1) #1052 - Column 'submit_date' in field list is ambiguous

      2) $spam the value of this does not change teh results I receive from the DB

        SELECT auto_id, user_id, bulletin, subject, color, [color=red]fb.[/color]submit_date
        

          Have you tried using "explain" before any of these queries to see how mysql will work with them? Just put the word explain at the beginning of the query and check the result. This will tell you how many rows mysql will have to evaluate, which indexes it will use, if it will use filesort or temporary. You can look in the mysql manual for help on how to analyze the explain results.

          http://dev.mysql.com/doc/refman/4.1/en/explain.html

          Veronica

            Almost working 1 small problem now, AND fb.spam =0
            This returns 0 results but if I do spam=1 it returns all results marked with 1 under spam

            SELECT auto_id, user_id, bulletin, subject, spam, color, fb.submit_date
            FROM friend_bulletin AS fb
            LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
            WHERE ff.status =1
            AND ff.friendid =1
            AND fb.spam =0
            ORDER BY fb.submit_date DESC
            LIMIT 0 , 20 

              Ok almost got it all working, 1 last thing though

              I need something like OR fb.user_id=1 to work so if I posted a buleltin myself it will still show up in my list but it isnt working, it will select all results up until it gets to my own bulletin, then it will show my same bulletin for like 8,000 times after

              SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
              FROM friend_bulletin AS fb
              LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
              WHERE ff.status =1
              AND ff.friendid =13
              AND fb.spam='1'
              OR fb.user_id=13 //This line isnt working correctly
              ORDER BY fb.submit_date DESC
              LIMIT 0 , 20

                You need to group things together using ( and ) so that the OR isn't an "OR" of the last AND.

                WHERE (ff.status =1
                AND ff.friendid =13
                AND fb.spam='1')
                OR fb.user_id=13

                  Ok so new code

                  SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
                  FROM friend_bulletin AS fb
                  LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
                  WHERE (
                  ff.status =1
                  AND ff.friendid =13
                  AND fb.spam = '1'
                  )
                  OR fb.user_id =13
                  ORDER BY fb.submit_date DESC
                  LIMIT 0 , 20 

                  the result seems to still return a bunch of buleltins posted from friend ID 13, they are the same buleltin over and over though, please see attached image

                    when I use SELECT DISTINCT it corrects this problem but take like 2 seconds to load instead of .04

                      here is my newest code, it is pretty fast but I have to run this 2 times, 1 to get results and 1 to get row count, is there a faster way to get the row count?

                      SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
                      FROM friend_bulletin AS fb
                      WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
                      AND spam = '1'
                      ORDER BY submit_date DESC
                      LIMIT 0 , 20
                      
                      
                      select count(*)
                      FROM friend_bulletin AS fb
                      WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
                      AND spam = '1'

                        Why not just use the [man]mysql_num_rows/man function on the result?

                          bpat1434 wrote:

                          Why not just use the [man]mysql_num_rows/man function on the result?

                          I just tried this, the problem with it is it returns only the number I am showing, I need the count to do my paging and show total results so since I limit to 20 per page it only returns 20 as the count, any other ideas?

                            Ok it seems this quiry only works good on limit 0,20 when I get into limit 5600,5620 for example or any high number it starts to take 4-5 seconds, and the join statement is fast but I can't get the join statement to work wiht showing my own bulletins

                            SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
                            FROM friend_bulletin AS fb
                            WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
                            AND spam = '1'
                            ORDER BY submit_date DESC
                            LIMIT 0 , 20 

                            here is the Join one which won't show my own results

                            SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
                            FROM friend_bulletin AS fb
                            LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
                            WHERE (
                            ff.status =1
                            AND ff.friendid =13
                            AND fb.spam = '1'
                            )
                            OR fb.user_id =13
                            ORDER BY fb.submit_date DESC
                            LIMIT 0 , 20 
                              Write a Reply...