CODEWITHSUNDEEP
mysqlsql
Chaitanya
Chaitanya

Reputation: 71

How to fetch the common result of two queries?

Say suppose I have two mysql queries which are something like this

if(!empty($company))
{
$q1="SELECT * FROM TABLE1 WHERE company='$company'";
}
else
{
$q1="SELECT * FROM TABLE1;
}
$r1=mysqli_query($q1);
if(!empty($color))
{
$q2="SELECT * FROM TABLE1 WHERE color='$color'";
}
else
{
$q2="SELECT * FROM TABLE1;
}
$r2=mysqli_query($q2);

While displaying result I tried comparing the pid(an auto_incrementing primary key) if

if($r1['pid]==$r2['pid])
{
/* display the contents */
}

But obviously this won't work cos if the pid is not equal then nothing is displayed and moreover both will not increment at the same pace, the next result of pid for r1 can be 23 where as in r2 it wil be only 12, this way nothing gets displayed. The query which will have lesser results will make the filter work obviously, how can I set the smaller pid equal to the greater one within one cycle of the while loop which is displaying the result? If this can be done then also I guess my problem will be solved?

How can I achieve this?

I simply want to display the results that are common to both the queries.

PS: These queries are being generated dynamically so if any of $color or $company is empty then everything will be selected from the table.

UPDATE *Example*

r1 returns this 

pid  company      color

1    acer          red
3    hp            red
5    logitech      red
6    compaq        red
7    microsoft     red
8    lenovo        red

AND r2 returns this

pid  company       color

5    logitech      red
6    compaq        red
7    microsoft     red
13   nvidia        red

Then from the above results we see that pid 5,6,7 exist in both r1 and r2, how to display all the columns with respect to these pids

Upvotes: 2

Views: 170

Answers (3)

Bohemian
Bohemian

Reputation: 425268

If your application variables are blank when unassigned, you may combine the two conditions, and all of your application logic (testing for blank in app code), with this single, simple but elegant query:

SELECT *
FROM TABLE1
WHERE '$company' IN ('', company)
AND '$color' IN ('', color)

Note how by reversing the usual way you use IN (...), you capture the ignore-if-blank logic with a minimum of code.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270723

Are you looking for this query?

SELECT *
FROM TABLE1 a
WHERE ('$company' = '' or company='$company') and
      ('$color' = '' or color='$color');

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26719

Usually this is done with INTERSECT. MySQL however do not support INTERSECT so you have to use JOIN

SELECT * FROM TABLE1 a WHERE company='$company' INNER JOIN (SELECT * FROM TABLE1 WHERE color='$color') b ON (a.pid = b.pid)

Of course, if your case is so simple, you can just add the WHERE clauses

SELECT * FROM TABLE1 a WHERE company='$company' AND color='$color'

This is how you'd build the query dynamically, bases on whether company or color is selected:

$q1="SELECT * FROM TABLE1 WHERE 1=1";
if(!empty($company))
{
$q1 .=" AND company='$company'";
}
if(!empty($color))
{
$q1 .=" AND color='$color'";
}

$r=mysqli_query($q1);

Upvotes: 2

Related Questions