Connect to Sql Express using PDO for PHP page

I am trying to connect a php page to a SQL Express database. Here is the code so far:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php $test = "Test Text";?>

<p> <?php echo $test; ?></p>

<?php
// Create connection
$conn = new PDO("sqlsrv:Server=10.10.0.2\EXACTSQLEXPRESS;Database=dbo.intranet", "portal", "password");
//$conn = new mssql_connect($servername, $username, $password, $dbname);?>

<p> <?php echo $test; ?></p>

<?php
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT jobno FROM issues";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Job Number: " . $row["jobno"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>
</body>
</html>

Open in new window


When I go to this page it only shows me the first "Test Text" but not the second. This is telling me the connection string is messed up.

Here is a screen shot of the .dll file in my ext folder

Ext Folder
Here is a screen shot of the php.ini file showing the extensions

phpini
Here is a screen shot of my phpinfo

phpinfo
Any ideas?
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

F PCommented:
I think you didn't escape your backslash with another backslash. Try this:

$conn = new PDO("sqlsrv:Server=10.10.0.2\\EXACTSQLEXPRESS;Database=dbo.intranet", "portal", "password");

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
I am still only getting one "Test Text"
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
BTW, when I do:

print_r(PDO::getAvailableDrivers());

it returns

Array ( [0] => sqlsrv )
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

F PCommented:
Before your <!DOCTYPE> declaration, put this in to see what error you're getting:

<?php

error_reporting(E_ALL);

?>

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Nothing happens. See screen shot

screen
0
F PCommented:
You can also try to prepare your statements to the PDO, which is really  how you should use that driver, instead of the OO style MySQLi style of querying...

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

Open in new window


http://php.net/manual/en/pdostatement.bindparam.php

Also, try a var_dump($test); to see what you get instead of the echo.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
I have no idea what you just said. Obviously I am new to PHP being that I can't even connect to a database. The only experience I have is using ASP classic.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Lets say I dump the PDO idea and do it the old fashioned way:

<?php error_reporting(E_ALL);?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<?php
$test = "test text";
$servername = "10.10.0.2\EXACTSQLEXPRESS";
$username = "portal";
$password = "password";
$dbname = "intranet";
?>

<p> <?php echo $test; ?></p>

<?php
// Create connection
$conn = new mssql_connect($servername, $username, $password, $dbname);?>

<p> <?php echo $test; ?></p>

<?php
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT jobno FROM issues";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Job Number: " . $row["jobno"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>
</body>
</html>

Open in new window


I still get the same results
0
F PCommented:
Click view source on the page, and show it please. Also, if you're going to use " double quotes, you need to put \\ and not \
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>

<p> test text</p>

Open in new window


I changed the \ to \\.
0
F PCommented:
<?php 

error_reporting(E_ALL);

$test = "test text";

echo <<<HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<p>{$test}</p>

HTML;

// Create connection
$servername = "10.10.0.2\\EXACTSQLEXPRESS";
$username = "portal";
$password = "password";
$dbname = "intranet";
$conn = new mssql_connect($servername, $username, $password, $dbname);

if(!$conn)
{
    $test = "Error with the db ya'll";
}

echo <<<HTML

<p>{$test}</p>

HTML;

// Check connection -- no need, you'll know.
/*
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
*/

$sql = "SELECT jobno FROM issues;";
$result = $conn->query($sql);

if ($result->num_rows == 0) {
    echo "0 results";
}
else
{
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Job Number: {$row["jobno"]}<br>";
    }
}
// $conn->close(); // not necessary

echo <<<HTML
</body>
</html>
HTML;

Open in new window

0
Dave BaldwinFixer of ProblemsCommented:
I suggest you make a test page using the non-PDO driver 'sqlsrv' to make sure your system is working.  I have several pages working using the 'sqlsrv' driver but using the same info, I can not get the 'pdo_sqlsrv' driver to work.  Not with your code or the demo code from the 'sqlsrv' documentation.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
I copied and pasted that and received a blank screen. Even the source was black.

What is:

HTML;
echo <<<HTML
No closing php (?>)

Not sure what is going on.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
David,

That is where I am at right now. Still having the same effect. It seems to fail at the connection string. I know the connection info is correct because it is the same information I use in my ASP pages. You can see in my post two up from this one the non-pdo code.
0
F PCommented:
That's called HEREDOC syntax. it tells PHP that everything is like a double quoted string " " between <<<HTML and HTML; *****

HTML;

****must both match what's after <<< (so <<<EOT would need EOT;) to tell it to stop, and it must not have anything else on the line with it, except the line break, and start at the first column, i.e., no indentation.

https://php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc

It's redundant to close the PHP tag if the script is already going to do it for you, and it can cause problems with non visible characters and includes unless you're output buffering.

http://programmers.stackexchange.com/questions/89553/closing-tag-on-php-files
0
F PCommented:
If you didn't put a line break at the end of my code, or add one extra line before saving, it will error out and be blank, correct.
0
F PCommented:
Also maybe change to this on the conditional:

if($result)
{
if ($result->num_rows == 0) 
{
    echo "0 results";
}
else
{
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Job Number: {$row["jobno"]}<br>";
    }
}
}

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Both the old and new conditional returned this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<p>test text</p>

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
FYI - This is the connection string for an ASP page that works:

connectstr.open = "Provider=SQLNCLI10;Server=SERVER\EXACTSQLEXPRESS;Database=Intranet;Uid=portal; Pwd=password;" 

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
I even changed the server name and username in the connection string to just junk and it still acts the same. It is if that information is not even being checked.
0
F PCommented:
http://php.net/manual/en/ref.pdo-sqlsrv.php

It's getting an error, and you have your php.ini set to Off on display errors. You'd have to use ini_set() like this:

