Change PHP mssql to sqlsrv (mssql_fetch_row)

Question

I have this code:

$query="Select SUBJECT,NOTES from CAMPNOTIFICATION
    where TYPE LIKE 'message_blackboard' AND VALIDAFTER <= GETDATE() AND (VALIDUNTIL >= GETDATE() OR VALIDUNTIL IS NULL)";

    $encode = array();

    //$query = strtr($query, array('{$raum}' => $raum));
    $query_result = mssql_query($query);

    while ($row = mssql_fetch_row($query_result))
     {
       $encode[] = $row;
       $text = $row[1];
       $text = str_replace("<br />","\n",$text);
                $text = str_replace("<br>","\n",$text);
                $text = str_replace("<br/>","\n",$text);
       $text = str_replace("<p>","\n",$text);
       $text = str_replace("\r","",$text);
                $text = strip_tags($text);
       $text = str_replace("\n","<br>",$text);
       $text = str_replace("<br>\r<br>","",$text);
       $text = str_replace("<br><br>","<br>",$text);

       echo "<h2>" . $row[0] . "</h2>" . $text . "<br>";
            }

I have to change the connections to the sqlsrv model. I managed to do it this way:

$query="Select SUBJECT,NOTES from CAMPNOTIFICATION
    where TYPE LIKE 'message_blackboard' AND VALIDAFTER <= GETDATE() AND (VALIDUNTIL >= GETDATE() OR VALIDUNTIL IS NULL)";


    //$params = array(SQLSRV_PHPTYPE_*);

    $encode = array();

  $query_result = sqlsrv_query($connection, $query);
    if ($query_result === false){
      die(print_r( sqlsrv_errors(), true));
    }



    while ($row = sqlsrv_fetch_object($query_result))
     {

         //echo "Contenido de Row ". $row -> NOTES;
       $encode[] = $row;
       $text = $row -> NOTES;
       $text = str_replace("<br />","\n",$text);
                $text = str_replace("<br>","\n",$text);
                $text = str_replace("<br/>","\n",$text);
       $text = str_replace("<p>","\n",$text);
       $text = str_replace("\r","",$text);
                $text = strip_tags($text);
       $text = str_replace("\n","<br>",$text);
       $text = str_replace("<br>\r<br>","",$text);
       $text = str_replace("<br><br>","<br>",$text);


       echo "<h2>" . $row -> SUBJECT . "</h2>" . $text . "<br>";
      }

But I need to keep the structure in which I use the position of the array instead of calling the object.

Does anyone know of any way? Thank you very much.

while ($row = sqlsrv_fetch_array( $query_result, SQLSRV_FETCH_ASSOC)) { — Zeljka, Nov 19 '19 at 16:00

Zhorov · Accepted Answer · 2021-01-26T07:40:43.673

Solution:

Function mssql_fetch_row() is part of the MSSQL PHP extension (mssql_ functions) and fetches one row of data as a numeric array. If you want to get the data in similar way using PHP Driver for SQL Server (sqlsrv_ functions), you should use sqlsrv_fetch_array() with SQLSRV_FETCH_NUMERIC as second parameter value.

Your code should look like this:

<?php

....  
while ($row = sqlsrv_fetch_array($query_result, SQLSRV_FETCH_NUMERIC)) {
   $encode[] = $row;
   // Additional code here ...
}
...

?>

Additional explanations:

If the SELECT statement returns more than one result set, you need to use sqlsrv_next_result() to make the next result of the specified statement active.

Example, using MSSQL extension:

<?php
// Connection
$server   = "server\instance";
$database = "database";
$username = "username";
$password = "password";
$conn = mssql_connect($server, $username, $password);
mssql_select_db($database, $conn);

// Statement
$sql = "
   SELECT [name], [age] FROM [dbo].[persons];
   SELECT [name], [salary] FROM [dbo].[jobs];
";
$stmt = mssql_query($sql, $conn);

// Fetch data
do {
   while ($row = mssql_fetch_row($stmt)) {
      echo print_r($row, true);
   }
} while (mssql_next_result($stmt));

// End
mssql_free_result($stmt);
mssql_close($conn);
?>

Example, using SQLSRV extension:

<?php
// Connection
$server   = "server\instance";
$database = "database";
$username = "username";
$password = "password";
$info = array(
   "Database" => $database,
   "UID" => $username,
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $info);

// Statement
$sql = "
   SELECT [name], [age] FROM [dbo].[persons];
   SELECT [name], [salary] FROM [dbo].[jobs];
";
$stmt = sqlsrv_query($conn, $sql);

// Fetch data
do {
   while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
      echo print_r($row, true);
   }
} while (sqlsrv_next_result($stmt));

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

Function equivalence:

The table below displays more information about the equivalence between the functions from each extension:

--------------------------------------------------------------------------------------
MSSQL PHP extension                     PHP Driver for SQL Server                      
--------------------------------------------------------------------------------------
mssql_fetch_assoc($stmt)                sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)
mssql_fetch_row($stmt)                  sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)
mssql_fetch_array($stmt, MSSQL_ASSOC)   sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)
mssql_fetch_array($stmt, MSSQL_NUM)     sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)
mssql_fetch_array($stmt, MSSQL_BOTH)    sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)
mssql_next_result($stmt)                sqlsrv_next_result($stmt)

Change PHP mssql to sqlsrv (mssql_fetch_row)

1 Answers1

Linked