Solved

Connecting to SQL Server 2012 Using PHP OOP

Posted on 2014-01-28
9
1,137 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 108

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 82

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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 82

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 82

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 82

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now