Jump to content
Forums are read-only ×
Larry Ullman's Book Forums

sql-lover

Members
 View Profile  See their activity

  • Posts

    3

  • Joined

  • Last visited

 Content Type 

Posts posted by sql-lover

    Array Inside A Loop

    in PHP for the Web: Visual QuickStart Guide (4th Edition)

    Posted

    Yeah,

     

    Good point Antonio.The security is horrible! lol ... but I was worried about the logic. I will inspect input later.

     

    By the way, got ti! Found an elegant and nice solution. Here's the final answer (or my way) and hope it can help someone else later...

     

    <!DOCTYPE html>
<html>
<head>
<title>Table Definition's Tool</title>
<style type="text/css">
	th { font-size: 110%; border-bottom: 2px solid black; }
	td { padding: 3px; border-bottom: 1px solid #aaa }
</style>
</head>
<body>
<h2>Table's definition (PRO database on Top)</h2>
<table>
<?php
//error_reporting (E_ALL ^ E_NOTICE);
require 'utils.php';
//Variables
$Server1 = Array();
$Server2 = Array();
$PROtable=$_POST['PROtable'];
$ITGtable=$_POST['ITGtable'];
// 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 the query for PRO's table. */
$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$PROtable'";

// Run PRO query
$qresult1 = sqlsrv_query($dbPRO, $query1);
if ($qresult1 === false) {
exitWithSQLError('Retrieving schema 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,SQLSRV_FETCH_NUMERIC)) {
$Server1[$row1['0']] = $row1;
$Server1[$row1['1']] = $row1;
$Server1[$row1['2']] = $row1;
$Server1[$row1['3']] = $row1;
$Server1[$row1['4']] = $row1;

echo '<tr><td>', htmlspecialchars($row1['0']),
	 '</td><td>', htmlspecialchars($row1['1']),
	 '</td><td>', htmlspecialchars($row1['2']),
	 '</td><td>', htmlspecialchars($row1['3']),
	 '</td><td>', htmlspecialchars($row1['4']),
  '</td><td>', htmlspecialchars($row1['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row1 === false) {
exitWithSQLError('Retrieving schema failed.');
}
/* Set up the query for ITG's table. */
$query2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$ITGtable'";

$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,SQLSRV_FETCH_NUMERIC)) {
$Server2[$row2['0']] = $row2;
$Server2[$row2['1']] = $row2;
$Server2[$row2['2']] = $row2;
$Server2[$row2['3']] = $row2;
$Server2[$row2['4']] = $row2;
echo '<tr><td>', htmlspecialchars($row2['0']),
	 '</td><td>', htmlspecialchars($row2['1']),
	 '</td><td>', htmlspecialchars($row2['2']),
	 '</td><td>', htmlspecialchars($row2['3']),
	 '</td><td>', htmlspecialchars($row2['4']),
  '</td><td>', htmlspecialchars($row2['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row2 === false) {
exitWithSQLError('Retrieving schema failed.');
}
//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";
}

// Share Release result liststatement resource and close connection
sqlsrv_free_stmt($qresult1);
sqlsrv_free_stmt($qresult2);
sqlsrv_close($dbPRO);
sqlsrv_close($dbITG);
?>

</table>
</body>
</html>

     

    exitWithSQLError is a function not noted above.

     

    Thanks Larry! Your books really teach people how to program! You have a gift, the gift to explain difficult concepts into something people can apply and understand.

     

    A little bit of background: above is my 1st PHP code! ;-) ... well, a serious one, not a "Hello World" stuff.

    • Upvote 1

    Array Inside A Loop

    in PHP for the Web: Visual QuickStart Guide (4th Edition)

    Posted

    Trying to put in practice what I've read in the book so far, and developed the following code ...

     

    <!DOCTYPE html>
<html>
<head>
<title>Table Definition's Tool</title>
<style type="text/css">
	th { font-size: 110%; border-bottom: 2px solid black; }
	td { padding: 3px; border-bottom: 1px solid #aaa }
</style>
</head>
<body>
<h2>Table's definition (PRO database on Top)</h2>
<table>
<?php
//error_reporting (E_ALL ^ E_NOTICE);
require 'utils.php';
//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,SQLSRV_FETCH_NUMERIC)) {
echo '<tr><td>', htmlspecialchars($row1['0']),
	 '</td><td>', htmlspecialchars($row1['1']),
	 '</td><td>', htmlspecialchars($row1['2']),
	 '</td><td>', htmlspecialchars($row1['3']),
	 '</td><td>', htmlspecialchars($row1['4']),
  '</td><td>', htmlspecialchars($row1['5']),
	 "</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,SQLSRV_FETCH_NUMERIC)) {
echo '<tr><td>', htmlspecialchars($row2['0']),
	 '</td><td>', htmlspecialchars($row2['1']),
	 '</td><td>', htmlspecialchars($row2['2']),
	 '</td><td>', htmlspecialchars($row2['3']),
	 '</td><td>', htmlspecialchars($row2['4']),
  '</td><td>', htmlspecialchars($row2['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row2 === false) {
exitWithSQLError('Retrieving schema failed.');
}
// Share Release result liststatement resource and close connection
sqlsrv_free_stmt($qresult1);
sqlsrv_free_stmt($qresult2);
sqlsrv_close($dbPRO);
?>

</table>
</body>
</html>

     

    I am using PHP with MS-SQL, so that's the reason for sqlsrv_fetch_array.

     

    My problem is that I can't find a way to compare both results sets: $row1 against $row2. I probably need to put an array inside each loop maybe? But I don't know how.

     

    The query returns a table's schema in MS-SQL so the idea is compare both results sets and if they are identical then both tables (schemas) are also the same.

     

    By the way ... I can't use JOINS, UNIONS or pure T-SQL ... because the tables will reside on different databases and different SQL servers.

     

    Help, please ....and thanks in advance ,,,

×
×
  • Create New...