Display SQL DB table in a html table with php

[kidkoder1]

Hi All,
new to this, I have searched the topics already here but haven’t found what I am looking for. I want to create a web page and display results from an SQL database, (not MySQL) in an HTML table using a stored procedure.
I have tried to display just a single table using SELECT TOP (5) * FROM dbo.Files but only succeeded in getting a single row.
Here is my code:

<?php
/* Connextion to SQL Server */
$server = 'tcp:Address';

$credentials = array
(
  'UID'       => 'User',
  'PWD'       => 'password',
  'Database'  => 'MyDB'
);
$connection = sqlsrv_connect( $server, $credentials );

/* Get sql data */
global $connection;
$sqlForTrack ="SELECT TOP (5) * FROM dbo.File";
$queryForTrack = sqlsrv_query( $connection, $sqlForTrack );

/* Handle sql errors if retuned */
if( $queryForTrack === false )
{
        die( '<pre class="error">' . print_r( sqlsrv_errors(), true ) . '</pre>' );
}

/* Handle sql response for track data */
if( sqlsrv_num_fields( $queryForTrack ) )
{
        echo "<table border='1'>
        <tr>
        <th>ID</th>
        <th>FileName</th>
        <th>Created</th>
        <th>RaceID</th>
        </tr>";

        while( $row = sqlsrv_fetch_array( $queryForTrack, SQLSRV_FETCH_ASSOC ) )
        {

                echo "<tr>";
                echo "<td>" . $row['ID'] . "</td>";
                echo "<td>" . $row['FileName'] . "</td>";
                echo "<td>" . $row['Created'] . "</td>";
                echo "<td>" . $row['RaceID'] . "</td>";
                echo "</tr>";

        }
        echo "</table>";
}

        sqlsrv_close ( $connection );
?>

I will add proper style later with css.

[droopsnoot]

@kidkoder1

but only succeeded in getting a single row.

Well, I have no direct experience of using SQL Server with PHP, but I cannot see anything in your code that would point to a problem. How many rows are there in the table? What happens if you lose the “TOP” specification and just get everything? What happens if you run that query directly in SQL Server, is it Management Studio?

[kidkoder1]

Hi, thanks for getting back to me.
No matter what query i run, i only get one row returned.
I have been playing around since I posted the question and have found that anything with a date is not posting, I wonder if this is causing a break?

What is the correct way to populate a dateTime value?

[droopsnoot]

If you’re concerned about specific field types, what about just echoing something like the id field which is presumably just a numeric, or even just a text string to say “found a row”?

When you say “anything with a date is not posting”, what do you mean by “posting”?

[kidkoder1]

Hi,
All rows now return when I removed the column ‘Created’ (which returns a DateTime) from the query.
So selecting top 5 * will return 5 rows if there isn’t a date in one of the columns.

I will now investigate how to output:
echo “” . $row[‘Created’] . “”; correctly
as this way crashes and shows an empty column.

[droopsnoot]

This any help? https://af-design.com/blog/2010/03/13/microsoft-sql-server-driver-for-php-returns-datetime-object/

[kidkoder1]

Hi droopdnoot,
Thanks for the link, I have tried the second option and although the columns that should contain a date remain blank, the whole table does show in html.

I’m not sure how to implement the first part that uses a timestamp function.
Could you advise?

function date_normaliser ( $d )
{
if ( $d instanceof DateTime )
{
return $d->getTimestamp ( );
}
else
{
return strtotime ( $d );
}
}

[droopsnoot]

As I said earlier I’ve never used PHP with SQL Server. Is it not simply a case of calling that function with your datetime as the parameter?

[kidkoder1]

Thanks for your help droopsnoot, your link recommendation worked.
All the best.