How to compare two query results

[sql-lover]

I am able to run and display two queries I need for my program but having issues comparing the two result sets. If they are identical, means both tables are also identical (the query is for the table's schemas in MS-SQL)

 

Here's the relevant part ...

 

//Variables


$table_name=$_POST['table_name'];


// Connect via Windows authentication
$server = 'win1\i01';

//Connection info for PRO
$connectionInfoPRO = array(

		'Database' => 'adventureworks',
		'CharacterSet' => 'UTF-8'
);
$dbPRO = sqlsrv_connect($server, $connectionInfoPRO);
if ($dbPRO === false) {
    exitWithSQLError('Database connection to PRO failed');
}


//Connection info for ITG
$connectionInfoITG = array(

		'Database' => 'adventureworksCOPY',
		'CharacterSet' => 'UTF-8'
);
$dbITG = sqlsrv_connect($server, $connectionInfoITG);
if ($dbITG === false) {
    exitWithSQLError('Database connection to ITG failed');
}



/* Set up and execute the query. */

$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";
  
// Run PRO query

$qresult1 = sqlsrv_query($dbPRO, $query1);
if ($qresult1 === false) {
    exitWithSQLError('Query of product data failed.');
}

echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';

// Retrieve individual rows from the result
while ($row1 = sqlsrv_fetch_array($qresult1)) {

    echo '<tr><td>', htmlspecialchars($row1['COLUMN_NAME']),
         '</td><td>', htmlspecialchars($row1['DATA_TYPE']),
         '</td><td>', htmlspecialchars($row1['ORDINAL_POSITION']),
         '</td><td>', htmlspecialchars($row1['COLUMN_DEFAULT']),
         '</td><td>', htmlspecialchars($row1['CHARACTER_MAXIMUM_LENGTH']),
	 '</td><td>', htmlspecialchars($row1['IS_NULLABLE']),
         "</td></tr>\n";
}


// null == no further rows, false == error
if ($row1 === false) {
    exitWithSQLError('Retrieving schema failed.');
}



//Run ITG query

$query2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";



$qresult2 = sqlsrv_query($dbITG, $query2);
if ($qresult2 === false) {
    exitWithSQLError('Query of product data failed.');
}


echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';

// Retrieve individual rows from the result
while ($row2 = sqlsrv_fetch_array($qresult2)) {
    echo '<tr><td>', htmlspecialchars($row2['COLUMN_NAME']),
         '</td><td>', htmlspecialchars($row2['DATA_TYPE']),
         '</td><td>', htmlspecialchars($row2['ORDINAL_POSITION']),
         '</td><td>', htmlspecialchars($row2['COLUMN_DEFAULT']),
         '</td><td>', htmlspecialchars($row2['CHARACTER_MAXIMUM_LENGTH']),
	 '</td><td>', htmlspecialchars($row2['IS_NULLABLE']),
         "</td></tr>\n";
}


// null == no further rows, false == error
if ($row2 === false) {
    exitWithSQLError('Retrieving schema failed.');
}

 

How can I compare $row1 and $row2 here (the query results for each one) and validate if they both have same results, each and all columns.

 

Any help is highly appreciated!

[MadTechie]

You could create an array from both loops and then use array_diff

[sql-lover]

Thank you so much for your help.

 

However, I just started programming in PHP a few days ago. I'm a professional SQL dba but PHP newbie.

 

Can you be more specific and post the code or what I need o add or fix?

 

Again, thanks a lot!

[MadTechie]

If you prefer to use SQL then why not do it all in SQL ?

as all it will take is a few joins !

[sql-lover]

I can't use JOINS or UNION.

 

The reason for that is that the tables will reside on different databases and different SQL servers. Also, LinkedServer is not an option.

 

I'm close to what I want. I know is doable via PHP. I just don't know how or where to put the array inside each loop so I can later compare each and find if the two result sets are equal or not.

 

 

[MadTechie]

The logic is like so,

(writing free hand so subject to errors/typos)

 

<?php
$Server1 = Array();
$Server2 = Array();

//IN SERVER 1 SQL Loop add
$Server1[$row['COLUMN_NAME']] = $row;

//IN SERVER 2 SQL Loop add
$Server2[$row['COLUMN_NAME']] = $row;

//Compare
foreach ($Server1 as $key => $fields) {
  foreach ($fields as $field => $value) {
    if (isset($Server2[$key][$field])) {
      if ($Server2[$key][$field] != $value) {
echo "Field '$field' values miss-match ($value / {$Server2[$key][$field]})<br />\n";
      } else {
//Matched
      }
    } else {
      echo "Field '$field' missing<br />\n";
    }
    unset($Server2[$key][$field]);
  }
  if(!empty($Server2[$key])){
    echo "Server 2 has extra fields";
    foreach($Server2[$key] as $K=>$V){
      echo "$K=$V<br />\n";
    }
  }
  unset($Server2[$key]);
}
if(!empty($Server2)){
  echo "Server 2 has extra columns<br />\n";
  foreach($Server2 as $K=>$V){
    echo "$K=$V<br />\n";
  }
}

 

That should push you in the right direction..

but its time for me to sleep as its almost 5am!

[sql-lover]

Thank you so much!

 

I'll test this for sure and let you know if worked or not ... 

[sql-lover]

Almost there!  And, again, thanks for your help and final push...

 

As my main goal is confirm if both schemas are different or not, I modified the comparison part to this ...

 

//Compare
foreach ($Server1 as $key => $fields) {
  foreach ($fields as $field => $value) {
    if (isset($Server2[$key][$field])) {
      if ($Server2[$key][$field] != $value) {

      } else {
//Matched
      }
    } else {
      
    }
    unset($Server2[$key][$field]);
  }
  if(!empty($Server2[$key])){
    echo "Warning! Schemas are different!";
    foreach($Server2[$key] as $K=>$V){
      
    }
  }
  unset($Server2[$key]);
}
if(!empty($Server2)){
  echo "Warning! Schemas are different!<br />\n";
  foreach($Server2 as $K=>$V){
  }
}

 

But I don't really understand why the unset and why we are checking two times $Server2 to see if we have different values or not.

 

[MadTechie]

We loop through Server1, BUT Server2 MAY have more entries, for example

 

Server 1.

1. One

2. Two

3. Three

 

Server 2

1. One

2. Two

3. Three

4. Four

 

Now Server2 has the same as Server1 BUT Server1 doesn't have the same as Server2,

as for the unsetting, that's just a quick way for checking for example

after 3 we have no entries left in Server1 but 1 in Server2, but which one.. well the ones that are currently set

 

also remember i wrote this free hand in the early hour of the morning..

[Andy-H]

http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

[sql-lover]

You did an awesome job ...  ... even if it was free hand, I got no error when I just pasted it, amazing. But remember, I'm new to PHP, hence the reason for my questions.

 

I may post a screenshot of the result set as I don't understand why one table my have more columns than the other one, I'm a bit confused. One may have more rows, but no more columns. The query is against metadata inside MSSQL server.

 

 

[Andy-H]

We loop through Server1, BUT Server2 MAY have more entries rows, for example

Same thing

[sql-lover]

http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

 

Hi Andy,

 

I'm using MSSQL2008 ...  ... not MySQL

[Andy-H]

Ahh, fair enough, and I should have read his code lol

[sql-lover]

Almost ... I'm almost there ..  ... just need a final help, please...

 

So I run three test cases, as follow

 

[*]Result sets have same size (rows and columns

 

[*]1st result's set has more columns

[*]2nd result's set has more columns

 

Test #1 passed: PHP code says they are identical.

Test #2 failed  :-(  , it says they are identical, when they are not (1st set has more columns)

Test #3 passed: PHP code says the schemas are differen.

 

Here's my revised code, for the comparison part only

 

//Compare
foreach ($Server1 as $key => $fields) {
  foreach ($fields as $field => $value) {
    if (isset($Server2[$key][$field])) {
      if ($Server2[$key][$field] != $value) {

      } else {
//Matched
      }
    } else {
      
    }
    unset($Server2[$key][$field]);
  }
  if(!empty($Server2[$key])){
    echo "Warning! Schemas are different!<br /><br />\n";
break 2; // Exit to outer foreach and stops comparing
    foreach($Server2[$key] as $K=>$V){
      
    }
  }
  unset($Server2[$key]);
}

if(!empty($Server2)){
  echo "Warning! Schemas are different!<br /><br />\n";
  foreach($Server2 as $K=>$V){
    
  }
}
else{
  echo "Schemas are identical!<br /><br />\n";
}

 

I added a "break".

 

Why is not detecting #2, because the location of final "else" maybe? ...

[sql-lover]

I got it!!!! Finally ... here's the final code (the comparison part). More refined and simple ...

 

//Comparing Arrays
foreach ($Server1 as $key => $value) {
if ($Server2[$key]!=$value) {
	echo "Warning! Schemas are different!<br /><br />\n";
	break;
}
}

$PROTableSize=sizeof($Server1);
$ITGTableSize=sizeof($Server2);
if ($PROTableSize==$ITGTableSize){
	echo "Schemas are identical!<br /><br />\n";
}

 

Tested when:

 

[*]1st table has more rows: Schemas are not the same

[*]2nd table has more rows: Schemas are not the same

[*]Both tables have same size and content is the same: Schemas are identical

[*]Both tables have same size and content is NOT the same: Schemas are not the same

 

Thanks a lot for the tips and advises!

[MadTechie]

Nice job

Only problem is it can't tell you what the differents are!

 

but if thats all you need then cool

 

 

Congrates

[sql-lover]

Ohh, thats not a problem :-)  ... I can post the whole code again.

 

I'm right now focus on the  input validation and security part.