I have this simple program that stores and retrieve files from the database, I can run the insertion of files but I can't seem to run the code fir retrieving the files.
I am using MSSQL for my database
Here is the code for the test connection (process1.php):
<?php
class Connection {
public $conn;
public function connectDatabase() {
$serverName = "localhost";
$uid = "sa";
$pwd = "joseph04";
$databaseName = "Profile";
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>$databaseName);
// Connect using SQL Server Authentication
$this->conn = sqlsrv_connect( $serverName, $connectionInfo);
// Test Connection
if( $this->conn === false )
{
echo "Connection could not be established.
";
die( print_r( sqlsrv_errors(), true));
}
}
}
?>
and here is the code for retrieving data in the database (ShowProcess.php):
<?php
include_once("process1.php");
class showData extends Connection {
public function doShowData(){
//declare the SQL statement that will query the database
$query = "SELECT col1, col2 ";
$query .= "FROM dbo.ProfileTable ";
//execute the SQL query and return records
$result = sqlsrv_query($this->conn, $query)
or die( print_r( sqlsrv_errors(), true));
//Show results in table
$o = '<table id="myTable">
<thead>
<tr>
<th>Col 1</th>
<th>Col 2</th>
</tr>
</thead><tbody>';
while ( $record = sqlsrv_fetch_array($result) )
{
$o .= '<tr><td>'.$record ['col1'].'</td><td>'.$record ['col2'].'</td></tr>';
}
$o .= '</tbody></table>';
echo $o;
//Show result from sql table separated by comma (commented out)
/* while ( $record = mssql_fetch_array($result) )
{
echo $record["col1"] . " , " . $record["col2"] . "<br />";
} */
//free result set memory
sqlsrv_free_stmt($result);
//close the connection
sqlsrv_close($this->conn);
}
}
if (isset($_POST['formView'])){
$i = new showData;
$i->connectDatabase();
$i->doShowData();
}
?>
and here's my error code:
Array ( [0] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'col1'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'col1'. ) [1] => Array ( [0] => 42S22 [SQLSTATE] => 42S22 [1] => 207 [code] => 207 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'col2'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'col2'. ) )
I think I have messed up with the sqlsrv_query()
parameter
please help? I'm just a newbie in PHP and MSSQL. thanks!
also I git the code from @klcant .. mind if I use your code? thanks!
@Abhishek here is the output:
Okay I've done that and this is the output:
Array ( [0] => [Name] => [1] => 0 [Age] => 0 [2] => [Sex] => ) Array ( [0] => Cayas [Name] => Cayas [1] => 35 [Age] => 35 [2] => female [Sex] => female ) Array ( [0] => Celina [Name] => Celina [1] => 19 [Age] => 19 [2] => Female [Sex] => Female ) Array ( [0] => Chala [Name] => Chala [1] => 90 [Age] => 90 [2] => Female [Sex] => Female ) Array ( [0] => Inna [Name] => Inna [1] => 19 [Age] => 19 [2] => female [Sex] => female ) Array ( [0] => Jenina [Name] => Jenina [1] => 19 [Age] => 19 [2] => Female [Sex] => Female ) Array ( [0] => John [Name] => John [1] => 12 [Age] => 12 [2] => male [Sex] => male ) Array ( [0] => Joseph [Name] => Joseph [1] => 19 [Age] => 19 [2] => Male [Sex] => Male ) Array ( [0] => Lady [Name] => Lady [1] => 0 [Age] => 0 [2] => female [Sex] => female ) Array ( [0] => Mary [Name] => Mary [1] => 28 [Age] => 28 [2] => female [Sex] => female ) Col 1 Col 2
Ok now you use this code , it may solve your problem --
public function doShowData(){
//declare the SQL statement that will query the database
$query = "SELECT * ";
$query .= "FROM dbo.ProfileTable ";
//execute the SQL query and return records
$result = sqlsrv_query($this->conn, $query)
or die( print_r( sqlsrv_errors(), true));
//Show results in table
$o = '<table id="myTable">
<thead>
<tr>
<th>Name</th>
<th>Age </th>
<th>Sex</th>
</tr>
</thead><tbody>';
while ( $record = sqlsrv_fetch_array($result) )
{
$o .= '<tr><td>'.$record ['Name'].'</td><td>'.$record ['Age'].'</td><td>'.$record ['Sex'].'</td></tr>';
}
$o .= '</tbody></table>';
echo $o;
//Show result from sql table separated by comma (commented out)
/* while ( $record = mssql_fetch_array($result) )
{
echo $record["col1"] . " , " . $record["col2"] . "<br />";
} */
//free result set memory
sqlsrv_free_stmt($result);
//close the connection
sqlsrv_close($this->conn);
}