Help with PHP to display t-sql statment using sqlsrv_query

Question

Hi there,

Just wondering if there is anyone that can help me understand why my code is not outputting information. I have created a t-sql query in Microsoft SQL Server 2008. The query works fine and displays all the correct data in SQL management studio. Below is how the data looks in SQL Server Management studio:

block sequence|number of ties|percent_of_q4|number of q3 ties|percent of q3 ties| quality

0 1108 11.34296 37 3.33935 1

1 1094 31.11517 66 6.032907 1

2 1109 21.633 53 4.77908 1

 

When I try to output the data in a simple PHP script using exactly the same query. No data is shown.

Does this happen because the sqlsrv_query does not like my query? Does sqlsrv_fetch_array not like the "IS NOT NULL"in my query? I've completely run out of ideas on what to try... Can anyone provide any advice for reason why my script may not be working? Any reply would be greatly appreciated! 

If there is anything that is unclear, please let me know.

 

Cheers,

Neil

 

The PHP script is below:

 

 

       <?php

		/*data base connection */



		$serverName = ".\SQLEXPRESS";

		$connectionOptions = array("Database"=>"V6_HOLLTS479_20101015_subset",

         "UID"=>"username",

         "PWD" => "password");

		 

		/* Connect using Windows Authentication */		 

		$conn = sqlsrv_connect($serverName, $connectionOptions);



		/* Check whether connnection is established */

		if($conn === false)

		{

			die(print_r(sqlsrv_errors(), true));

		}

		

		/* SQL query */

$tsql = "

