PHP to query SQL Server 2008 R2

Question

We have a database running on Windows Server 2008 R2, SQL Server 2008 R2. I need to query the database via PHP (I'm also open to other options) and display the data in a web page.

I have tested connectivity with my username, password and server name using a UDL test file. My connection is successful.

My web server has PHP 5.4 installed and working.

I have built a PHP script that is supposed to connect and query the database but I get a server error.

<?php
$myServer = "ipaddress";
$myUser = "username";
$myPass = "password";
$myDB = "dbname"; 

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer"); 

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB"); 

//declare the SQL statement that will query the database
$query = "SELECT fullName, email_address, pos_desc";
$query .= " FROM dbo.EMPLOYEE_VIEW ";
$query .= "WHERE grp_cde='STAFF'"; 

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result); 
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 

//display the results 
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>

Keep in mind the Database server and the Web Server are both running Windows Server 2008 R2 and exist in the same domain.

The only error I receive is: 500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.

Is there anything I can do to troubleshoot why this is not working?

I appreciate any and all help on this question.

The only error I receive is: 500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed. — Chaddly
– Chaddly, Commented Jun 6, 2013 at 13:33
Did you change the Server parameter in first 4 line with correct values ? ipaddress should be either localhost or 127.0.0.1, similarly username and password should be one used in your database server. Just asking if you did change them. — Sumit Gupta
– Sumit Gupta, Commented Jun 6, 2013 at 13:34
Yes, i tested my server name, username and password with a utility that tests for a successful connection. The results came back successful. Although since the msSQL server is running on a different server I am using the ip address of that server and not localhost. — Chaddly
– Chaddly, Commented Jun 6, 2013 at 13:36
I think it might have something to do with the PHP configuration or the msSQL server configuration. Oh by the way our old web server is currently using asp.net to query it and it works. — Chaddly
– Chaddly, Commented Jun 6, 2013 at 13:38

Wesley van Dijken · Accepted Answer · 2013-06-06 13:43:14Z

1

Error 500 usually means you forgot a ; or ', I suggest you check your code carefully for any redundant or missing characters.

Also, turning on php errors in your php.ini file helps alot with fixing these sort of stuff.

answered Jun 6, 2013 at 13:43

Wesley van Dijken

212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chaddly Over a year ago

Thank you for your input Wesley, I am going to look up how to turn on php errors and see if I can pull any additional info for this problem.

Daanvn Over a year ago

@Chaddly You could put <?php ini_set('display_errors', true) ?> at the top of your form, maybe you can see another error then.

Chaddly · Accepted Answer · 2013-06-07 11:56:47Z

0

After pheww... what now? 6 hours? I have it working! The problem was that I needed to set up SQL Server 2008 drivers on my web server and connect using ODBC via my PHP script. I am now pulling and displaying data from my SQL Server 2008 Database like a pro. Thanks for the assistance guys.

odbc_connect("DRIVER={SQL Server Native Client 10.0};Server=serverip;Database=tablename",         "username", "pass");

answered Jun 7, 2013 at 11:56

Chaddly

2671 gold badge6 silver badges18 bronze badges

1 Comment

David Mukoro Over a year ago

i am using a shared windows server 2008 on the cloud. I also have this issue, how can i know if sql server native client 10.0 is installed?Also my connection string is sqlsvr_connect(). Will this also requires native client? Pls I need assitance

Collectives™ on Stack Overflow

PHP to query SQL Server 2008 R2

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related