SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Converting DB Functions to use MSSQL

    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:

    PHP Code:
    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:

    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
    Last edited by neilmerton; Apr 27, 2007 at 07:58.

  2. #2
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Austin
    Posts
    401
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    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;

    Merchant Equipment Store - Merchant Services, POS, Equipment, and supplies.
    Merchant Account Blog | Ecommerce Blog

  3. #3
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Austin
    Posts
    401
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Merchant Equipment Store - Merchant Services, POS, Equipment, and supplies.
    Merchant Account Blog | Ecommerce Blog

  4. #4
    That's Right. notepad_coder's Avatar
    Join Date
    Apr 2002
    Location
    Colorado
    Posts
    835
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are functions for MSSQL just like MySQL.

    PHP Code:
    # 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
    - the lid is off the maple syrup again!

  5. #5
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Austin
    Posts
    401
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Merchant Equipment Store - Merchant Services, POS, Equipment, and supplies.
    Merchant Account Blog | Ecommerce Blog

  7. #7
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP 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:

    Code:
    [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.

  9. #9
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Turns out I had a stray variable name in the script - all sorted and working 100&#37;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •