Robert Francis
asked on
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:
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
Here is a screen shot of the php.ini file showing the extensions
Here is a screen shot of my phpinfo
Any ideas?
<!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>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
Here is a screen shot of the php.ini file showing the extensions
Here is a screen shot of my phpinfo
Any ideas?
ASKER
I am still only getting one "Test Text"
ASKER
BTW, when I do:
print_r(PDO::getAvailableD
it returns
Array ( [0] => sqlsrv )
print_r(PDO::getAvailableD
it returns
Array ( [0] => sqlsrv )
Before your <!DOCTYPE> declaration, put this in to see what error you're getting:
<?php
error_reporting(E_ALL);
?>ASKER
Nothing happens. See screen shot
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...
http://php.net/manual/en/pdostatement.bindparam.php
Also, try a var_dump($test); to see what you get instead of the echo.
<?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();
?>http://php.net/manual/en/pdostatement.bindparam.php
Also, try a var_dump($test); to see what you get instead of the echo.
ASKER
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.
ASKER
Lets say I dump the PDO idea and do it the old fashioned way:
I still get the same results
<?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>I still get the same results
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 \
ASKER
<!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>I changed the \ to \\.
<?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;
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.
ASKER
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.
What is:
HTML;
echo <<<HTML
No closing php (?>)
Not sure what is going on.
ASKER
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.
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.
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
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
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.
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>";
}
}
}ASKER
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>ASKER
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;" ASKER
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.
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
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;Databas e=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;Da tabase=tes tdb", "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.databa se.windows .net;Datab ase=testdb ", "UserName@12345abcde", "Password");
You do have the correct drivers, I assume?
http://php.net/manual/en/ref.pdo-sqlsrv.php
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.
The drivers you're using, means you must be using IIS, and not Apache. Is that right?
ASKER
That is correct, but we have been testing using the non-PDO way for the last couple of post
ASKER
When I was trying the PDO way I did everything in those list.
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');
}ASKER
Just a blank page.
Maybe it wasn't meant to be.
Maybe it wasn't meant to be.
ASKER
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
This is absolutely insane that I can't make a simple connection to a database
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.
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);
?>ASKER
OK I add this:
if (function_exists('mssql_co
echo "Functions are available.<br />\n";
} else {
echo "Functions are not available.<br />\n";
}
and it returned "Functions are not available."
if (function_exists('mssql_co
echo "Functions are available.<br />\n";
} else {
echo "Functions are not available.<br />\n";
}
and it returned "Functions are not available."
ASKER
Where do I get SQLNCLI10 and what do I put in my php.ini for it to work?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Are there any other sections of the phpinfo you would like to see?
ASKER
ASKER
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));
}
ASKER
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.
ASKER
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.
ASKER
The example from php.net that I was talking about
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!!
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 " ".
Open in new window