mssql

Connecting to MSSQL

I have had to create a few PHP sites that connect to a MSSQL server. Not being down with setting up these servers, there are a couple of caveats you should be aware of if you are not accustomed to the server yourself.

TCP/IP access is turned off by default, so if you are trying to connect via IP address, you will need to switch this on. It is in the SQL Server Configuration Manager under the Server Network Configuration > Protocols for xxx

Also, if you are a MySQL guru, you will use user accounts to connect, and MSSQL can have the same, but they are switched off by default. If you connect to your MSSQL server instance using the Management Studio and right click on the server (the top item in the filetree) and choose properties, you will find a “Security” tab, where you will find an option for allowed authentication types.  Switch it to SQL Server and Windows Authentication mode.

This will save you a few hours!

MSSQL IF (multiple fields > one value)

In MySQL I use IF(value,then,else) quite often, but in a new project I am doing I need to do the same in MSSQL.

SELECT field1, field2, IF(field3 = '1', 'Started', 'Not Started') AS status FROM table WHERE id = x;

This won’t work in MSSQL, but this will:

SELECT field1, field2, status = (CASE WHEN field3 = '1' THEN 'Started' WHEN field3 <> '1' THEN 'Not Started' END) FROM table WHERE id = x;

Not worked out the case ELSE yet, but haven’t got time today, so just recording the above.

Compile php_imap and mssql for PHP on Mac

IMAP

To get imap support in PHP on your Mac, you need to get the php_imap module compiled for your version of PHP. I have attached mine to this to save you the effort, but this wiki article can remain for reference.

Firstly, you need to download the Client-c code. This is a library that allows various applications to communicate with IMAP servers. PHP being one of them.

ftp://ftp.cac.washington.edu/imap/imap-2007e.tar.gz

Save it into your downloads folder, and once finished, extract it. If you are using Terminal, you can do the above with the following commands: (NB, do all this in sudo)

cd ~/Downloads
curl -o imap.tar.gz ftp://ftp.cac.washington.edu/imap/imap-2007e.tar.gz
tar -zxvf imap.tar.gz

Then we need to compile it, and copy all the C files to the right places.

cd imap-2007e/
make osx EXTRACFLAGS="-arch i386 -arch x86_64 -g -Os -pipe -no-cpp-precomp"
mkdir /usr/local/include
cp c-client/*.h /usr/local/include/
mkdir /usr/local/lib
cp c-client/*.c /usr/local/lib/
cp c-client/c-client.a /usr/local/lib/libc-client.a

Now we need the version of PHP we need. To find out what version you are running, run this in a Terminal window:

php -v

That’ll tell you which version you need. Replace the curl address below with the relavant version from the php.net website.

cd ~/Downloads
curl -o php.tar.gz http://uk.php.net/distributions/php-x-x-x.tar.gz
tar -zxvf php.tar.gz

Once that is all extracted, we need to compile PHP:

cd php-x.x.x/
sudo ./configure
sudo make

Once that’s done we can go to the imap extension folder and compile that extension. There are more extensions in the file structure, but for now, we just need the imap one:

cd php-x.x.x/ext/imap
phpize
CFLAGS="-arch i386 -arch x86_64 -g -Os -pipe -no-cpp-precomp"
CCFLAGS="-arch i386 -arch x86_64 -g -Os -pipe"
CXXFLAGS="-arch i386 -arch x86_64 -g -Os -pipe"
LDFLAGS="-arch i386 -arch x86_64 -bind_at_load"
./configure --with-imap=/usr/local/imap-2007 --with-kerberos --with-imap-ssl=/usr/
MACOSX_DEPLOYMENT_TARGET=10.6

That should create a nice little imap.so file in the modules folder:

/Users/chris/Downloads/php-x.x.x/ext/imap/modules/imap.so

This file needs copying to the extensions folder on your PC. So you need to find the right folder. Mine was:

/usr/lib/php/extensions/no-debug-non-zts-20090626/

So, I copied the file there:

cp imap.so /usr/lib/php/extensions/no-debug-non-zts-20090626/

And then all we need to do is edit the php.ini file, to load the extension:

nano /etc/php.ini

Scroll down to the extensions bit, and add it in, mine would be like:

extension=/usr/lib/php/extensions/no-debug-non-zts-20090626/imap.so

Then reboot Apache

apachectl restart

That should be it.

Next, php_mssql (if required)

MSSQL

The process is really very similar to what we just did for imap, but we do need to install freetds which is a mssql thing

Create the directory /usr/local/freetds
Download the latest version of freetds (http://www.freetds.org/software.html)
Open a terminal window
Untar/zip the freetds file and cd into that directory in terminal

Run the following to compile the extension

 ./configure --prefix=/usr/local/freetds --sysconfdir=/usr/local/freetds/conf/freetds --disable-libiconv --disable-odbc
make
sudo make install

Find the PHP source code from earlier, and go to the correct folder:

cd /Users/chris/Downloads/php-x.x.x./ext/mssql/
sudo phpize
sudo ./configure --with-mssql=/usr/local/freetds
sudo make

Now its the same as the imap section. Find the mssql.so file in the ext/mssql/modules/mssql.so and copy it to the extensions folder.

cp mssql.so /usr/lib/php/extensions/no-debug-non-zts-20090626/

And then all we need to do is edit the php.ini file, to load the extension:

nano /etc/php.ini

Scroll down to the extensions bit, and add it in, mine would be like:

extension=/usr/lib/php/extensions/no-debug-non-zts-20090626/mssql.so

Then reboot Apache

apachectl restart

And that should work now. Create a page with a simple echo phpinfo() and that should confirm the fact.