[RESOLVED] PHP+SQL Server

[sheckel]

I sure hope someone can help me. Here is my script. The only output I am getting is the first echo statement. I get no error messages. Thanks so much!

<?php

echo "This is my SQL Server Connection Project";

//connects to SQL Server

$hostname = "tech-db";
$username = "username";
$password = "**********";
$dbname = "database";
$sqlConn = @mssql_connect($hostname, $username, $password)or die("Database Failed to Respond.");
mssql_select_db($dbname, $sqlConn) or die("Table unavailable");

//create the query

$myquery="Select title, type from book";
$result=mssql_query($myquery, $sqlConn);

echo "This is using fetch_row";

while ($row = mssql_fetch_row($result))
{
echo ("$row[0], $row[1]");
echo "<br>";
}

//create the query again

mssql_select_db($dbname) or die("Table unavailable");
$myquery="Select title, type from book";
$result=mssql_query($myquery, $sqlConn);

echo "This is using fetch_array";

while ($row=mssql_fetch_array($result))
{
echo "row[title], row[type]";
echo "<br>";
}

?>

[ Mod Edit - bpat1434 ] Added [php] tags as well as removed sensitive information.

[wempy]

Hi,

mssql_connect returns a MS SQL link identifier on success, or FALSE on error.

As the if construct can test the result of an assignement you could re-write it:

if($sqlConn = @mssql_connect($hostname, $username, $password)){
// yes, connected to db ok
echo "connected to db ok\n";
mssql_select_db($dbname, $sqlConn);
}else{
// failed to connect
echo "failed to connect to db\n";
exit
}

Though, that is what the "or die('blah')" construct is supposed to do, though I have had 'issues' with it in the past, so prefer the. admittedly more long winded, approach.

cheers.

[sheckel]

Thanks so much for your quick reply. I've changed my code as you've indicated (see below), but I still don't get any messages past my first echo statement.

<?php

echo "This is my SQL Server Connection Project";

//connects to SQL Server

$hostname = "tech-db";           

$username = "username";
$password = "password";
$dbname = "database_name";

if($sqlConn = @mssql_connect($hostname, $username, $password)){
    // yes, connected to db ok
    echo "connected to db ok";
    mssql_select_db($dbname, $sqlConn);
}else{
    // failed to connect
  echo "failed to connect to db";
    exit;
}			

//create the query

$myquery="Select title, type from book";
$result=mssql_query($myquery, $sqlConn);

echo "This is using fetch_array";

while ($row=mssql_fetch_array($result))
{
echo "row[title], row[type]";
echo "<br>";
}

?>

[ Mod Edit - bpat1434 ] Added [php] tags.

[bpat1434]

Umm... may be a stupid question, but isn't the host supposed to be something like "localhost" or "localhost\tech-db"? I'm not the foremost authority on mssql, but that would be a factor right there.

Also, while you're trying to debug some information, remove the "@" from in front of the mssql_connect call, that way errors are shown. Also, think about heightening your error_level dynamically with [man]ini_set[/man]('error_level', E_ALL & E_STRICT); (or just E_ALL for PHP < 5.0).

Then give us errors you see.

[sheckel]

MSSQL is on a one server (tech-db), while PHP is on another server (web). Both servers are in-house only.

Here is my altered code:

<?php

echo "This is my SQL Server Connection Project";

//connects to SQL Server

$hostname = "tech-db.***-****.******.edu";           

$username = "username";
$password = "**********";
$dbname = "database";

if($sqlConn = mssql_connect($hostname, $username, $password)){
    // yes, connected to db ok
    echo "connected to db ok";
    mssql_select_db($dbname, $sqlConn);
}else{
    // failed to connect
  echo "failed to connect to db";
    exit;
}			

//create the query

$myquery="Select title, type from book";
$result=mssql_query($myquery, $sqlConn);

echo "This is using fetch_array";

while ($row=mssql_fetch_array($result))
{
echo "row[title], row[type]";
echo "<br>";
}

?>

Here is the error message I get now.

Call to undefined function mssql_connect() in D:\web\cpt214-25\PHPconnectstoMSSQL.php on line 12

I don't know how to code the dynamic error-level with in-_set (what would the actual code look like?).

Again, thanks so much for any help you can give me. My network administrator and I are pulling our hair out!

Sharon

[EDIT] Never put username(s) and password(s) to live accounts online. It's a huge security issue.

[Roger_Ramjet]

So the mssql extenstions are not installed, from the manual

The extension requires the MS SQL Client Tools to be installed on the system where PHP is installed. The Client Tools can be installed from the MS SQL Server CD or by copying ntwdblib.dll from \winnt\system32 on the server to \winnt\system32 on the PHP box. Copying ntwdblib.dll will only provide access. Configuration of the client will require installation of all the tools.

[bpat1434]

Dynamic error level would look something like this:

<?php
ini_set('error_reporting', E_ALL & E_STRICT); // PHP 5
ini_set('error_reporting', E_ALL); // PHP 4 & below

