Saturday, June 23, 2012

Cake PHP 2.3.1 with SQL Server 2005 or higher

Here are steps to connect CakePHP with SQL Server

1. Download latest stable stable version cakePHP from here

2. Copy the framework to web server document root folder(htdocs)

3. Download Microsoft Drivers for PHP for SQL Server from here
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=20098

In this link you can see two versions(2.0,3.0) of Microsoft drivers for PHP.  Suppose you use windows XP with PHP 5.3.0 or lesser version then choose 2.0 driver otherwise choose 3.0 driver.

Download the driver and install it. It generates driver dll files. There are two way to connect SQL server with PHP. A procedural interface (SQLSRV) and an object oriented interface (PDO). Both the SQLSRV and PDO APIs provide a comprehensive data access solution from PHP, and include support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling.

Note:
Microsoft Drivers for PHP 2.0 need Microsoft SQL Server 2008 Native Client
Microsoft Drivers for PHP 3.0 need Microsoft SQL Server 2012 Native Client
More information & downloads available http://msdn.microsoft.com/en-us/library/cc296170.aspx

4. To enable PDO SQL Server extension, copy php_pdo_sqlsrv_53_nts_vc9.dll into "ext" folder of PHP where other PHP extensions are available

5. Enable PDO SQL Server extension on php.ini


[pdo_sqlsrv]
extension=php_pdo_sqlsrv_53_nts_vc9.dll
pdo_sqlsrv.log_severity=-1

6. Restart the web server

7. Make sure pdo_sqlsrv extension enabled by phpinfo

<?php phpinfo(); ?>

8. Edit CakePHP database config file for SQL Server database connection

Go to app/Config/database.php, edit database settings


 public $default = array(
                'datasource' => 'Sqlserver',
                'persistent' => false,
                'host' => 'sqlserver',
                'login' => 'sa',
                'password' => 'password',
                'database' => 'demo',
                'prefix' => ''     
        );



Now i hope you can able to connect database & create models for SQL Server tables.

3 comments:

  1. Hi, thanks for sharing this, I am not able to connect my cake php code with sql server as Microsoft Drivers for PHP 3.0 need Microsoft SQL Server 2012 Native Client is not avail for download. Kindly help me to resolve the issue.

    ReplyDelete
  2. http://www.microsoft.com/de-de/download/details.aspx?id=29065

    ReplyDelete
  3. Hi, I am not able to connect my cake php 2.7.3 with sql server 2008, I am done all the steps but i get error like this -
    Error: A Database connection using "Sqlserver" was missing or unable to connect.
    The database server returned this error: SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver 11 for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver 11 for SQL Server for x86: http://go.microsoft.com/fwlink/?LinkId=163712

    Please help me ASAP.

    ReplyDelete