title: Compare each row from 2 tables
body: <p>I have 2 tables. Tables are having same colums and I need to compare them. Simple Join query cannot resolve my problem 'cause table2 can contain many rows appropriate to table 1, but I need to choose the best appropriate row. For example : <br /></p>
  
  <h2>Table 1</h2>
  
  <p>duration; price; number; <br />
  1; 3; 5; </p>
  
  <p><br /></p>
  
  <h2>Table 2</h2>
  
  <p>duration; price; number; <br />
  1; 3.1; 5;  <br />
  1; 3.01; 5; <br /></p>
  
  <p>I need to compare row1 from table1 to row1 and row2 from table 2 and choose the best appropriate(e.g row2 is the best appropriate) and mark the row2 as compared and do not to  compare it next time. I'm using FIREBIRD database and ADODB php library. I wrote some code but it works very long time when I have many records in tables. How can I optimize my code to do this task more faster? <br><br>
  CODE: </p>
  
  <pre><code>$this-&gt;connect-&gt;BeginTrans();
          $sourceResult = $this-&gt;connect-&gt;Execute( "SELECT SC_PHONE_NUMBER, SC_CALL_START, SC_DURATION, SC_RATE, SC_ID  FROM ". $this-&gt;sourceTableName . " WHERE sc_comparing_id = " . $this-&gt;insertedId );
  
          if ( $sourceResult ) {
  
              while ( !$sourceResult-&gt;EOF ) {
                  $result = array();
  
                  $comparationResult = $this-&gt;connect-&gt;Execute(
                      "SELECT CC_PHONE_NUMBER, CC_CALL_START, CC_DURATION, CC_RATE, CC_ID  FROM " . $this-&gt;comparableTableName . " WHERE cc_comparing_id = " . $this-&gt;insertedId
                      .   " AND cc_is_compared  = 0"
                      .   " AND cc_phone_number = " . $sourceResult-&gt;fields['SC_PHONE_NUMBER']
                      .   " AND " .  $sourceResult-&gt;fields['SC_CALL_START'] . " BETWEEN cc_call_start - " . TIME_RANGE . " AND " . " cc_call_start + " . TIME_RANGE
                  );
  
  
                  if ( $comparationResult ) {
  
                      while ( !$comparationResult-&gt;EOF ) {
  
                          $callStartRating = TIME_RANGE / ( TIME_RANGE + abs( $sourceResult-&gt;fields['SC_CALL_START'] - $comparationResult-&gt;fields['CC_CALL_START'] ) );
                          $durationRating = 0;
                          $rateRating = 0;
  
                          if ( $sourceResult-&gt;fields['SC_DURATION'] &gt; $comparationResult-&gt;fields['CC_DURATION'] ) {
                              $durationRating = $comparationResult-&gt;fields['CC_DURATION'] / $sourceResult-&gt;fields['SC_DURATION'];
                          } else {
                              $durationRating = $sourceResult-&gt;fields['SC_DURATION'] / $comparationResult-&gt;fields['CC_DURATION'];
                          }
  
                          if ( $sourceResult-&gt;fields['SC_RATE'] &gt; $comparationResult-&gt;fields['CC_RATE'] ) {
                              $rateRating = $comparationResult-&gt;fields['CC_RATE'] / $sourceResult-&gt;fields['SC_RATE'];
                          } else {
                              $rateRating = $sourceResult-&gt;fields['SC_RATE'] / $comparationResult-&gt;fields['CC_RATE'];
                          }
  
                          $totalRating = $rateRating + $durationRating + $callStartRating;
                          $result[]  =  array(
                              'sc_id' =&gt;  $sourceResult-&gt;fields['SC_ID'],
                              'cc_id' =&gt;  $comparationResult-&gt;fields['CC_ID'],
                              'rating' =&gt; $totalRating
                          );
                          $comparationResult-&gt;MoveNext();
                      }
  
                      $resArray = null;
  
                      if ( count( $result ) &gt;= 1 ) {
  
                          $resArray = $result[0];
  
                          foreach ( $result as $row ) {
                              if ( $resArray['rating'] &lt; $row['rating'] ) {
                                  $resArray = $row;
                              }
                          }
                          $query = "UPDATE source_cdr SET sc_cc_key = " . $row['cc_id'] . " WHERE sc_id = " . $row['sc_id'];
  
                          $this-&gt;connect-&gt;_Execute( $query );
                          $this-&gt;connect-&gt;_Execute( "UPDATE comparable_cdr SET cc_is_compared = 1 WHERE cc_id = " . $resArray['cc_id'] );
  
                      }
                  }
                  $this-&gt;connect-&gt;CommitTrans();
                  $sourceResult-&gt;MoveNext();
              }
  </code></pre>