Join 3 tables?

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!

I hope this is not a “mission impossible” and that it’s only a matter of time when someone will help me…

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… :frowning:
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

that’s a php error, and this is the mysql forum :slight_smile:

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.

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)…