Link to home
Start Free TrialLog in
Avatar of Nico2011
Nico2011Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Connect to MSSQL DB using PHP

Hello,

I'm trying to connect to an MSSQL DB using PHP.  This is to run on a Windows 2008 R2 Server running ASP AND PHP, so perhaps the <!DOCTYPE needs to be changed?

Once I can get this working, I'll be coming back with more questions (to be sure!), as I'll need to read and write data.

My code is as follows (which lists the fields in a table), but this doesn't work:

<!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
// connect
$query = mssql_connect ( 'DB_IP_ADDRESS', 'User_Name', 'PW' ) or die ( 'Can not connect to server' );// select
mssql_select_db ( 'Table_Name', $query ) or die ( 'Can not select database' );
//query
// Construct table
echo '<h3>Table structure for \'Table_Name\'</h3>';
echo '<table border="1">';

// Table header
echo '<thead>';
echo '<tr>';
echo '<td>Field name</td>';
echo '<td>Data type</td>';
echo '<td>Max length</td>';
echo '</tr>';
echo '</thead>';

// Dump all fields
echo '<tbody>';

for ($i = 0; $i < mssql_num_fields($query); ++$i) {
    // Fetch the field information
    $field = mssql_fetch_field($query, $i);

    // Print the row
    echo '<tr>';
    echo '<td>' . $field->name . '</td>';
    echo '<td>' . strtoupper($field->type) . '</td>';
    echo '<td>' . $field->max_length . '</td>';
    echo '</tr>';
}

echo '</tbody>';
echo '</table>';

// Free the query result
mssql_free_result($query);
?> 
</body>
</html>

Open in new window


Thanks in advance for your help!
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Here is an HTML5 skeleton.  You may or may not need jQuery.

<?php // RAY_html5.php
error_reporting(E_ALL);

// CREATE VARIABLES FOR OUR HTML
$xyz = 'Hello World. ';


// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<title>HTML5 Page in UTF-8 Encoding</title>
</head>
<body>
<p>$xyz</p>
</body>
</html>
HTML5;

// RENDER THE WEB PAGE
echo $htm;

Open in new window

I'll see if I can come up with some test-worthy code for the DB part of things in a moment. ~Ray
Avatar of Mike Kristensen
Mike Kristensen

What is the error?
SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nico2011

ASKER

Thanks Ray - the output works, but only your file - not with the DB...

Mike - I don't get an error - just the text saying 'Table Structure For' and a table with headings but no data.
ASKER CERTIFIED SOLUTION
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the output works, but only your file - not with the DB...
Try the second script (the one that actually uses the data base) and show us what you get, thanks.
Wow, I hate that EE throws away my entire comment edit if someone posts after me...
Trying again as a second comment. Here's a bit of code using my examples:

// Connect
$conn = mssql_connect("localhost","sa","12345");
mssql_select_db("DatabaseA");

// Perform query
$rs = mssql_query("SELECT * FROM TableA");

// Check for errors
if($rs === false)
{
  echo "Error during query: " . mssql _get_last_message();
}
else
{
  // No error - loop through results
  $counter = 0;
  while($row = mssql_fetch_assoc($rs))
  {
     $counter++;
     echo "Record #" . $counter . " = <pre>" . print_r($row,true) . "</pre><br>\n";
  }
}

Open in new window

Again, none of this will work if you don't have the MSSQL extension enabled. You may want to create a PHP script that simply contains this:

<?php
phpinfo();
?>

Save it and then run it from your web browser. You should see a bunch of information about your PHP configuration and if you search for mssql, you should see if the extension is successfully working.
Thank you both - you have both given valuable replies!  I will try it out and post a new question if I run into trouble!
Sorry chaps - gr8gonzo - that's why I try to close questions quickly, but you are both working so fast and have both assisted me!  I was actually trying Ray's code when you posted your answer.

Thank you both once again - I'm getting errors, but probably down to my lack of knowledge with PHP (*definitely* rather than probably!).
Hi gr8gonzo - I've done what you said with the php file and I think the MSSQL extension is enabled - the file is at www.villasdirect.com/LG/gr8.php!

Ray & gr8gonzo,

Is there a way to contact developers who you feel you work well with?  Ray helped a great deal on a problem last week (which works beautifully now) and you also say in your profile that you do some sub-contracted work, and your help is also spot on.

I use oDesk when I have projects that I really need help with - it's a shame EE don't allow 'askers' to hire people who want to be hired - it would help both sides, and maybe they should be talking to make a deal of some sort!

What I'm currently working on is trying to convert a JSON-RPC feed (which Ray helped me convert to a text file) to populate a DB Table, which then needs to create an array of properties to go back to the same server feeding the initial list of properties, with the property ID's, get another JSON-RPC feed and populate our DB with the detailed information of the properties.

I'm happy to continue hammering away at this myself, but sometimes that's not the most efficient manner - especially as I don't 'speak' PHP...!

I don't want to be breaking any EE rules, just that you guys really know what you're doing and it's a shame to have to trial others who have not helped.