Page 1 of 1

mssql and php query returns zero results

Posted: Fri Mar 22, 2019 11:45 pm
by jdjohnson
Sorry for the newbie question but its been some time since I have played in this space.

I have MSSMS ver 17.9, Apache 2.4, php 7.3 set up and I can connect to my database however when I run a query I get zero rows returned (even though the code says I a row).

I have spent a few hours trying to see what is wrong but I just cannot see it.

Please help!!!!

php code

Code: Select all

<?php

$serverName = "DESKTOP-6SFJUGF\SQLEXPRESS";
$connectionInfo = array( "Database"=>"SandBox");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( mssql_errors(), true));
}

$TableName="[dbo].[titanic]";
$MyQuery="SELECT [pclass]
      ,[survived]
      ,[name]
      ,[sex]
      ,[age]
      ,[sibsp]
      ,[parch]
      ,[ticket]
      ,[fare]
      ,[cabin]
      ,[embarked]
      ,[boat]
      ,[body]
      ,[home#dest]
FROM $TableName";

$query = sqlsrv_query($conn,$MyQuery);

$a = sqlsrv_num_rows($query);
print_r ($a);

 $rows = sqlsrv_has_rows($query);
     if ($rows === true)
          echo "There are rows. <br />";
       else
      echo "There are no rows. <br />";

$b=sqlsrv_errors();
print_r ($b);


?>
returns the following in Apache

Connection established.
There are rows.

I have checked the permissions for the table but from my limited experience it looks ok.

Re: mssql and php query returns zero results

Posted: Sat Mar 23, 2019 12:46 am
by jdjohnson
Dont you hate it when you find the answer 10 minutes after you post the question.

The trouble was my confusion between mssql commands and sqlsrv commands for php.

Code: Select all

<?php

$serverName = "DESKTOP-6SFJUGF\SQLEXPRESS";
$connectionInfo = array( "Database"=>"SandBox");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( mssql_errors(), true));
}

$TableName="[dbo].[titanic]";

$MyQuery="SELECT [pclass]
      ,[survived]
      ,[name]
      ,[sex]
      ,[age]
      ,[sibsp]
      ,[parch]
      ,[ticket]
      ,[fare]
      ,[cabin]
      ,[embarked]
      ,[boat]
      ,[body]
      ,[home#dest]
FROM $TableName";

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );


$query = sqlsrv_query($conn,$MyQuery,$params,$options);

$row_count = sqlsrv_num_rows( $query );
   
if ($row_count === false)
   echo "Error in retrieveing row count.";
else
   echo $row_count;


?>
Which returns

Connection established.
1310