Jump to content

How to compare two query results


sql-lover

Recommended Posts

Hi all,

 

Found this fantastic forum via Google.

 

I'm a newbie in PHP, still learning the basics, although not new into programming or T-SQL

 

I need help with a simple code I'm writing to  compare the results of this query, after being executed on two different MS-SQL instances:

 

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE = 'products'

 

Above T-SQL query basically returns the structure of a table.

 

So the 1st thing I need to do (please remember, I'm just learning PHP) I need to run the same query against two difference SQL instances that reside on same Domain.

 

2nd, on the webpage, display the result of each one so user can see the table structure.

 

And 3rd and maybe most important goal, after comparing the result set, highlight of display what's different.

 

I'm totally lost! Of course I was able to install MS-SQL, PHP and Apache on web server. They all work like a charm. But I have no idea how to display the results and later, compare those.

 

My main objective or the reason for this PHP simple app is give the user the ability to compare two tables schemas, let's say PRO vs Dev environment so the developer can validate if the tables are different or no. Variables or parameters could be MS-SQL instance of course, database and target table.

 

Can someone provide some hints or basic code where I can start with?

 

Thanks in advance,

Link to comment
Share on other sites

I managed to write this code, but of course is not formatted , does not display the null columns or let the user know about it and still I don't know how to do the same with other table and compare results to highlight differences ...

 

<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "WIN1\I01";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Set up and execute the query. */
$tsql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='ProductPhoto'";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false)
{
     echo "Error in query preparation/execution.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Retrieve each row as an associative array and display the results.*/

echo "[Column Name] [Position] [Column's Default] [Data Type] [Lenght]";
echo '<br>';
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
      echo $row['COLUMN_NAME'].", ".$row['ORDINAL_POSITION'].", ".$row['COLUMN_DEFAULT'].
   $row['DATA_TYPE'].", ".$row['CHARACTER_MAXIMUM_LENGTH'].

"\n";
      echo '<br>';	
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

 

Please help ....

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.