SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Join 3 tables?

  1. #1
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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) :
    Code:
    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!

    Full time ADMIN - art community
    Part time coder - dsign

  2. #2
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I hope this is not a "mission impossible" and that it's only a matter of time when someone will help me....

    Full time ADMIN - art community
    Part time coder - dsign

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you post the output of a SHOW CREATE TABLE query for them tables and the users table?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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 :
    Code:
    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:
    Code:
    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

    Full time ADMIN - art community
    Part time coder - dsign

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dujmovicv View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 :
    Code:
    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.

    Full time ADMIN - art community
    Part time coder - dsign

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dujmovicv View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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) :
    PHP Code:
    while ($bulletins_rows mysql_fetch_array($result_bulletins)) {

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

    // echo the wanted rows here

    and the function is :
    PHP Code:
        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).....

    Full time ADMIN - art community
    Part time coder - dsign


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •