Jump to content

How many time can I call a query or invoke sqlsrv_fetch_array


sql-lover

Recommended Posts

Dummy question here, so please apologize my ignorance ...

 

How many times I can invoke or call a query inside a PHP code if I'm using sqlsrv_fetch_array function? Here's the code (which works like a charm)

 

<!DOCTYPE html>
<html>
<head>
    <title>Table Definition's Tool</title>
	<style type="text/css">
		body {
			font-family: Arial, Verdana, sans-serif;
			color: #111111;}
		table {
			width: 600px;}
		th, td {
			padding: 7px 10px 10px 10px;}
		th {
			text-transform: uppercase;
			letter-spacing: 0.1em;
			font-size: 90%;
			border-bottom: 2px solid #111111;
			border-top: 1px solid #999;
			text-align: left;}
		tr.even {
			background-color: #efefef;}
		tr:hover {
			background-color: #c3e6e5;}
		.money {
			text-align: right;}
	</style>
</head>

<body>

<?php

//Variables
$QueryResults1 = Array();		
$QueryResults2 = Array();
$dbName=$_POST['dbName'];		//Variable for the database name	
$PROinstance=$_POST['PRO'];		//Variable for PRO SQL connection string
$ITGinstance=$_POST['ITG'];		//Variable for ITG SQL connection string	
$table=$_POST['table'];			//Variable for the table
$params = array($table);		//Parameters for the T-SQL query, the table name here


require 'ErrorHandling.php';
require 'ConnectionInfo.php';

#############################
##Modify Date for PRO table##
#############################

$mdateQuery="SELECT cast( cast (create_date as date) as date), cast( cast (modify_date as date) as date) FROM sys.tables WHERE name=?";
//The proper way is SELECT create_date, dateadd(dd,0, datediff(dd,0, modify_date)) FROM sys.tables WHERE name=?;

$TableSchemaQuery = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, NUMERIC_PRECISION, NUMERIC_SCALE
	  FROM INFORMATION_SCHEMA.COLUMNS
	  WHERE TABLE_NAME=?";
	  	  

$mdatePROtable = sqlsrv_query($dbPRO, $mdateQuery, $params);
if ($mdatePROtable === false) {
exitWithSQLError('Wrong table name or table does not exist.');
}

$mdateITGtable = sqlsrv_query($dbITG, $mdateQuery, $params);
if ($mdateITGtable === false) {
exitWithSQLError('Wrong table name or table does not exist.');
}

##########################
##Table Input Validation##
##########################
//Main query to validate
$validatingQuery ="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
			WHERE TABLE_NAME =?";	

$validatingPROtable = sqlsrv_query($dbPRO, $validatingQuery, $params);
if ($validatingPROtable === false) {
exitWithSQLError('Wrong table name or table does not exist.');
}

$validatingITGtable = sqlsrv_query($dbITG, $validatingQuery, $params);
if ($validatingITGtable === false) {
    exitWithSQLError('Wrong table name or table does not exist.');
}

while ($row1 = sqlsrv_fetch_array($validatingPROtable,SQLSRV_FETCH_NUMERIC)) {	
 $PROtableName=$row1[0];
}

while ($row1 = sqlsrv_fetch_array($validatingITGtable,SQLSRV_FETCH_NUMERIC)) {	
$ITGtableName=$row1[0];
}				

if ( empty($PROtableName) && empty($ITGtableName) ) {
echo "There is no table on PRO or ITG with that name. Please validate with your POR dba.";
}

elseif (empty($PROtableName)) {
echo "There is a table on ITG with that name but not on PRO. Please validate with your POR dba.";
}

elseif (empty($ITGtableName)){
echo "There is a table on PRO with that name but not on ITG. Please validate with your POR dba.";
}

//If table's name exists on both, do schema comparison inside following "else" statement
else  {

/* Set up the query for PRO's table. */

	  

// Run PRO query
$qresult1 = sqlsrv_query($dbPRO, $TableSchemaQuery, $params);
if ($qresult1 === false) {
	exitWithSQLError('Retrieving schema failed.');	
}
// Retrieve individual rows from PRO result
echo "<h3>Table's structure for \"$table\" - PRO environment</h3>";
echo '<table>';
echo '<thead>';
echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>';
echo '</thead>';
echo '<tbody>';
$i=1;
while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) {
	$i++;
	$QueryResults1[$row1['0']] = $row1;
	$QueryResults1[$row1['1']] = $row1;
	$QueryResults1[$row1['2']] = $row1;
	$QueryResults1[$row1['3']] = $row1;
	$QueryResults1[$row1['4']] = $row1;
	$QueryResults1[$row1['5']] = $row1;
	$QueryResults1[$row1['6']] = $row1;
	$QueryResults1[$row1['7']] = $row1;

	if ($i % 2) {
			echo '<tr class="even">';
			echo '<td>' . htmlspecialchars($row1['0']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['1']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['2']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['3']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['4']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['5']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['6']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['7']) . '</td>';
			echo '</tr>';
	} 
	else {
			echo '<tr>';
			echo '<td>' . htmlspecialchars($row1['0']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['1']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['2']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['3']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['4']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['5']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['6']) . '</td>';
			echo '<td>' . htmlspecialchars($row1['7']) . '</td>';
			echo '</tr>';
	}

}

echo '</tbody>';
echo '</table>'; 
echo '<br>';

// null == no further rows, false == error
if ($row1 === false) {
	exitWithSQLError('Retrieving schema failed.');
}
while ( $row1 = sqlsrv_fetch( $mdatePROtable)) 
{ 
   echo "<p>Created on " .sqlsrv_get_field( $mdatePROtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdatePROtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; 
} 	

/* Set up the query for ITG's table. */
$qresult2 = sqlsrv_query($dbITG, $TableSchemaQuery, $params);
if ($qresult2 === false) {
	exitWithSQLError('Query of product data failed.');
}
// Retrieve individual rows from ITG result
echo "<h3>Table's structure for \"$table\" - ITG environment</h3>";
echo '<table>';
echo '<thead>';
echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>';
echo '</thead>';
echo '<tbody>';
$i=1;
while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) {
	$i++;
	$QueryResults2[$row2['0']] = $row2;
	$QueryResults2[$row2['1']] = $row2;
	$QueryResults2[$row2['2']] = $row2;
	$QueryResults2[$row2['3']] = $row2;
	$QueryResults2[$row2['4']] = $row2;
	$QueryResults2[$row2['5']] = $row2;
	$QueryResults2[$row2['6']] = $row2;
	$QueryResults2[$row2['7']] = $row2;

	if ($i % 2) {
			echo '<tr class="even">';
			echo '<td>' . htmlspecialchars($row2['0']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['1']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['2']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['3']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['4']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['5']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['6']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['7']) . '</td>';
			echo '</tr>';
	} 
	else {
			echo '<tr>';
			echo '<td>' . htmlspecialchars($row2['0']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['1']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['2']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['3']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['4']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['5']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['6']) . '</td>';
			echo '<td>' . htmlspecialchars($row2['7']) . '</td>';
			echo '</tr>';
	}

}

echo '</tbody>';
echo '</table>';
echo '<br>';

while ( $row1 = sqlsrv_fetch( $mdateITGtable)) 
{ 
	echo "<p>Created on " .sqlsrv_get_field( $mdateITGtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdateITGtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; 
}	

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


//Comparing Arrays
foreach ($QueryResults1 as $key => $value) {
	if ($QueryResults2[$key]!=$value) {
		echo '<p><span style="BACKGROUND-COLOR: #ffff00">The schemas are different!</span></p>';
		break;
	}
}

$PROTableSize=sizeof($QueryResults1);
$ITGTableSize=sizeof($QueryResults2);
if ($PROTableSize==$ITGTableSize){
		echo "<p>Schemas are identical!</p>\n";
}

// Releasing resources and closing connections 
sqlsrv_free_stmt($qresult1);
sqlsrv_free_stmt($qresult2);

}

// Releasing resources and closing connections 
sqlsrv_free_stmt($validatingITGtable);
sqlsrv_free_stmt($validatingPROtable);	
sqlsrv_close($dbPRO);
sqlsrv_close($dbITG);

?>
</body>
</html>

 

Not the most elegante code, I'm still learning PHP, but this is what it does:

 

-

[*]Use a T-SQL query to get the table's schema of a given table

[*]Run same query on two different MS-SQL instances

[*]Display result for each one

[*]Compare results to see if they match

 

The problem I have (logic, not syntax) is that I want the comparison's result to be display 1st or way on the top. But if I do that, won't work, as I am capturing the result of each query or array on some variables, $QueryResults1 and $QueryResults2.

 

If I try to run the while plus sqlsrv_fetch_array twice (1st to capture the results, 2nd to actually display) it gives me an error or does not display anything.

 

How many times can I call sqlsrv_fetch_array inside a PHP code? Or, how can I compare both results so the echo statement can appear way on top?

 

Hope I explain it correctly, thanks in advance

 

Link to comment
Share on other sites

You can call that function as many times as there are rows in your result set.  I'm not sure what your issue is, you might try explaining again in another way.  If you're just looking to show a comparison of two tables you'd pretty much go as follows:

 

- Get all the columns for table A

  - Loop the result set and save the results into an array.

- Get all the columns for table B

  - Loop the result set and save the results into an array.

 

- Display the two array's to the screen.

 

Link to comment
Share on other sites

You can call that function as many times as there are rows in your result set.  I'm not sure what your issue is, you might try explaining again in another way.  If you're just looking to show a comparison of two tables you'd pretty much go as follows:

 

- Get all the columns for table A

  - Loop the result set and save the results into an array.

- Get all the columns for table B

  - Loop the result set and save the results into an array.

 

- Display the two array's to the screen.

 

Thanks for reply!

 

In terms of query result of actual display, this is what I want

 

-Show if they are different or not (top of the page)

-Get all columns and display table A

-Get all the columns and display table B

 

This is how is right now ...

 

-Get all columns and display table A

-Get all the columns and display table B

-Show if they are different or not (bottom of the page)

 

My problem is, if I tried to show if they are different or not, it won't work, because that loop is using the variable I put to inside each table's loop.

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.