How to retrieve data from an sql server db using a stored procedure php

Category: sql server driverphp

Question

CuriousCoder15 on Fri, 09 Mar 2018 23:43:25


Hi,

I am struggling to understand how to get data from my SQL server DB using a stored procedure, the stored procedure requires that I pass it the parameter of 'date', then I wish to populate an HTML table with the list of rows that it will return.

I successfully connected to my DB and retrieved an entire table and displayed it in an HTML table but the syntax confuses me when trying with a Stored Proc, can anyone help?

Here is my code:

<?php
/* Empty data array */
$trackData = array( );

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

$credentials = array
(
  'UID'       => 'user',
  'PWD'       => 'password',
  'Database'  => 'myDB',
  'ReturnDatesAsStrings' =>1

);

$date = date('d-m-Y');
$params = array( $date, SQLSRV_PARAM_IN ) ;
$connection = sqlsrv_connect( $server, $credentials );


/* Get sql data */
global $connection;
$sqlForTrack ="SPGetTracks";
$queryForTrack = sqlsrv_query( $connection, $sqlForTrack, $params );


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

/* Handle sql response for track data */
if( sqlsrv_num_fields( $queryForTrack ) )
{
        echo "<thead>
                        <tr>
                        <th>Tracks</th>
                        </tr>
                        </thead>";

        while( $row = sqlsrv_fetch_array( $queryForTrack, SQLSRV_FETCH_ASSOC ) )
        {
                echo "<tbody>";
                echo "<tr>";
                echo "<td>" . $row['Tracks'] . "</td>";
                echo "</tr>";
                echo "</tbody";

        }
        echo "</table>";
}

sqlsrv_free_stmt( $connectoin );
sqlsrv_close ( $connection );
?>


CuriousCoder