Displaying table from SQL Server database

Question

I am trying to display a table, which i can edit. My table comes up empty, even though there are records in the table. Any help would be appreciated.

code for selecting data: select.php

 <?php  
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
 ?>

<?php  
 $server = "";
$options = array( "UID" => "", "PWD" => "", "Database" => "");
global $conn;
$conn = sqlsrv_connect($server, $options);
if ($conn === false) exit("<pre>" . print_r(sqlsrv_errors(), true));

 $output = '';  
 $sql = "SELECT * FROM Table";  
 $result = sqlsrv_query($conn, $sql);  
 $output .= '  
      <div class="table-responsive">  
           <table class="table table-bordered">  
                <tr>  
                     <th width="10%">ID</th>  
                     <th width="40%">column</th>  
                     <th width="40%">column</th>  
                </tr>';  
 $rows = sqlsrv_num_rows($result);
 if($rows > 0)  
  {  
      if($rows > 10)
      {
          $delete_records = $rows - 10;
      $delete_sql = "DELETE FROM Table LIMIT $delete_records";
      sqlsrv_query($conn, $delete_sql);
  }
  while($row = sqlsrv_fetch_array($result))  
  {  
       $output .= '  
            <tr>  
                 <td>'.$row["id"].'</td>  
                 <td class="column" data-id1="'.$row["id"].'" contenteditable>'.$row["PurchaseID"].'</td>  
                 <td class="LogBookNo" data-id2="'.$row["id"].'" contenteditable>'.$row["LogBookNo"].'</td> 
<td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" class="btn btn-xs btn-danger btn_delete">x</button></td>

       ';  
  }  
  $output .= '  
       <tr>  
            <td></td>  
            <td id="column1" contenteditable></td>  
            <td id="column2" contenteditable></td>
            <td><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-success">+</button></td>  
       </tr>  
  ';  
 }  
 else  
 {  
      $output .= '
                <tr>  
                <td></td>  
                <td id="column1" contenteditable></td>  
                    <td id="column2" contenteditable></td>  
                    <td><button type="button" 
name="btn_add" id="btn_add" class="btn btn-xs btn-success">+</button></td>  
           </tr>';  
  }  
 $output .= '</table>  
      </div>';  
 echo $output;  
 ?>

`LIMIT` is for mysql, not mssql. You also can't fetch a delete. — user3783243, Jun 24 '19 at 11:45
@user3783243 Okey, but i don't think this is the main problem for table not displayin data from SQL table — mnm, Jun 24 '19 at 12:23

Zhorov · Accepted Answer · 2019-06-24T13:13:32.297

Function sqlsrv_num_rows returns the actual rows count when you use a client-side, static, or keyset cursor with sqlsrv_query. You need to use "Scrollable" => SQLSRV_CURSOR_KEYSET in your $options parameter. Another issue with your code, is that T-SQL doesn't have LIMIT keyword. In your case next answers may help.

Next is an example, based on your code:

<?php  
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
?>

<?php  
$server = "";
$options = array( 
    "UID" => "", 
    "PWD" => "", 
    "Database" => ""
);
$conn = sqlsrv_connect($server, $options);
if ($conn === false) {
    exit ("<pre>" . print_r(sqlsrv_errors(), true));
}   

$output = '';  
$sql = "SELECT * FROM Table";  
$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));  
$output .= '  
      <div class="table-responsive">  
           <table class="table table-bordered">  
                <tr>  
                     <th width="10%">ID</th>  
                     <th width="40%">column</th>  
                     <th width="40%">column</th>  
                </tr>';  
$rows = sqlsrv_num_rows($result);
if ($rows > 0) {  
    /* 
    if ($rows > 10) {
        $delete_records = $rows - 10;
        $delete_sql = "DELETE FROM Table LIMIT $delete_records";
        sqlsrv_query($conn, $delete_sql);
    }
    */
    while($row = sqlsrv_fetch_array($result)) {  
       $output .= '  
            <tr>  
                <td>'.$row["id"].'</td>  
                <td class="column" data-id1="'.$row["id"].'" contenteditable>'.$row["PurchaseID"].'</td>  
                <td class="LogBookNo" data-id2="'.$row["id"].'" contenteditable>'.$row["LogBookNo"].'</td> 
                <td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" class="btn btn-xs btn-danger btn_delete">x</button></td>
       </tr>';  
    }  
    $output .= '  
       <tr>  
            <td></td>  
            <td id="column1" contenteditable></td>  
            <td id="column2" contenteditable></td>
            <td><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-success">+</button></td>  
       </tr>  
  ';  
} else {  
    $output .= 
        '<tr>  
                <td></td>  
                <td id="column1" contenteditable></td>  
                    <td id="column2" contenteditable></td>  
                    <td><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-success">+</button></td>  
        </tr>';  
}  

$output .= '</table>  
      </div>';  
echo $output;  
?>

Displaying table from SQL Server database

1 Answers1