Need Help

Stay Secure

Get Connected

Need Technology?

---

Incoming Students

Students

Faculty/Staff

---

Office of the Vice President for IT and CIO

myIdentity

Other IT Resources

About University IT

University IT Home

University IT » Web Services » Help » MsSql » Data Types

Using PHP to Connect to Microsoft SQL Server

Determining the Datatype of a Column

It is possible to determine the datatype of a particular column with a simple MS SQL query. This is useful for finding incompatible datatypes before a PHP script is written. There are several ways to check for incompatible datatypes.

1. First, let's say we are interested in a column named col1 in a table named mytable. Then we can use the following SQL query to find its datatype.

   SELECT DATA_TYPE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE COLUMN_NAME = "col1"
   AND TABLE_NAME = "mytable"
         

   The table INFORMATION_SCHEMA.COLUMNS holds information about all of the columns in the database.

2. Now let's say we want to know the datatypes of all columns in a table named mytable. We can use the following query to do this.

   SELECT DATA_TYPE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = "mytable"
         

3. And finally, let's say we want to know all of the columns in a table named mytable that are of an incompatible datatype. We use the following SQL query to find all columns of the types bigint, decimal, float, ntext, numeric, and xml. As listed in the table of datatypes previously, these are the incompatible datatypes.

   SELECT COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE DATA_TYPE IN ("bigint","decimal","float","text","numeric","xml")
   AND TABLE_NAME = "mytable"
         

The following example demonstrates how to incorporate the final query into a PHP script that will output HTML.

<?php
$query = "SELECT COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE DATA_TYPE IN (
		'bigint',
		'decimal',
		'float',
		'text',
		'numeric',
		'xml')
	AND TABLE_NAME = 'mytable'";
$result = mssql_query($query);

echo "<pre>";
while ( $record = mssql_fetch_array($result) ) { print_r($record); }
echo "</pre>";
?>
      

Unicode Datatypes

Although the datatypes nchar, nvarchar, and ntext in MS SQL support unicode, the FreeTDS protocol used to bridge the gap between PHP and MS SQL Server does not. Therefore, communications between PHP and the database are limited to the ASCII character set.

Binary Data

SELECT queries can retrieve data from a binary field. However, attempts to display binary data will output nonsense characters. Fortunately, some PHP functions exist to convert binary data into other forms.

• The PHP function bin2hex() converts binary data to hexadecimal form.
• And the PHP function hexdec() converts a hexadecimal number to decimal form.

The bit Datatype

Columns of the datatype bit can be set to one of three values: 1 (True), 0 (False), or NULL. However, SELECT queries like the following example can yield some unpredictable results.

SELECT bit_field FROM tablename
      

• For a particular record in the table, if the bit column is set to 1, the value 1 is returned to PHP (as one would expect).
• If the bit column is set to 0, the value 0 is returned to PHP (again, as one would expect).
• However, an ambiguity arises when the the bit is set to NULL. In that case, the value 0 is returned to PHP.

Therefore, if the output in PHP from a bit column is zero, it is ambiguous whether the value is set to 0 or NULL.