sql-lover Posted February 16, 2012 Share Posted February 16, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/257138-how-many-time-can-i-call-a-query-or-invoke-sqlsrv_fetch_array/ Share on other sites More sharing options...
kicken Posted February 17, 2012 Share Posted February 17, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/257138-how-many-time-can-i-call-a-query-or-invoke-sqlsrv_fetch_array/#findComment-1318182 Share on other sites More sharing options...
sql-lover Posted February 17, 2012 Author Share Posted February 17, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/257138-how-many-time-can-i-call-a-query-or-invoke-sqlsrv_fetch_array/#findComment-1318191 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.