ini_set('display_errors', '1');

before or after the error_reporting to see them.

Also follow what they say here: http://php.net/manual/en/ref.pdo-sqlsrv.connection.php

Examples

Example #1 PDO_SQLSRV DSN examples

The following example shows how to connecto to a specified MS SQL Server database:

$c = new PDO("sqlsrv:Server=localhost;Database=testdb", "UserName", "Password");
The following example shows how to connect to a MS SQL Server database on a specified port:

$c = new PDO("sqlsrv:Server=localhost,1521;Database=testdb", "UserName", "Password");
The following example shows how to connecto to a SQL Azure database with server ID 12345abcde. Note that when you connect to SQL Azure with PDO, your username will be UserName@12345abcde (UserName@ServerId).

$c = new PDO("sqlsrv:Server=12345abcde.database.windows.net;Database=testdb", "UserName@12345abcde", "Password");
0
F PCommented:
You do have the correct drivers, I assume?
http://php.net/manual/en/ref.pdo-sqlsrv.php
Installation

The PDO_SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file. The PDO_SQLSRV download comes 8 driver files, four of which are for PDO support. If you are running non-thread-safe PHP (PHP 5.3), use the php_pdo_sqlsrv_53_nts.dll file. (You should use a non-thread-safe version if you are using IIS as your web server). If you are running thread-safe PHP, use the php_pdo_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_pdo_sqlsrv_54_nts.dll or php_pdo_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.

The most recent version of the driver is available for download here: » SQLSRV 3.0 download. If you need support for PHP 5.2 and/or PHP compiled with VC6, use the 2.0 release of the driver: » SQLSRV 2.0 download.

For more information about system requirements, see » SQLSRV System Requirements.

The PDO_SQLSRV extension is only compatible with PHP running on Windows. For Linux, see ODBC and » Microsoft's SQL Server ODBC Driver for Linux.
0
F PCommented:
The drivers you're using, means you must be using IIS, and not Apache. Is that right?
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
That is correct, but we have been testing using the non-PDO way for the last couple of post
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
When I was trying the PDO way I did everything in those list.
0
F PCommented:
No go with this?

<?php
// Server in the this format: <computer>\<instance name> or 
// <server>,<port> when using a non default port number
$server = 'SERVER\EXACTSQLEXPRESS';

// Connect to MSSQL
$link = mssql_connect($server, 'portal', 'password');

if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}

Open in new window

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Just a blank page.

Maybe it wasn't meant to be.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
I have switched to php_sqlsrv_56_nts.dll based on another thread and it still does not work.

This is absolutely insane that I can't make a simple connection to a database
0
Dave BaldwinFixer of ProblemsCommented:
Frank, the 'mssql' driver does not work on Windows with PHP 5.3 or greater.  The SQL Native Client is required for 'sqlsrv'.  If you have SQLNCLI10 on that machine then 'sqlsrv' should be working.
This demo from the SQLSRV documentation works fine on my machine while the PDO-SQLSRV does not.
<?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 = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$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

0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
OK I add this:
if (function_exists('mssql_connect')) {
    echo "Functions are available.<br />\n";
} else {
    echo "Functions are not available.<br />\n";
}

and it returned "Functions are not available."
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Where do I get SQLNCLI10 and what do I put in my php.ini for it to work?
0
Dave BaldwinFixer of ProblemsCommented:
According to your ASP connection string, you are already using it.  Is the ASP string from the same machine or is that on a different machine?  https://msdn.microsoft.com/en-us/data/ff658533.aspx

From http://php.net/manual/en/intro.mssql.php ...
This extension is not available anymore on Windows with PHP 5.3 or later.
0
F PCommented:
I posted where to download the drivers already in a previous post.

http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx

If your PHP is 5.3 or earlier,

OR

you use IIS,

then you should be using _nts_ or not thread safe drivers. Otherwise you use the _ts_ (thread safe).

http://php.net/manual/en/ref.pdo-sqlsrv.php

If you are running thread-safe PHP, use the php_pdo_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_pdo_sqlsrv_54_nts.dll or php_pdo_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.

Microsoft says:

Microsoft Drivers for PHP for SQL Server...

Version 3.2 supports PHP 5.6, 5.5, and 5.4
Version 3.1 supports PHP 5.5 and 5.4
Version 3.0 supports PHP 5.4



Maybe if you post a phpinfo(); output please?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
phpinfoextfolderphpini
Are there any other sections of the phpinfo you would like to see?
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
sqlsrv
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Here is where we stand now. I installed Microsoft ODBC Driver 11 for PHP for SQL Server. I changed the function from mssql_connect() to sqlsrv_connect(). I rebooted the server. I changed the connection to:

$serverName = "server\exactsqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"intranet", "UID"=>"portal", "PWD"=>"password");
// Create connection
$conn = sqlsrv_connect( $serverName, $connectionInfo);
// Check connection
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

Open in new window

This is what I get now. Looks like a step forward.

cp
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
So I changed server\exactsqlexpress to server\\exactsqlexpress and now it works. Not sure why php.net does not show this being needed in the example.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Thank you Frank and Dave for putting in the time to help me work through this problem. I hope I was not a complete pain and that you guys will be there for me in the future.
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
The example from php.net that I was talking about
ex
0
F PCommented:
You were not, and by far and above not the most difficult person to deal with. There are many many issues and personalities to go along with them in IT, and I'm glad that you were able to resolve it! I'm also glad to have helped!!
0
F PCommented:
When you use double quotes, the \ is an escape character and ignored by the interpreter. So when you threw in two \\ it told php to leave one there. Also it would've worked if you used single quotes ' ' instead of the doubles on the example " ".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.