Now, that's JUST FOR DEBUGGING. Once your site goes live, you want to back the error level down to E_WARNING or less. Actually, you'd want your errors logged to a log file and the display_errors off.

So to recap:

For Debug Mode:

<?php
// Turn display of errors on / off
ini_set('display_errors', 1); // 1 = On; 0 = Off

// Set the error level
ini_set('error_reporting', E_ALL & E_STRCIT); // PHP 5 ::  Debug Mode
ini_set('error_reporting', E_ALL); // PHP 4 ::  Debug Mode
/**/

For Live Mode:

<?php
/** 15 == E_ERROR | E_WARNING | E_PARSE | E_NOTICE **/
// http://us3.php.net/manual/en/ref.errorfunc.php#errorfunc.constants
ini_set('error_reporting', 15); // PHP 4 & 5 ::  Essential
ini_set('display_errors', 0); // Don't display errors
ini_set('log_errors', 1); // Log the errors instead

$path = realpath($_SERVER['DOCUMENT_ROOT'].'/../'); // Get dir above root (should be your user dir)
if($path === false)
  ini_set('error_log', $_SERVER['DOCUMENT_ROOT'].'/.hterrors');
else
  ini_set('error_log', $path.'/errors.log');

It looks confusing, but if you just break it down into its two parts: Debugging and Live, then you'll understand.

[sheckel]

SQL Server Client 2005 has been successfully installed onto the PHP 5.2.0 server.

Ntwdblib.dll from webzila.com has been downloaded and copied to the system32 folder on the PHP server.
Extension=php_mssql.dll has been uncommented in the php.ini file.
The server with PHP has been rebooted…more than once.

I am still getting the same error message: Call to undefined function mssql_connect()

Is there somewhere I can find step-by-step how to do ALL of the steps necessary to connect these two servers? For example, one place says to put the Client Services onto the PHP box OR copy ntwdblib.dll, while other comments seem to indicate both have to be done.

Thanks to all of you helping me out.

Sharon

[bpat1434]

Well, one thing you can do is see if the extension is even loaded. Run a [man]phpinfo/man page to see if the extension itself is installed. Much better than looking at an undefined function for hours 😉

I heard somewhere else that an install of the client tools should take care of everything needed for use. Have you installed the MDAC? Installing the client tools should do this for you.

I have found that a Windows Webserver cannot connect to a Microsoft SQL Database if the webserver is running Microsoft SQL Server Agent. It gives connection and undefined function errors. Hope this helps some people.

Did you see that in the comments about MSSQL support in the PHP manual?

[Roger_Ramjet]

Undefined function error means that the mssql library is not loading.

Now you have downloaded ntwdlib.dll and I presume it is version 8.x, but have you altered the NTFS filesystem privileges for it so that IIS/PHP can execute it? Remember, it is in system32 so inherits protected status for that folder while IIS generally runs as a low-grade anonymous user. If the dll had been installed with php then it would have adequate security settings, but after a manual copy it does not.

[sheckel]

PHP + MS SQL Server – SUCCESS!

Thanks to all!

PHP is on an IIS server, SQL Server is on a different server in the same domain.

Changes made to PHP
o Add MS SQL Server Client Tools
o Uncomment the extension=php_mssql.dll statement in the php.ini file
o Add system variable PHPRC value c:\php
o Use ntwdblib.dll version 2000.80.194.0 (ver 2000.2.8.0 did not work); copied the files to c:\windows\system32 directory on the PHP server

Changes made to IIS
o Set up IUSER_machinename account with password

Changes made to MS SQL Server
o Create IUSER_machinename account with password – synchronize with IIS password
o Add IUSER as login
o Add IUSER as user on database, including permissions

After all of these steps were followed, the following PHP script ran correctly:

<html><head><title>MsSQL Table Viewer</title></head><body>
<?php
$db_host = hostname';
$db_user = 'username';
$db_pwd = 'password';
$database = 'dbname';
$table = 'tablename';

if (!mssql_connect($db_host, $db_user, $db_pwd))

die("Can't connect to database");
if (!mssql_select_db($database))

die("Can't select database");

// sending query
$result = mssql_query("SELECT * FROM {$table}");
if (!$result) {

die("Query to show fields from table failed");
}

$fields_num = mssql_num_fields($result);
echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headersfor($i=0; $i<$fields_num; $i++)
{

$field = mssql_fetch_field($result);    
echo "<td>{$field->name}</td>";

}
echo "</tr>\n";
// printing table rows

while($row = mssql_fetch_row($result))
{

echo "<tr>";

// $row is array... foreach( .. ) puts every element

// of $row to $cell variable

foreach($row as $cell)

echo "<td>$cell</td>";

echo "</tr>\n";
}
mssql_free_result($result);
?>

</body></html>[/COLOR]

[bradgrafelman]

Note that copying dll files for use with PHP into system folders is deprecated (and has been for a while now), and that you should instead keep them in your c:\php\ folder (or wherever) and add that main folder to your system's PATH environment variable.

Don't forget to mark this thread resolved (if it is).