Solved

Connecting to SQL Server 2012 Using PHP OOP

Posted on 2014-01-28
9
1,224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 110

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

734 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