Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts

    INNER JOIN query help

    Hi i have tried this a few ways and no results and no errors either. Im getting really tired so thought would ask for some help here.

    The original query that works perfectly was

    PHP Code:

    $iscomment
    =mysql_query("SELECT * FROM profile_comment WHERE com_memid =  $USERID AND com_approved = '1' ORDER BY com_time DESC",$link); 

    So that will grab all the queried comments left by users and list those comments on the USERID profile (USERID is a copy of SESSIONID)

    and that works fine but what i want to do is not list the comments from users who have hidden their profile.

    So i need to do an INNER JOIN i think. I need to grab two other values from another table called adverts adv_paused and adv_approved


    Here is my latest attempt and i will try to doc for you as we go.

    This does not work by the way lol


    PHP Code:

    $iscomment
    =mysql_query("SELECT *, 

    // get all the fields needed from the profile_coments table
    profile_comment.mem_id, profile_comment.com_memid, profile_comment.comment, profile_comment.com_approved, profile_comment.com_time, 

    //also get the values from the adverts table for paused and approved
    adverts.adv_paused, adverts.adv_approved 


    FROM profile_comment

              INNER JOIN adverts ON(profile_comment.memid = adv_userid)  
            //grab advert for whoever left the comment 
            // memid is the person who left the comment, so grab the matching adv_userid from adverts table


           WHERE profile_comment.com_memid ='$USERID' AND com_approved = '1' 
             //now locate the profile for the signed on member in the table to pull their comments if any match their id.  USERID is a compy of SESSIONID so we want the locate those USERID records and make sure the comment has been approved. 

                                    
                            ORDER BY profile_comment.com_time DESC"
    ,$link);
                            
    //now order by com_time in desc order 
    Seems simple but i cant get it to work lol

    thanks in advance.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    You can't/shouldn't mix SELECT * with the selection of individual fields.

    If you are going to select individual fields (which you SHOULD do and you are doing), then kill the * part of the SELECT.

    Anyway, time to DEBUG DEBUG DEBUG. Stop trying to do everything in one statement. Break it up so it's debuggable.
    Code:
    $sql = "SELECT P.mem_id, P.com_memid, P.comment, P.com_approved, P.com_time,  "
         .       "A.adv_paused, A.adv_approved  "
         . " FROM profile_comment AS P INNER JOIN adverts AS A " 
         . " ON P.memid = A.adv_userid "
         . " WHERE P.com_memid ='$USERID' " 
         . " AND P.com_approved = 1 "
         . " ORDER BY P.com_time DESC";
    
    echo "<hr/>DEBUG SQL: " . $sql . "<hr/>\n";
    
    $iscomment=mysql_query( $sql, $link ) or die( mysql_error( ) );
    
    ...
    So first look at the debug output to be sure it is what you expect. In particular, is the value of $USERID correct?

    If you get an error from the "or die", then what is the error?

    And if you copy/paste that SQL into a query tool, what does it give you?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    durangod (10-08-2012)

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    Thanks honestly i use the sql option in phpMyAdmin all the time and i cant tell you or myself why i didnt even think of that, maybe cause i was tired i dont know but i feel pretty much like a dunce right now lol... Thanks for the reply and the lesson in proper technique...

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,415
    Thanks
    269
    Thanked 32 Times in 31 Posts
    Thanks i got it fixed. Appreciate the help.

    I also out of curiosity did a ransack of the script and there are 65 occurances of

    PHP Code:
    SELECT *, field names 
    So i will go thru this and correct those. What i find sort of interesting is that in one part of this script there is a vbulletin application that has the same thing in several places

    PHP Code:
    SELECT *, field names 

    so im guessing that even well known scripts from well known experienced coders have made the same mistake.

    i will clean it up, thank god its not causing errors right now cause that would be a nightmare lol... peace ;0
    Last edited by durangod; 10-09-2012 at 04:21 AM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    There are two problems with using SELECT *, fields

    First, doing SELECT * alone is "expensive" in terms of performance. For a couple of reasons: The DB engine has to go query the schema of the table(s) to find all the fields and you may be returning much much more data than is needed. Remember, MySQL is running in one process on the computer and PHP/web server is running in another process. So *ALL* the data has to be sent from one process to another, and inter-process communication is not free.

    Second, you end up with *DUPLICATE* fields in the result. That doesn't seem to cause problems for PHP, but it does for other systems. But even when it doesn't cause problems, it again causes more data than is needed to be sent across that inter-process barrier.

    Now...There are a *few* cases where using SELECT *, fields can make sense. Suppose, for example, you have joined two (or more tables) and the first table has only a handful of fields and you really do need all those fields in your PHP code. So you might well do SELECT table1.*, table2.fieldX, table2.fieldY It's probably still better to list the fields you need form table1, but the added overhead in this situation isn't really enough to worry much about.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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