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