By silurius on

My PHP development skills are still on the weak side, and I could use some input on a problem I am having with an application I'm attempting to migrate.

Here's the code in question:

$hostname = "[INFO REMOVED FOR THIS POST]";
$username = "[INFO REMOVED FOR THIS POST]";
$password = "[INFO REMOVED FOR THIS POST]";
$dbName = "[INFO REMOVED FOR THIS POST]";

$conn = MSSQL_CONNECT($hostname,$username,$password);

mssql_select_db($dbName, $conn);

Using a PHP page content type, when I attempt to preview the page using only this code, the page I get is mostly blank. I only see a bit of html from the same piece of content and nothing else, so I think this is because Drupal is being persuaded to try to utilize this database info for other things. Incidentally, I do hope to integrate the external data into my drupal database, but I'm not ready to do this yet.

My assumption is that I will need to customize this to make it clearer that I'm talking about an external database, but my searches are failing me today and I'm not sure how I would do this. Can I simply modify "$hostname" to be anything I want, provided I edit it consistently within the code? I would sincerely appreciate any feedback or help anyone might have.

⋅ Categories: Drupal 5.x

Comments

nevets’s picture

nevets commented

See http://drupal.org/node/18429 for how to use multiple databases with Drupal.

silurius’s picture

silurius commented

Thanks for the reference. Is a file edit absolutely necessary? If so, I assume that's considered best practice for a good reason.

Also, the last part of the article worries me because Drupal is using mysql and the php application is using mssql:

This only works with two databases of the same type. For example the following code will not work.

// ... header of the settings.php file

$databases = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);

// ...

Edit: Upon further reflection, I suspect the comment about databases of different types was only relevant to arrays?

nevets’s picture

nevets commented

My guess though under the hood it is changing the default database to use and that is confusing drupal.

silurius’s picture

silurius commented

I'm aware of efforts to make MSSQL fully supported (see this thread) but I'm still unclear on whether or not one can simply make occasional calls to MSSQL from a drupal site that happens to be running on MySQL.

roydean’s picture

roydean commented

I also want to display data from a MSSQL database within the Drupal site that is using MySQL. I do not want to and cannot modify the corporate DB to MySQL, but I would like to use Drupal for our Intranet and display data on Drupal nodes.

Any suggestions?

Found one myself (for anyone else's benefit):

<?php
$myServer = "servername";
$myUser = "username";
$myPass = "userpassword";
$myDB = "dbname";

// connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass) 
    or die("Couldn't connect to SQL Server on $myServer");

// select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
    or die("Couldn't open database $myDB");

//Generic statement for displaying test of connection success
echo "You are connected to the " . $myDB . " database on the " . $myServer . ".";

$msquery = "SELECT * FROM tablename
WHERE (fieldname = 'whatevervalue')";
$msresults= mssql_query($msquery);
echo "<table >";
while ($row = mssql_fetch_array($msresults)) {
        echo "<tr><td>" . $row['fieldname'] . "/" . $row['anotherfieldname'] . "-" . $row['anotherfieldname'] . "</td><td>" . $row['anotherfieldname'] . "</td><td>" . $row['anotherfieldname'] . "</td><td>" . $row['anotherfieldname'] .  "</td></tr>";
}
echo "</table>";
// close the connection
mssql_close($dbhandle);
?>

Now all that is left is to collect criteria from a form and format the output.

seanmclucas’s picture

seanmclucas commented

Yeah but I think this will be more difficult to theme with Drupal. Fortunately, both the DBs I needed to work with were both Mysql, but you will run into trouble when you try to switch Drupal back and forth between different DB engines because it changes the core functions.