Select from multiple databases

[ebolt007]

I'm trying to select multiple peoples ID's where different values in different databases tables match. For instance, I want to make sure the person is a different gender than what the person logging in is, which would be set as 1(male) 2(female) in a Genders Column in the Membership_Profile table, while I am also checking to make sure that they are not blocked inside the Membership table and making sure they have a profile image in a user_profilepic table. How would I do this? This all needs to sort by the Date they were added inside the Membership Table and Limit it to 5 results. When I use the below code tho, it pulls up both Male and Females, but seems to look at the Membership correctly.

 

$sql2 = "SELECT Gender FROM Membership_Profile where UserID = '$user_ID'";
                    $sql_result2 = mysql_query($sql2);
                    $login_row2 = mysql_fetch_assoc($sql_result2);
                    $user_gender = $login_row2['Gender'];  

                    $true_query = mysql_query("SELECT Membership.ID, Membership_Profile.ID, user_profilepic.UserID FROM Membership, Membership_Profile, user_profilepic WHERE Membership_Profile.Gender != '$user_gender' AND Membership.ID = user_profilepic.UserID  AND Membership.IsApproved='1' AND Membership.IsLockedOut = '0' AND Membership.UserLevel = '1' AND user_profilepic.Profile_Pic = '1' ORDER BY Membership.CreateDate DESC LIMIT 5;");
                     $is_odd_row = 1;
                     while ($true_row = mysql_fetch_assoc($true_query))
                      {
                        $right_user = $true_row['UserID'];
                        $right_user2 = $true_row['ID'];



                    $sql = "SELECT * FROM Users where ID = '$right_user' AND ID = '$right_user2'";
                    $sql_result = mysql_query($sql);
                    $login_row = mysql_fetch_assoc($sql_result);
                    $right_user_ID = $login_row['ID'];
                    $rightGUID = $login_row['UserId'];
                    $right_name = $login_row['UserName'];
                    
                    $sql = "SELECT DOB, City, State FROM Membership_Profile where ID = '$right_user' AND ID = '$right_user2'";
                    $sql_result = mysql_query($sql);
                    $login_row = mysql_fetch_assoc($sql_result);
                    $user_dob = $login_row['DOB'];
                    $City = $login_row['City'];
                    $State = $login_row['State'];
                    
                    if ($is_odd_row)
                  		{
                  			echo "<tr style=\"background-color: #e1ebf8\">\n";
                  			$is_odd_row = 0;
                  		}
                  		else
                  		{
                  			echo "<tr style=\"background-color: #f3f7fc\">\n";
                  			$is_odd_row = 1;
                  		}

                    echo "
                    <td align=\"left\">
                    <img width=\"85px\" style=\"margin-right:10px\" src=\"profilepics/$rightGUID/profile/$right_user_ID.jpg\"><br />
                    <span style=\"font-size:13px; font-weight:900; color:#ffae00;\">$right_name </span><br />
                    Location:<br /><b>$City, $State</b><br />
                    Age:<b>";echo CalculateAge("$user_dob");
                    echo "</b></td></tr>";    
                      
                      }

[harkly]

Looks like you are searching on different tables and not databases, correct?

 

To do the tables you want to use "Joins"

 

SELECT
FROM
LEFT JOIN
ON

[ebolt007]

Awww, yep, you are correct, but I suck with joins. How would I write the above select with joins? I've tried grasping the concept of writing them, but can't get it quite yet, most of the stuff comes easy to me, but all the reading I do on joins is normally so different from various sites and I can never get it to work.

 

Thanks

[harkly]

There really not that bad, break them into parts

 

SELECT

FROM

LEFT JOIN

ON

WHERE

 

For the SELECT put every field you want to pull info out, you do need to fully qualify them = tableName.fieldName. Here is an example of mine, I am just pulling info from 2 tables

SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1

 

FROM is the the first table, I use my most important table here

FROM user

 

LEFT JOIN - I view it a just another FROM, pretty sure I will get slammed for that, but I like to keep things simple, defintely look more into it once you get the gist

LEFT JOIN photos

 

ON is the where the 2 tables connect, here I am connecting user w/ photos based on the userID, they are identical in both tables

ON user.userID = photos.userID

 

I am going to use 3 more LEFT JOINS becuase I need them for my conditions in my WHERE clause

LEFT JOIN about_me
ON user.userID = about_me.userID
LEFT JOIN bkgd
ON user.userID = bkgd.userID
LEFT JOIN appearance
ON user.userID = appearance.userID

 

WHERE is the place you put all your conditions for all tables, use the "AND" to separate them

 

WHERE user.userID !='$clientID' AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender 
=$genderPref AND user.genderPref = $gender AND user.exp_date) ";

 

Just take your tables and plug them in