Converting DB Functions to use MSSQL

[neilmerton]

Hi all,

I've been asked to get a website up-and-running that was moved over to new hosting - the issue is the new host doesn't have MySQL installed - only MS SQL, so this will require rewriting a few functions to work with MS SQL database.

There are a few functions that have already been written and I'm hoping that getting these to work will save a bit of time in the long run.

Note: I'm not a very experienced in PHP - I normally code in CF.

Here's the code in question:

function db_connect()
{
    $result = @mysql_pconnect('servername', 'username', 'password');
    if (!$result)
    {
        return false;
    }
    if (!@mysql_select_db('database'))
    {
        return false;
    }
    return $result;
}

function db_result_to_array($result)
{
    $res_array = array();
    for ($count = 0; $row = @mysql_fetch_array($result); $count++)
    {
        $res_array[$count] = $row;
    }
    return $res_array;
} 

The only way I've been able to connect to the MS SQL database so far is using the following bit of PHP code:

$myServer = "servername";
$myUser = "username";
$myPass = "password";
$myDB = "database";

$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");

$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr);

$query = "SELECT TOP 10 id, name FROM mailing_list";

$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
for ($i=0; $i < $num_columns; $i++)
{
    $fld[$i] = $rs->Fields($i);
}

echo "<table>";

while (!$rs->EOF)
{
    echo "<tr>";
    for ($i=0; $i < $num_columns; $i++)
    {
        echo "<td>" . $fld[$i]->value . "</td>";
    }
    echo "</tr>";
    $rs->MoveNext();
}

echo "</table>";

$rs->Close();
$conn->Close();
$rs = null;
$conn = null; 

Could someone please advise how to get the two functions above to work with the connection code I've managed to get working with MSSQL?

Thanks in advance

[jestep]

Without seeing the COM class, I cant really figure out how to make your script work.

Here's a class that works very well for php5 and mssql. I am using it with several sites, and I really like it.

http://www.phpclasses.org/browse/package/2843.html

Also, these should work from your existing script.

function db_connect()
{
    $result = @mssql_pconnect("host,port",user,password);
    if (!$result)
    {
        return false;
    }
    if (!@mssql_select_db('database',$result))
    {
        return false;
    }
    return $result;
}

function db_result_to_array($result)
{
    $res_array = array();
    for ($count = 0; $row = @mssql_fetch_array($result); $count++)
    {
        $res_array[$count] = $row;
    }
    return $res_array;
} 

[jestep]

Also, there are some small differences in querying mssql compared to mysql. If you have a query that fails it may be because mssql doesn't like the format.

Mysql syntax like 'Limit' doesn't work, and mssql is more strict with certain queries to certain data types.

[notepad_coder]

There are functions for MSSQL just like MySQL.

# instead of...
mysql_pconnect("localhost", "username", "password");
# use this...
mssql_pconnect("localhost", "username", "password");

# instead of...
mysql_query("SELECT * FROM table");
# use this...
mssql_query("SELECT * FROM table");

# instead of...
$row = mysql_fetch_array($result);
# use this...
$row = mssql_fetch_array($result); 

I don't know if this is what you were going for, but I used to use them.

http://www.php.net/mssql

[neilmerton]

Hi guys, thanks for the replies.

I take it that in order to use 'mssql_...' I will need to ensure that the php_mssql extension is enabled.

[jestep]

Should just have to uncomment it in php.ini assuming that you are using php5. If you are using php4, you will need to recompile php with mssql supported.

[neilmerton]

Hi Jestep - I think I've managed to get things working using 'mssql_...' so it's looking good!

Many thanks, and if I come across any more issues I'll be sure to post about them here

[neilmerton]

Right,

I thought all was fine, but sadly not.

I'm getting the following error:
"Warning: mssql_query(): supplied argument is not a valid MS SQL-Link resource in <path_to_file>\db_fns.php on line 133"

This references the following bit of code:

function get_all_work()
{
    $conn = db_connect();
    $sql = "SELECT * FROM work ORDER BY date DESC";
    $result = mssql_query($sql, $conn);
    if (!$result)
    {
        return false;
    }
    else
    {
        $return = db_result_to_array($result);
        return $return;
    }
} 

The current table structure for 'work' is:

[id] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[title] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[region] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date] datetime NULL,
[short_des] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[story] text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[img] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Any thoughts are appreciated.

[neilmerton]

Turns out I had a stray variable name in the script - all sorted and working 100&#37;