StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCompare each row from 2 tables
text
Body
copied!<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->connect->BeginTrans(); $sourceResult = $this->connect->Execute( "SELECT SC_PHONE_NUMBER, SC_CALL_START, SC_DURATION, SC_RATE, SC_ID FROM ". $this->sourceTableName . " WHERE sc_comparing_id = " . $this->insertedId ); if ( $sourceResult ) { while ( !$sourceResult->EOF ) { $result = array(); $comparationResult = $this->connect->Execute( "SELECT CC_PHONE_NUMBER, CC_CALL_START, CC_DURATION, CC_RATE, CC_ID FROM " . $this->comparableTableName . " WHERE cc_comparing_id = " . $this->insertedId . " AND cc_is_compared = 0" . " AND cc_phone_number = " . $sourceResult->fields['SC_PHONE_NUMBER'] . " AND " . $sourceResult->fields['SC_CALL_START'] . " BETWEEN cc_call_start - " . TIME_RANGE . " AND " . " cc_call_start + " . TIME_RANGE ); if ( $comparationResult ) { while ( !$comparationResult->EOF ) { $callStartRating = TIME_RANGE / ( TIME_RANGE + abs( $sourceResult->fields['SC_CALL_START'] - $comparationResult->fields['CC_CALL_START'] ) ); $durationRating = 0; $rateRating = 0; if ( $sourceResult->fields['SC_DURATION'] > $comparationResult->fields['CC_DURATION'] ) { $durationRating = $comparationResult->fields['CC_DURATION'] / $sourceResult->fields['SC_DURATION']; } else { $durationRating = $sourceResult->fields['SC_DURATION'] / $comparationResult->fields['CC_DURATION']; } if ( $sourceResult->fields['SC_RATE'] > $comparationResult->fields['CC_RATE'] ) { $rateRating = $comparationResult->fields['CC_RATE'] / $sourceResult->fields['SC_RATE']; } else { $rateRating = $sourceResult->fields['SC_RATE'] / $comparationResult->fields['CC_RATE']; } $totalRating = $rateRating + $durationRating + $callStartRating; $result[] = array( 'sc_id' => $sourceResult->fields['SC_ID'], 'cc_id' => $comparationResult->fields['CC_ID'], 'rating' => $totalRating ); $comparationResult->MoveNext(); } $resArray = null; if ( count( $result ) >= 1 ) { $resArray = $result[0]; foreach ( $result as $row ) { if ( $resArray['rating'] < $row['rating'] ) { $resArray = $row; } } $query = "UPDATE source_cdr SET sc_cc_key = " . $row['cc_id'] . " WHERE sc_id = " . $row['sc_id']; $this->connect->_Execute( $query ); $this->connect->_Execute( "UPDATE comparable_cdr SET cc_is_compared = 1 WHERE cc_id = " . $resArray['cc_id'] ); } } $this->connect->CommitTrans(); $sourceResult->MoveNext(); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload