• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

how to compare two tables in a db?

I need to compare 2 resultset from query($result1 & $ result2) to find a data which has same values.
my coding is as follows
 while($row1 = mysql_fetch_array($result1)){
      while(($row2 = mysql_fetch_array($result2)){
          if($row1["dbb"] == $row2["dbb"]{
           echo $row1["dbb"];
          }//if
      }//while
  }//while

By this coding I cannot get my required result.
can anyone help me out to solve the above problem.

thankx in advance.
harish
0
harishsai
Asked:
harishsai
  • 3
  • 3
1 Solution
 
dkjariwalaCommented:
What you are trying to achieve ?

I guess you can do this using query only if two tables,[queries] have common field.

Give some more details,we can have some elegant solution out.

JD
0
 
harishsaiAuthor Commented:
Hai JD,
thanks for ur interest.kindly do help me.

my coding starts like this .....

$w1="Pass";
$result1 = mysql_query("select * from $tableName1
    where result='$w1'");
$result2=mysql_query("select * from $tableName2
    where result ='$w1'");
$num1=mysql_num_rows($result1);
$num2=mysql_num_rows($result2);
while($row1 = mysql_fetch_array($result1)){
     while(($row2 = mysql_fetch_array($result2)){
         if($row1["dbb"] == $row2["dbb"]{
          echo $row1["dbb"];
         }//if
     }//while
 }//while

ie what Iam trying to do is get $result1 & $result2 from 2 different tables where the condition is "Pass".

then from these 2 recordset($result1 & $result2) i need to get the record where field "dbb" has same value.

I hope now u can understand.
if not reply me back
thank u very much
harish
0
 
harishsaiAuthor Commented:
ie , $result1 & $result2 has common field "dbb".
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dkjariwalaCommented:
$w1="Pass";
$result1 = mysql_query("select * from $tableName1
   where result='$w1'");
$result2=mysql_query("select * from $tableName2
   where result ='$w1'");
$num1=mysql_num_rows($result1);
$num2=mysql_num_rows($result2);
while($row1 = mysql_fetch_array($result1)){
    while(($row2 = mysql_fetch_array($result2)){
        if($row1["dbb"] == $row2["dbb"]{
         echo $row1["dbb"];
        }//if
    }//while
}//while

What happens is when the first  inner loop ( $row2 one ) gets over, the result set is set to end. and you will not be able to traverse it again.

So try like

$w1="Pass";
$result1 = mysql_query("select * from $tableName1
   where result='$w1'");
$result2=mysql_query("select * from $tableName2
   where result ='$w1'");
$num1=mysql_num_rows($result1);
$num2=mysql_num_rows($result2);
$i = 0;
while($row1 = mysql_fetch_array($result1)
{
     $res_arr1[$i] = $row1;
     $i++;
}
$i=0;
while($row1 = mysql_fetch_array($result1)
{
     $res_arr2[$i] = $row2;
     $i++;
}

for($i=0;$i<num1;$i++)
{
     $row_result1 = $res_arr1[$i];
     $value1 = $row_result1['dbb'];

     for($j=0;$j<$num2;$j++)
     {
          $row_result2 = $res_arr2[$j];
          $value2= $row_result2['dbb'];
          if($value2 == $value1)
               print "Value matched. Matched value is $value1";
     }
}

I have not tested it,
But it should work,
JD
0
 
andrivCommented:
dkjariwala answer looks perfect but if dbb is a unique identifier where only one dbb record of table 1 matches only 1 record of table 2 you can do it all in the query


$Query = "select * from TableName1,TableName2 where TableName1.result = \"Pass\" And TableName2.result = \"Pass\" And Table1.dbb = Table2.dbb";

$result1 = mysql_query($Query);


   while($row = mysql_fetch_array($result1)){
               echo $row["dbb"];
       }


$row will contain all fields for record for both tables.
0
 
dkjariwalaCommented:
Yeah, exactly,I was goin to suggest that only.
You can do it using query and that would be best thing to do.

JD
0
 
harishsaiAuthor Commented:
hai JD,

Thank u very much.
It works wonderful.I could get exactly what I require.

but if I wanted it to write to another table (table3) in the same database("db1").ie to write $value1 in table3 under field name field1.
how am I to do?

thankx
harish
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now