phpsql-servermssql-jdbc
MS SQL & PHP to return table from database but it is returning random letters
I have been trying to show tables from my MS SQL into my browser using the following php code but it is returning table with random letters please see screenshots and code below
this is the code I am using
<?php
/* ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL); */
function databaseOutput()
{
$serverName = "*************"; //serverName\instanceName
$connectionInfo = array( "*******"=>"********", "UID"=>"******", "PWD"=>"*******!");
$db = sqlsrv_connect( $serverName, $connectionInfo);
$dbQuery = sqlsrv_query($db, "select * from vw_Impact_Return2 ");
$rows = sqlsrv_fetch_array($dbQuery);
//print('<pre>');
//print_r($rows);
//print('<pre>');
//exit;
foreach($rows as $dbRow) {
?>
<tr>
<td><?php echo $dbRow['Schema_ID']; ?></td>
<td><?php echo $dbRow['Stakeholder_ID']; ?></td>
<td><?php echo $dbRow['Intended_Changes']; ?></td>
<td><?php echo $dbRow['Investment_Type_ID']; ?></td>
<td><?php echo $dbRow['Value_of_Investment']; ?></td>
<td><?php echo $dbRow['Summary']; ?></td>
<td><?php echo $dbRow['Outcomes_Description']; ?></td>
<td><?php echo $dbRow['Outcomes_Indicator']; ?></td>
<td><?php echo $dbRow['Outcomes_Source']; ?></td>
<td><?php echo $dbRow['Outcomes_Quantity']; ?></td>
<td><?php echo $dbRow['Outcomes_Duration']; ?></td>
<td><?php echo $dbRow['Outcomes_Start']; ?></td>
<td><?php echo $dbRow['Outcomes_Financial_Proxy']; ?></td>
<td><?php echo $dbRow['Outcomes_Value_of_Proxy']; ?></td>
<td><?php echo $dbRow['Deadweight']; ?></td>
<td><?php echo $dbRow['Displacement']; ?></td>
<td><?php echo $dbRow['Attribution']; ?></td>
<td><?php echo $dbRow['Drop_Off']; ?></td>
<td><?php echo $dbRow['Impact']; ?></td>
<td><?php echo $dbRow['Return_Year0']; ?></td>
<td><?php echo $dbRow['Return_Year1']; ?></td>
<td><?php echo $dbRow['Return_Year2']; ?></td>
<td><?php echo $dbRow['Return_Year3']; ?></td>
<td><?php echo $dbRow['Return_Year4']; ?></td>
<td><?php echo $dbRow['Return_Year5']; ?></td>
</tr>
<?php
}
} // end of function databaseOutput()
if (isset($_POST['submit_docs'])) { // word output
header("Content-Type:application/msword");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("content-disposition: attachment;filename=test.docx");
?>
<html>
<body>
<h1> Social Return</h1>
<table>
<tr>
<th>Schema_ID</th><th>Stakeholder_ID</th><th>Intended_Changes</th><th>Investment_Type_ID</th><th>Value_of_Investment</th><th>Summary</th><th>Outcomes_Description</th><th>Outcomes_Indicator</th><th>Outcomes_Source</th><th>Outcomes_Quantity</th><th>Outcomes_Duration</th>
<th>Outcomes_Start</th><th>Outcomes_Financial_Proxy</th><th>Outcomes_Value_of_Proxy</th><th>Deadweight</th><th>Displacement</th><th>Attribution</th><th>Drop_Off</th><th>Impact</th>
<th>Return_Year0</th><th>Return_Year1</th><th>Return_Year2</th><th>Return_Year3</th><th>Return_Year4</th><th>Return_Year5</th>
</tr>
<?php databaseOutput(); ?>
</table>
</body>
</html>
<?php
exit; // end of word output
}
?>
<html>
<head>
<title>Social Return</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/mycss.css">
</head>
<body>
<form name="export_form" action="<?php echo($_SERVER['PHP_SELF']);?>" method="post">
<input type="submit" name="submit_docs" value="Export as MS Word" class="input-button" /> <a href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/admin.php"><button type="button" class= "btn btn-block">Go back to Admin Area</button></a>
</form>
<table class="table table-striped" id="student">
<tr>
<th>Schema_ID</th><th>Stakeholder_ID</th><th>Intended_Changes</th><th>Investment_Type_ID</th><th>Value_of_Investment</th><th>Summary</th><th>Outcomes_Description</th><th>Outcomes_Indicator</th><th>Outcomes_Source</th><th>Outcomes_Quantity</th><th>Outcomes_Duration</th>
<th>Outcomes_Start</th><th>Outcomes_Financial_Proxy</th><th>Outcomes_Value_of_Proxy</th><th>Deadweight</th><th>Displacement</th><th>Attribution</th><th>Drop_Off</th><th>Impact</th>
<th>Return_Year0</th><th>Return_Year1</th><th>Return_Year2</th><th>Return_Year3</th><th>Return_Year4</th><th>Return_Year5</th>
</tr>
<?php databaseOutput(); ?>
</table>
</body>
</html>
Please please help me, I have tried everything and I can only connect to my database via sqlsrv_connect
Solution
sqlsrv_fetch_array returns a single row as an array, so in your foreach you're not looping through what you think you are. Try doing a while loop instead:
while($dbRow = sqlsrv_fetch_array($dbQuery, SQLSRV_FETCH_ASSOC)) {
?>
<tr>
<td><?php echo $dbRow['Schema_ID']; ?></td>
<td><?php echo $dbRow['Stakeholder_ID']; ?></td>
<td><?php echo $dbRow['Intended_Changes']; ?></td>
<td><?php echo $dbRow['Investment_Type_ID']; ?></td>
<td><?php echo $dbRow['Value_of_Investment']; ?></td>
<td><?php echo $dbRow['Summary']; ?></td>
<td><?php echo $dbRow['Outcomes_Description']; ?></td>
<td><?php echo $dbRow['Outcomes_Indicator']; ?></td>
<td><?php echo $dbRow['Outcomes_Source']; ?></td>
<td><?php echo $dbRow['Outcomes_Quantity']; ?></td>
<td><?php echo $dbRow['Outcomes_Duration']; ?></td>
<td><?php echo $dbRow['Outcomes_Start']; ?></td>
<td><?php echo $dbRow['Outcomes_Financial_Proxy']; ?></td>
<td><?php echo $dbRow['Outcomes_Value_of_Proxy']; ?></td>
<td><?php echo $dbRow['Deadweight']; ?></td>
<td><?php echo $dbRow['Displacement']; ?></td>
<td><?php echo $dbRow['Attribution']; ?></td>
<td><?php echo $dbRow['Drop_Off']; ?></td>
<td><?php echo $dbRow['Impact']; ?></td>
<td><?php echo $dbRow['Return_Year0']; ?></td>
<td><?php echo $dbRow['Return_Year1']; ?></td>
<td><?php echo $dbRow['Return_Year2']; ?></td>
<td><?php echo $dbRow['Return_Year3']; ?></td>
<td><?php echo $dbRow['Return_Year4']; ?></td>
<td><?php echo $dbRow['Return_Year5']; ?></td>
</tr>
<?php
}