Meloria • Ever Better
Search Tools Main Menu

Using PHP to Connect to Microsoft SQL Server

Introduction | Getting Started | Limitations | More on Data Types | Troubleshooting

Limitations

If you are familiar with using PHP to interact with MySQL databases, doing the same with MS SQL is not much different. However, there are two important limitations.

  1. INSERT queries in MS SQL must be formed like the following example.

    INSERT INTO tablename (col1, col2, col3) VALUES(12, "Hello, World!", "test")

    By contrast, INSERT queries in MySQL can also be formed as follows. However, the following syntax will NOT work in MS SQL.

    INSERT INTO tablename SET
    	col1 = 12,
    	col2 = "Hello, World!",
    	col3 = "test"
  2. Grabbing certain data types from a table with a SELECT query will cause the PHP script to fail. For example, columns of the datatype decimal cause problems on our servers, so the following MS SQL query will fail when run from PHP.

    SELECT decimal_field FROM tablename

    Variables of one datatype can be converted to another. This is called casting, and the MS SQL function CAST will do this for us. The datatype real is compatible, so we can CAST the decimal column as real, and the script will not fail. The following is an example of a successful query.

    SELECT CAST(decimal_field AS real) FROM tablename

    Incompatible datatypes can be cast as compatible datatypes before they are sent to PHP to avoid script failures.

    • Incompatible numerical datatypes may be cast as real variables. The following example shows how to do this.

      SELECT CAST(fieldname AS real) FROM tablename
    • Incompatible non-numerical datatypes may be cast into the text datatype, as shown in the following example.

      SELECT CAST(fieldname AS text) FROM tablename
    • The CAST function can be used on individual columns in a list as the following example demonstrates.

      SELECT col1, CAST(col2 AS text), CAST(col3 AS real) FROM tablename

    Note that it is theoretically possible to cast a variable as any other datatype—not just real or text. However, problems arise when incompatible datatypes are returned to PHP.

    The following table lists all of the datatypes in MS SQL 2000 and 2005, along with any issues to keep in mind when working with them.

    DatatypeNotes
    bigintIncompatible datatype. CAST as real.
    binaryCompatible datatype. Must be converted to another form for viewing. See the section on Binary Data for information on converting to hexadecimal or decimal form.
    bitCompatible datatype. See the section on The bit datatype for more detail.
    charCompatible datatype. Only the first 255 characters are returned.
    datetimeCompatible datatype.
    decimalIncompatible datatype. CAST as real.
    floatIncompatible datatype. CAST as real.
    imageCompatible datatype. Seems to behave exactly like the datatype text in MS SQL Server 2000.
    intCompatible datatype.
    moneyYes
    ncharCompatible datatype. See the section on Unicode Datatypes for more information.
    ntextIncompatible datatype. CAST as text.
    numericIncompatible datatype. CAST as real.
    nvarcharCompatible datatype. See the section on Unicode Datatypes for more information.
    realCompatible datatype.
    smalldatetimeCompatible datatype.
    smallintCompatible datatype.
    smallmoneyCompatible datatype.
    textCompatible datatype.
    timestampCompatible datatype. See the section on Binary Data for information on displaying a timestamp. NOTE: timestamp columns cannot be modified. UPDATE queries will return an error.
    tinyintCompatible datatype.
    uniqueidentifierCompatible datatype. NOTE: the output of a uniqueidentifier column from a MS SQL SELECT query can be misleading. The string PHP returns does not match what is inserted into the database.
    varbinaryCompatible datatype.
    varcharCompatible datatype. Only the first 255 characters are returned.
    xmlIncompatible datatype. (The datatype xml is present in MS SQL Server 2005, but not in MS SQL Server 2000.)