Solved

Connecting to SQL Server 2012 Using PHP OOP

Posted on 2014-01-28
9
1,167 Views
Last Modified: 2014-01-29
I've got my test data all set up, I've got a table of data ready to be revealed via my first PHP OOP adventure. Problem is, I can't decipher how to establish a connection between my page and the database using an OOP approach.

I've seen several web articles on it, but I get skittish when I see the SQL Server Management Studio 2008 version having just spent several hours uploading the 2012 version after running into problems.

So, how do I set up a database connection to my SQL database using an OOP PHP approach?
0
Comment
Question by:brucegust
  • 4
  • 4
9 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39815847
You can use PDO.  This article is about MySQL, not SQL Server, but the principles are all the same.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39815871
You pretty much have to use the Microsoft SQL drivers for PHP.  They have one version for Windows and another that works on RHEL.  http://www.microsoft.com/en-us/download/details.aspx?id=20098  They include the PDO drivers.
0
 

Author Comment

by:brucegust
ID: 39816075
Yo, Dave!

I've got the drivers and I've installed them in the appropriate directory (C/Program Files/iis express/PHP/v5.3/ext) but I'm stuck now as far as where I go from here.

I've looked at some tutorials and it would appear as though I have to edit my ini file in order to activate the drivers, but I can't tell and then I'm still struggling to figure out the actual syntax that facilitates the actual connecting to the database.

Thoughts?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:brucegust
ID: 39816142
Progress, I think...

I added the necessary entry into my php.ini file like so...

; Windows Extensions
; Note that ODBC support is built in, so no dll is needed for it.
; Note that many DLL files are located in the extensions/ (PHP 4) ext/ (PHP 5)
; extension folders as well as the separate PECL DLL download (PHP 5).
; Be sure to appropriately set the extension_dir directive.
;

extension=php_sqlsrv_53_ts.dll

I found a thread you had contributed to that referenced the importance of the Thread Safe version setting (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28102807.html) and I confirmed from my php.ini file that the Thread Saftey feature has been disabled, hence the "ts" as opposed to the "nts."

So, what's next?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39816183
Since you are using IIS-Express, I would guess that you do not have a "Pro" version of Windows.  I think that since you are running IIS, you probably need the NTS version of the driver.  

The code that is required is in the CHM help file for the driver.  It is also on the PHP man pages:  http://us2.php.net/manual/en/book.sqlsrv.php
0
 

Author Comment

by:brucegust
ID: 39818562
OK, Dave, I think we're poised on the threshold of greatness, but not quite there yet.

First off, I've confirmed by looking at my phpMyInfo page that the sqlsrvr support has been enabled so the correct files have been referenced in the php.ini file, so I think we're good to go there.

I'm using this code to connect:

<?php
/*
Connect to the local server using Windows Authentication and specify
the AdventureWorks database as the database in use. To connect using
SQL Server Authentication, set values for the "UID" and "PWD"
 attributes in the $connectionInfo parameter. For example:
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"AdventureWorks");
*/
$serverName = "(CORP-LOANER114\SQLEXPRESS)";
$connectionInfo = array( "Database"=>"symbion_cer");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )
{
     echo "Connection established.\n";
}
else
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}

//-----------------------------------------------
// Perform operations with connection.
//-----------------------------------------------

/* Close the connection. */
sqlsrv_close( $conn);
?>

Open in new window


...and here's the error I'm getting:

Connection could not be established. Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => -1 [code] => -1 [2] => [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [message] => [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 11.0]Login timeout expired [message] => [Microsoft][SQL Server Native Client 11.0]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => -1 [code] => -1 [2] => [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) ) 

Open in new window


I'm looking at my SQL Server Management Studio and I've got these settings:

Authentication Method: Windows Authentication
User Name: SYMBION/bgust
Database: symbion_cer
Computer Name: CORP-LOANER114

What do you think?
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39818952
I think you have no reason to parenthesis around the server name in...
$serverName = "(CORP-LOANER114\SQLEXPRESS)";

Open in new window

0
 

Author Comment

by:brucegust
ID: 39819242
Dave, I got it!

I used an ODBC approach. Went into my Administrative Tools, set up a connection and BAM!

It's healthy too in that the project I'm working on is using that dynamic so it's good practice.

Thanks for weighing in!
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39819313
You're welcome, glad to help.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question