Combine 2 MySql Queries

[transfield]

Hi,

I am currently using the results of one query to query another table. I'm looking for a simple solution to combine both these queries into a single query & achieve the same result. Part of my code is below. Could you please help me to re-write my query? Thanks.

$query1=("SELECT * FROM test_log WHERE '$mktime' < expiry ORDER BY id DESC"); 
    $result1=mysql_query($query1); 
    $num1=mysql_num_rows($result1);


while ($row1 = mysql_fetch_array($result1))
    { 
    $id = $row1["id"];
    $keyword = $row1["keyword"];
    $sale = $row1["sale_rent"];
    $agents = $row1["e_num"];
    $email = $row1["email"];
    $cc_email = $row1["cc_email"];
    $expiry = $row1["expiry"];
    
    $query2= ("SELECT * FROM condo WHERE (location LIKE '%{$row1['keyword']}%' AND sale_rent LIKE '%{$row1['sale_rent']}%' AND e_num LIKE '{$row1['e_num']}') AND (date >= '$sendate') AND TRIM(IFNULL(`phone_1`,'')) <> '' ORDER BY sale_rent, location");
    $result2=mysql_query($query2);    
$num2=mysql_num_rows($result2);

From the code above, I am using the results of $query1 to query the condo table ($query2). So now I want to do away with two queries & have just one query to achieve the same results.

 

Could you show me the code please?

 

Thanks

Edited April 14, 2015 by transfield

[Muddy_Funster]

When you are selecting information from multiple tables you use a JOIN command.  This is part of the core functionality of a relational database - you build relations between the tables of data. You really should take the time to learn about this.

 

Looking at your queries there seems to be some fundamental flaws in your table design.  can you post up describe's of your tables and detail any fields that relate across tables?

[Psycho]

Give this a try

 

SELECT c.*
 
FROM condo c
JOIN test_log tl
   ON c.location LIKE tl.keyword
  AND c.sale_rent LIKE tl.sale_rent
  AND c.e_num LIKE tl.e_num
 
WHERE (c.date >= '$sendate')
  AND TRIM(IFNULL(c.phone_1,'')) <> ''
 
ORDER BY c.sale_rent, c.location