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
$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
Seems simple but i cant get it to work lol
// 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
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
thanks in advance. :)
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.
So first look at the debug output to be sure it is what you expect. In particular, is the value of $USERID correct?
$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( ) );
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?
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... :)
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
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
SELECT *, field names
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
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.