CODEWITHSUNDEEP
phpmysql
acctman
acctman

Reputation: 4349

Combining 3 mysql queries into 1

I need to query two new table $res_info and $res_text... how can I combine all 3 queries into one. The first query $res grabs everything include the unique field m_id which is used in the two other tables. can this be done with a UNION?

$res = @mysql_query("SELECT *, DATE_FORMAT(m_lld,'%m/%d/%y') 
                    AS m_lld_formatted 
                    FROM social_members
                    WHERE m_user='$en[user]'");

if (@mysql_num_rows($res) == 0) call404();
$line = @mysql_fetch_assoc($res);
foreach ($line as $key => $value) 
        $en['m'.$key] = str_replace("\n",'<br/>',stripslashes($value));

$res_info = mysql_query("SELECT *, 
                            FROM social_meminfo 
                            WHERE m_id = '".$en['mm_id']."'");
$res_text = mysql_query("SELECT *, 
                            FROM social_memtext 
                            WHERE m_id = '".$en['mm_id']."'");

Upvotes: 0

Views: 67

Answers (2)

anubhava
anubhava

Reputation: 784908

Based on your comments I think you are looking for one OUTER JOIN and another INNER JOIN like this:

SELECT sm.*, DATE_FORMAT(sm.m_lld,'%m/%d/%y') AS m_lld_formatted 
FROM social_members sm
LEFT OUTER JOIN social_memtext smt ON (sm.m_id = smt.m_id)
JOIN social_meminfo smi ON (sm.m_id = smi.m_id)
WHERE sm.m_user = "$en['user']"

LEFT OUTER JOIN will take care of situation when table social_memtext does have matching entries.

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

//use mysql_real_escape_string for your $en['User']
SELECT sm.*, DATE_FORMAT(sm.m_lld,'%m/%d/%y') 
                    AS m_lld_formatted 
                    FROM social_members sm
                    JOIN social_meminfo smi ON(smi.m_id = sm.m_id)
                    JOIN social_memtext smt ON(smt.m_id = sm.m_id)
                    WHERE sm.m_user = "$en['user']"

Upvotes: 1

Related Questions