Join 3 tables?

[dujmovicv]

Hi ALL!

I'd like to join 3 tables in one query but I'll definitely need your help guys, as I'm not too good in MySql...
I write a php script for displaying bulletins (users status) from a database. I'll try to illustrate what I want to accomplish...

The first table is holding users data (table 'users_details') :



the second (table 'contacts') is 'responsible' for defining if 2 users are in contact relationship or not (status = 1 when they are 'contacts' and 0 if not) :



and the 3rd table is holding the bulletin messages (table 'bulletins') :



I'm using this query to suggest a member to another member (to get in touch if they are from the same city) :

SELECT users_details.*
					  FROM users_details
					LEFT OUTER JOIN
					  (SELECT receiver_username AS username
					   FROM contacts
					   WHERE asker_username = '$username'
					   AND contacts.status = 1
						 UNION
					   SELECT asker_username
					   FROM contacts
					   WHERE receiver_username = '$username'
					   AND contacts.status = 1
					  ) AS c
					ON users_details.username = c.username
					WHERE c.username IS NULL AND users_details.username != '$username' AND users_details.city = '$user_city'

Can someone tell me how to build a query to display :
- user avatar from table users_details
- bulletin message from table bulletins
in case they are 'contacts' (status = 1 in table 'contacts')?
Is it possible to do with one query?
Thank you advanced for your time and patience!

[dujmovicv]

I hope this is not a "mission impossible" and that it's only a matter of time when someone will help me....

[SpacePhoenix]

Can you post the output of a SHOW CREATE TABLE query for them tables and the users table?

[dujmovicv]

Can you post the output of a SHOW CREATE TABLE query for them tables and the users table?

Thanks for trying to help me, but I'm afraid I didn't get what have you asked...
At the moment, I'm trying to retrieve the 'poster' and 'msg' fields from the table 'bulletins' in cases the members are 'contacts'. Here's my query :

SELECT bulletins.* 
		FROM contacts
	INNER JOIN bulletins
		ON bulletins.poster_uname = contacts.receiver_username
		WHERE contacts.asker_username = '$username'
		AND contacts.status = 1
		UNION
		SELECT bulletins.*
		FROM contacts
	INNER JOIN bulletins
		ON bulletins.poster_uname = contacts.asker_username
		WHERE contacts.receiver_username = '$username'
		AND contacts.status = 1
		ORDER BY date DESC

which returns warning:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Xampp\htdocs\my_site\modules\bulletins.php on line 10

[r937]

supplied argument is not a valid MySQL result resource

that's a php error, and this is the mysql forum

run your query outside of php to find the real mysql error

[dujmovicv]

that's a php error, and this is the mysql forum

run your query outside of php to find the real mysql error

Thank you r937! I added a die(mysql_error()) and it came out it was a collation incompatibility between the 2 tables ('contacts' and 'bulletins'). This query is working now :

SELECT bulletins.* 
		FROM contacts
	INNER JOIN bulletins
		ON bulletins.poster_uname = contacts.receiver_username
		WHERE contacts.asker_username = '$username'
		AND contacts.status = 1
		UNION
		SELECT bulletins.*
		FROM contacts
	INNER JOIN bulletins
		ON bulletins.poster_uname = contacts.asker_username
		WHERE contacts.receiver_username = '$username'
		AND contacts.status = 1

Can someone tell me if I can modify this query to select users avatar from the 'users_details' table? Thanks in advance.

[r937]

Can someone tell me if I can modify this query to select users avatar from the 'users_details' table?

yes, you can

how is the users_details tables related to the bulletins table?

[dujmovicv]

yes, you can

how is the users_details tables related to the bulletins table?

Please have a look at the attached images in my first post :
the column 'uname' in users_details table holds the username which is inserted into the 'poster' column when a bulletin message is submitted. I'd like to retrieve the 'avatar' from 'users_details' where uname = poster
Hope I managed to explain....

In the meantime I made a solution without joining 3 tables in a mysql query with the help of PHP. I know this is the MySql forum, I just want to post my solution here, maybe someone will find it helpful...

So, a function has to be called inside the loop (I know this is not very elegant but it works) :

while ($bulletins_rows = mysql_fetch_array($result_bulletins)) {

    $database->selectAvatarFromDir($bulletins_rows['poster_uname']);

// echo the wanted rows here
} 

and the function is :

    function selectAvatarFromDir($user_name) {
        global $avatar_img_src;
        
        $avatars_dir = $website_path."__data/users/avatars/";
                
                $count = 0;
                if(is_dir($avatars_dir)) {
                 if($handle = opendir($avatars_dir)) {
                 while(($file = readdir($handle)) !== false) {
                 $count++;
                 }
                closedir($handle);
                }
                }
                
                $dh  = opendir($avatars_dir);
                while (false !== ($filename = readdir($dh))) {
                    $files[] = $filename;
                }
                
                for ($i=1; $i<=$count; $i++) {
                $strippedName[$i] = preg_replace('/\d*(\..+)?/', '', $files[$i]);
                $file_ext[$i] = substr($files[$i], strrpos($files[$i],".")+1);
                
                if ($strippedName[$i] == $user_name) {
                    $avatar_img_src = $strippedName[$i].".".$file_ext[$i];
                    if ($avatar_img_src == "") { $avatar_img_src = "default_avatar.jpg"; }
                    }
                }
    } // end function selectAvatarFromDir 

Anyway, I suppose it's more elegant way to join the tables with a query, so I'll definitely switch to that one if I manage to build the query (with your help of course).....