Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQLSRV query doesn't work with PHP

Status
Not open for further replies.
lhatwwp

lhatwwp

Technical User
Oct 23, 2007
79
US
Hello,

I'm trying to create a page with a list of column names from a SQL table. The query works in SQL but not in PHP... can anyone offer some help?

Code: 
<html>
<body>
Testing
<?PHP 

$connectionInfo = array("UID" => "me_myslef", "PWD" => "my_password", "Database"=>"my_db","ReturnDatesAsStrings" => true);
$db = sqlsrv_connect("my_server",$connectionInfo) or die("Unable to connect to server");

$result = sqlsrv_query($db,"SELECT  
   
    [Column Name] = c.name, 
    [Description] = ex.value  
FROM  
    sys.columns c  
LEFT OUTER JOIN  
    sys.extended_properties ex  
ON  
    ex.major_id = c.object_id 
    AND ex.minor_id = c.column_id  
    AND ex.name = 'MS_Description'  
WHERE  
    OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
    AND OBJECT_NAME(c.object_id) = 'DailyTotals' 
ORDER  
    BY OBJECT_NAME(c.object_id), c.column_id");				


while ($row = sqlsrv_fetch_array($result)){

echo $row['Column Name'];
  

}
?>
</body>
</html>
 
Sort by date Sort by votes
The query works in SQL but not in PHP
Click to expand...

That's a little vague. Have you checked if your query is returning rows, are you sure the connection is taking place. Are you getting any errors when you attempt to run this?

I'd start by checking if the query is actually being performed:

Code: 
$result = sqlsrv_query($db,"...") or die (sqlsrv_errors());

If that returns no errors, then check if the query is returning any rows.

Code: 
if(sqlsrv_has_rows($result)){ echo "1 or more rows have been returned";


Finally try outputting the $row variable, see if what you expect inside it is really there:

Code: 
while(...)
{
echo "<pre>";
print_r($row);
echo "</pre>"
}

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
Thanks for the reply Phil.

After your suggestions and reading some from the MS site
I was able to get the first column to display in PHP using sqlsrv_get_field. However I wasn't successful in retrieving the second column. Here's the code I had limited success with. To get the second column it seems I have to iterrate through a nvarchar stream. I can't seem to make it work. Have a look at this
If you can offer an explaination or help please let me know. By the way... it seems a lot of this is due to the new data types in MS SQL2008.

Thanks,
Lou


Code: 
<html>
<body>
TESTING
<BR>

		

<?php
   $connectionInfo = array("UID" => "me_myself", "PWD" => "my_pwrd", "Database"=>"my_db","ReturnDatesAsStrings" => true);
   $db = sqlsrv_connect("my_srvr",$connectionInfo) or die("Unable to connect to server");
   

$result = sqlsrv_query($db,"SELECT [Column Name] = c.name, 
    				   [Description] = ex.value  
				   FROM  
    				   sys.columns c  
                                   LEFT OUTER JOIN  
                                   sys.extended_properties ex  
                                   ON  
                                   ex.major_id = c.object_id 
                                   AND ex.minor_id = c.column_id  
                                   AND ex.name = 'MS_Description'  
                                   WHERE  
                                   OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
                                   AND OBJECT_NAME(c.object_id) = 'DailyTotals' 
                                   ORDER  
                                   BY OBJECT_NAME(c.object_id), c.column_id" , array());

/* uncommnet these lines to see the row count results */

   $row_count = sqlsrv_num_rows( $result );
   
   if ($result !== NULL) {
      $rows = sqlsrv_has_rows( $result );
   
      if ($rows === true)
         echo "\nthere are \n$row_count\n rows\n";
      else 
         echo "\nno rows\n";
   }



$j=1;

while ($row = sqlsrv_fetch($result)) {
$ColName = sqlsrv_get_field( $result, 0);



print "<BR>";
echo "Row: ". $j . " " . $ColName;
$j=$j+1;
}


sqlsrv_free_stmt( $result);
sqlsrv_close( $db);



?>
</body>
</html>
 
Upvote 0 Downvote
It seems that the problem I'm having comes down to using SQLSRV_FETCH_ARRAY with table type functions. I have some nice functions in SQL that provide results from larger chunks of data. When I use SQLSRV_FETCH_ARRAY or BOTH or NUMERIC the result is a successful execution of the statement, a row count of not null but not 1 either (weird) and no results that I can use.

I am using PHP 5.2.14 on Windows 2008 and SQL2008R2.

Has anyone else seen this problem and if so do you know how to work around it or properly construct the syntax so a usable recordset is returned?

TIA,
Lou
 
Upvote 0 Downvote
The question would be: What are you getting in the $row array?

As I suggested before have you output the entire $row variable just to see what's in it?

Have you tried using the more standard format of naming fields;

Code: 
SELECT c.name as Column_Name...




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
Hi Phil,

I have tried SELECT c.name as Column_Name... and there's nothing in the result that I can see. If I add a simple echo $1; in the while loop I see it count... but that's it. Nothing else.

Thanks again,
Lou
 
Upvote 0 Downvote
Do this in the while loop:

Code: 
echo "<pre>";
print_r($row);
echo "</pre>"

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
I get nothing from your code... I have done it with a dynamic number and it will increment.

Code: 
echo "<pre>";
print_r($row);
echo "</pre>"

I also tried the following with no success...

Code: 
$row = sqlsrv_fetch_array($result);
$name = sqlsrv_get_field( $result, 0);
echo $name;
 
Upvote 0 Downvote
You get nothing in your $row variable if you do this:
Code: 
while ($row = sqlsrv_fetch_array($result)) {
 echo "<pre>";
 print_r($row);
 echo "</pre>"
}

If that's the case, then your query is either not returning anything, or not getting run at all.

Make sure you have error display set to E_ALL in your PHP.ini just to make sure there's nothing else going on. Or go check the server error logs.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
Phil,

I beleive you now understand why I'm scratching my head... No error messages and no results. I just want to throw my hands up in the air!!!

Lou
 
Upvote 0 Downvote
O.K. try this, lets see if we can coax any errors out:

Code: 
function get_error() {

 $errors = sqlsrv_errors();
 $errtxt = "Error occured: ";
    foreach( $errors as $error) {
       $errtxt .= $error["SQLSTATE"] . ", ";
       $errtxt .= $error["code"] . ", ";
       $errtxt .= $error["message"] . "<br>";
    }
 return $errtxt;
}
...

while ($row = sqlsrv_fetch_array($result)) {
if($row===false){ die(get_error()); }
else ( print_r($row); }
}


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
I'm sure you will be shocked by the results... I get Testing and nothing more.

Here's my code

Code: 
<html>
<body>
<?PHP 
echo "Testing";

$connectionInfo = array("UID" => "me_myself", "PWD" => "my_pwrd", "Database"=>"my_db");
$db = sqlsrv_connect("my_srvr",$connectionInfo) or die("Unable to connect to server");

$result = sqlsrv_query($db,"SELECT [Column Name] = c.name, 
    				   [Description] = ex.value  
				   FROM  
    				   sys.columns c  
                                   LEFT OUTER JOIN  
                                   sys.extended_properties ex  
                                   ON  
                                   ex.major_id = c.object_id 
                                   AND ex.minor_id = c.column_id  
                                   AND ex.name = 'MS_Description'  
                                   WHERE  
                                   OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
                                   AND OBJECT_NAME(c.object_id) = 'my_table' 
                                   ORDER  
                                   BY OBJECT_NAME(c.object_id), c.column_id");			


function get_error() { 
$errors = sqlsrv_errors(); 
$errtxt = "Error occured: ";    
    foreach( $errors as $error) {       
       $errtxt .= $error["SQLSTATE"] . ", ";
       $errtxt .= $error["code"] . ", ";
       $errtxt .= $error["message"] . "<br>";
    } 
  return $errtxt;
}

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_BOTH)) {
if($row===false){die(get_error());}
else { print_r($row); }
 }

?>
</body>
</html>
 
Upvote 0 Downvote
Well, I am stumped now.

As a last resort, perhaps you can try to use a different driver to access and connect to the database.

Try using the mssql functions and see if you get the desired results. They should be available in most standard installations of PHP.

MSSQL Functions @ PHP.net

If it works, it may point to a problem with either the sqlsrv driver or the implementation of the functions.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.
 
Upvote 0 Downvote
Thanks Phil,

I did use the MSSQL driver prior to upgrading to SQL2008. I don't recall the exact problem I had with the MSSQL but it didn't play well with SQL 2008. Now it seems both the MSand SQLSRV drivers have problems with SQL2008.

Cheers!
Lou
 
Upvote 0 Downvote
are you certain your query is good? not for sqlserver but for the driver. there are sometimes differences and/or limitations with some drivers. consider testing $result.

Code: 
if(!$result) print_r( sqlsrv_errors( SQLSRV_ERR_ALL  ) );

consider also not passing a parameters value when no placeholders are provided. I have not looked at the M$ code to see whether this use case is gracefully handled.
 
Upvote 0 Downvote
I finally found the problem. The data type was causing the problem. Once I CAST the results as NChar data types everything worked.

NEW CODE
Code: 
"SELECT cast(c.name as nchar) as [Column Name], cast(ex.value as NCHAR) as [Description]  
FROM...

OLD CODE
Code: 
SELECT [Column Name] = c.name, [Description] = ex.value
FROM...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
250
SashaBuilder3
SashaBuilder3
PeDa
  • Locked
  • Question
Unwanted spaces in php HTML mail 1
Replies
4
Views
233
PeDa
PeDa
Mandy_crw
  • Locked
  • Question
How to send sms in PHP using usb gsm modem?
Replies
1
Views
241
vishvajit
vishvajit
Wand850
  • Locked
  • Question
Parsing XML with multiple namespaces using PHP
Replies
3
Views
182
spamjim
spamjim
lflynnlrf
  • Locked
  • Question
wordpress/ shortcode/ PHP duplicating code
Replies
1
Views
118
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top