DECLARE @block_size AS real



  DECLARE @threshold_1 AS real

 DECLARE @threshold_2 AS real

  DECLARE @threshold_3 AS real

 DECLARE @threshold_4 AS real



 



   --Set variables



	SET @block_size = 200.0



	SET @threshold_1 = 50  -- GISCat4HighThresh

	SET @threshold_2 = 75  -- GISCat3HighThresh

	SET @threshold_3 = 25  -- GISCat4LowThresh

	SET @threshold_4 = 50  -- GISCat3LowThresh



 SELECT

  sub_t.block_sequence ,

  sub_t.number_of_ties,

   tie_q_4.number_of_ties AS number_of_q4_ties,

   (CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 AS percent_of_q4_ties,

       tie_q_3.number_of_ties AS number_of_q3_ties,

    (CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 AS percent_of_q3_ties,



       --The next column shows the block quality.  This is currently 4,3,1 based on the threshold rules.

    CASE WHEN (

		(CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_1

			OR

		(CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_2

			) THEN 4

        WHEN (

		(CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_3

			OR

        (CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_4

			) THEN 3

        ELSE 1

        

					END AS quality

		

		FROM (

        SELECT

            FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

			COUNT(image_sequence) AS number_of_ties

            FROM database

			GROUP BY 

				FLOOR(CAST(image_sequence AS real)/@block_size)

			) AS SUB_T



		LEFT JOIN (

		SELECT FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

			COUNT(image_sequence) AS number_of_ties

			FROM database

			WHERE 

				quality = 4

			GROUP BY 

			FLOOR(CAST(image_sequence AS real)/@block_size)

		) AS tie_q_4 ON sub_t.block_sequence = tie_q_4.block_sequence



		LEFT JOIN (

		SELECT

			FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

			COUNT(image_sequence) AS number_of_ties

            FROM  database

			WHERE 

				quality = 3

			GROUP BY 

			FLOOR(CAST(image_sequence AS real)/@block_size)

		) AS tie_q_3 ON sub_t.block_sequence = tie_q_3.block_sequence

        
		WHERE sub_t.block_sequence IS NOT NULL
		 ORDER BY block_sequence

";




$result = sqlsrv_query($conn, $tsql);



$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

print("<pre>");

print_r($row);

print("</pre>");



?>

 

Answer 1

nielcoder-

Have you tried calling sqlsrv_errors after the calls to sqlsrv_query and sqlsrv_fetch_array? I'm wondering if the script above execute successfully, but just prints an empty array. If the script fails somehow, seeing the error messages would be helpful.

-Brian 

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 2

Thank you Brian for your reply! I have added the following under my SQL statement to check for errors if query files, but I get nothing... Just a blank screen. The table has NULL fields in the first row, hence I added the line "WHERE column IS NOT NULL"... Do you think that this could be a problem? It is quite a complicated SQL query, do you think that it could be an issue with the driver being unable to process the query?

Any advice welcomed!

 

Cheers

Neil

 

 

$result = sqlsrv_query($conn,$tsql);



		if( $result === false)

		{

			echo "Error in query preparation/execution.\n";

			die( print_r( sqlsrv_errors(), true));

		}  

 

Answer 3

If you are getting a blank screen, have you checked your php error log to see if you have any php errors?

If not, then I will say what I usually do, and I say it a lot:  try calling sqlsrv_next_result()...your 'SET' statements can cause several results from your query.  e.g.:

$result = sqlsrv_query($conn,$tsql);
if( !$result )
{
    exit("Error in query preparation/execution.\n" . print_r( sqlsrv_errors(), true));
}
$row = null;
do
{
    $test = sqlsrv_fetch_array($result);
    if ( is_array($test) )
    {
        $row = $test;
        // break here if you want.
    }
} while ( sqlsrv_next_result($result) ) ;
sqlsrv_free_stmt($stmt);

 

---

Rob

Answer 4

Thank you for your advice Rob. I tried what you said and still nothing. I don't if this makes a difference or not, but if I take out the "WHERE column IS NOT NULL"... The output is as shown below:

Array
(
  [block_sequence] => 
  [number_of_ties] => 0
  [number_of_q4_ties] => 
  [percent_of_q4_ties] => 
  [number_of_q3_ties] => 
  [percent_of_q3_ties] => 
  [quality] => 1
)

The first row of the output in configuration manager has a "NULL" value for block sequence, number of q4 ties, percent of q4 ties, number of q3 ties, number of q3 ties... values 0 for number of ties and 1 for quality. All the rest of the rows in the query output have data, hence the reason I used "WHERE column IS NOT NULL". Is this likely to cause a problem with the driver? Anything else I could try? I really appreciate your time and efforts!

 

Cheers,

 

Neil

 

Answer 5

neilcoder,

Did you try to simply run through the resultsets and see how many you're getting back? sqlsvr_fetch_array will only return one row, not the entire result set as an array...

 

Thanks,

 

Jonathan

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 6

Thank you for the response Jonathan, I'm afraid that I am a beginner at PHP and not sure how to go about what you said... Is it possible to post some example code or advise me on some good resources to look at?

 

Many thanks,

 

Neil

 

 

Answer 7

Neil

Please post an example of a cross-section of rows in your 'database' table that will provide results for this query, and also the table definition.

Your php code is very straightforward, I can't see anything wrong with it.  So it's probably a SQL issue, and I'd like to try it out.  You say it works in Management Studio, so there must be a difference somewhere.

I suppose we can't rule out a driver error, if you are certain there are results but you don't receive any, with nothing in the php log file, and nothing in sqlsrv_errors().

Why don't you try calling the php function var_dump($row) to see what type and value $row contains after the call to sqlsrv_fetch_array()?

If it is NULL, the driver has not returned any rows, and there is no error.

If it is false, sqlsrv_errors() should contain an array.

If it is an array, a row was returned.

If the script bombs for any reason, your php log will have a message, as long as you have not disabled logging.

To loop through all the returned rows, call sqlsrv_fetch_array in a loop - here is an extension to my previous example:

The 'PRE' statement is interpreted differently by browser, so just output a plain text document - place this header before printing anything:

header('Content-Type: text/plain');
...
$conn = sqlsrv_connect(...);
...
$result = sqlsrv_query($conn,$tsql);
if( !$result )
{
    exit("Error in query preparation/execution.\n" . print_r( sqlsrv_errors(), true));
}

do
{
    while( ($row = sqlsrv_fetch_array($result)) )
    {
        print_r($row);
    }
    if ( $row===NULL )
    {
        echo "There are no more rows.\r\n";
    }
    else if ( $row===false )
    {
        echo "ERROR!: " . print_r( sqlsrv_errors(), true );
    }
} while ( ($next=sqlsrv_next_result($result)) ) ;

if ( $next === false )
{
    echo "Result error: " . print_r(sqlsrv_errors(), true);
}
else if ( $next === NULL )
{
    echo "No more results.\r\n";
}

sqlsrv_free_stmt($result);

echo "End of script\r\n";

I haven't tested this, please correct any syntax errors.

---

Rob

Answer 8

Thank you for the script! Good news the script worked first time, as I tried it will another simple select query. Unfortunately I got the below result:

 

Output without "WHERE NOT NULL" clause:

Array
(
  [0] => 
  [block_sequence] => 
  [1] => 0
  [number_of_ties] => 0
  [2] => 
  [number_of_q4_ties] => 
  [3] => 
  [percent_of_q4_ties] => 
  [4] => 
  [number_of_q3_ties] => 
  [5] => 
  [percent_of_q3_ties] => 
  [6] => 1
  [quality] => 1
)
ERROR!: Array
(
  [0] => Array
    (
      [0] => 22003
      [SQLSTATE] => 22003
      [1] => 0
      [code] => 0
      [2] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range
      [message] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range
    )

)
No more results.
End of script

 

Output with "WHERE NOT NULL CLAUSE":

ERROR!: Array
(
  [0] => Array
    (
      [0] => 22003
      [SQLSTATE] => 22003
      [1] => 0
      [code] => 0
      [2] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range
      [message] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range
    )

)
No more results.
End of script

 

I assume what you mean by cross-section is how the data looks in management studio. Here is the output of the query:

 

block_sequence | number_of_ties | number_of_q4_ties | percent_of_q4_ties | number_of_q3_ties | percent_of_q3_ties | quality

0 1108 170 15.34296 37 3.33935 1

1 1094 231 21.11517 66 6.032907 1

2 1109 251 22.633 53 4.77908 1

3 1116 272 24.37276 52 4.659498 1

4 1087 290 26.67893 62 5.703772 3

5 1072 255 23.78731 48 4.477612 1

6 1080 255 23.61111 50 4.62963 1

7 1073 294 27.39981 56 5.219012 3

8 1089 208 19.10009 46 4.224059 1

 

I'm just trying to work out how to get the table definition in SQL management studio and I shall try the var_dump function. Just thought I'd shoot off a quick reply in the meantime in case you see something I can't see....

 

 

Cheers,

 

Neil

Answer 9

Here is what I think you mean by table definition... 

• Column Name| Data Type | Allow Nulls
• tie_parent_id uniqueidentifier Unchecked
• image_sequence int Checked
• gps_lat decimal(8, 5) Checked
• gps_long decimal(8, 5) Checked
• geo_post smallint Checked
• geo_sample smallint Checked
• priority tinyint Unchecked
• quality tinyint Unchecked
• validated binary(1) Unchecked
• ycoord_max smallint Unchecked
• ycoord_min smallint Unchecked
• rotten tinyint Unchecked
• split tinyint Unchecked
• wheel_cut tinyint Unchecked
• broken tinyint Unchecked

I'm sorry my last message was a bit messy... Here is a cross section again... This is without using "IS NOT NULL"....

 

 

• block_sequence | number_of_ties | number_of_q4_ties | percent_of_q4_ties | number_of_q3 ties | percent_of_q3_ties | quality |
• NULL 0 NULL NULL NULL NULL 1
• 0 1108 170 15.34296 37 3.33935 1
• 1 1094 231 21.11517 66 6.032907 1
• 2 1109 251 22.633 53 4.77908 1
• 3 1116 272 24.37276 52 4.659498 1
• 4 1087 290 26.67893 62 5.703772 3
• 5 1072 255 23.78731 48 4.477612 1

Any feed back would be greatly appreciated!

 

Cheers,

Neil

 

Answer 10

Why don't you try calling the php function var_dump($row) to see what type and value$row contains after the call to sqlsrv_fetch_array()?

Hi there,

I tried to use the var_dump($row) and with the "IS NOT NULL" clause the result was... bool(false).

WITHOUT the "IS NOT NULL" the output was:

"array(7) { ["block_sequence"]=> NULL ["number_of_ties"]=> int(0) ["number_of_q4_ties"]=> NULL ["percent_of_q4_ties"]=> NULL ["number_of_q3_ties"]=> NULL ["percent_of_q3_ties"]=> NULL ["quality"]=> int(1) }"

I noticed when I re-ran the query in Management Studio that the message WITHOUT the "WHERE WHERE SUB_T.block_sequence IS NOT NULL" clause and in the message tab was the following message:

"Warning: Null value is eliminated by an aggregate or other SET operation.

(35 row(s) affected)"

With the "NOT NULL" clause the message did not appear... This probably isn't significant but I am really desperate to try and solve this issue... 

So:

• The query outputs fine in SQL Server Management Studio without errors
• Does the code in my script output the information correctly... I have tried to output the query in a table format using while($row = sqlsrv_fetch_array( $result )) , but I get the same result of my previous scripts where nothing is outputted...

Is there anything else I can try or do you think I will have to completely re-write the query?

Any advice would be welcomed!

 

Many thanks,

Neil

Answer 11

Hi Neil

Thanks for the feedback.  I think the error from sqlsrv_errors() that you captured explains the answer, it's something I should have spotted from your query.

The reason you receive a row without "WHERE NOT NULL" is because it does not contain any numbers.  It goes wrong when numbers are returned because they are probably too big (contain too many digits of precision) for the "real" data type, at some stage.

So, if you change your script, for example, if you use cast(tie_q_4.number_of_ties as float) instead of real, and declare your variables as float instead of real, it should work.

The data type DECIMAL(8,5) can contain a value which is beyond the capacity of a "real".

The error can occur when a conversion would truncate the numeric value.

Unfortunately it looks like you will need to rewrite (or just find/replace real to float) in  your query, because it explicitly casts to "real"s.

As far as management studio is concerned, I'm not sure why it works there and not in php.  Your values all look within the 'real' range, but it might be the way the Native Client driver is handling the formulas, or binding the result columns within the query.

 

 

---

Rob

Answer 12

Thank you Rob, Johnathan and Brian for all your help and time! I shall try out your suggestion Rob and let you know on the result...

 

Cheers,

 

Neil

Answer 13

Just to let you know Robert that your solution worked!! Thank you so much!! Really appreciate all your help with this!!

 

Cheers,

 

Neil

Answer 14

Great, glad to hear it.  Thanks for the update Neil.

---

Rob

Answer 15

Hi Robert, I'm spanish and I don't speek english very well, so i will try to ask you something hehe.

I have the same problem and I can't fix it.

Array ( [0] => Array ( [0] => 22003 [SQLSTATE] => 22003 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Valor numérico fuera del intervalo [message] => [Microsoft][SQL Server Native Client 10.0]Valor numérico fuera del intervalo ) )

Valor númerico fuera del intervalo -> Numeric value out of range ;)

I've got a REAL value that is a percentage... is that the error?

If is this the error, what can i do? Change the type to INT or something?

Thank you.