INNER JOIN query help

[durangod]

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.

[Old Pedant]

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.

$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?

[durangod]

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

[durangod]

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

[Old Pedant]

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.