Use PHP_Compat to use more recent PHP functions in old versions of PHP

As stated in my previous post, we needed to stick to PHP 5.2 rather than upgrade to a more recent version. One of the many downsides of this other than for security reasons is that you miss out on a large number of more useful functions.

In our case we missed the “str_getcsv()” function when reading in a .CSV file line by line. A really quick way to get around this, with or without using PEAR, was to download PHP_Compat, which includes a huge number of compatible functions from later versions of PHP.

If you have PEAR installed you can simply run the following to enable the functions:

pear install PHP_Compat

But if you are like us, running a version of Linux without PEAR installed by default you can just download the library directly. Once downloaded, just place in your web application directory (or elsewhere depending on configuration) and use a require_once() command in each PHP script for each function that you need, e.g.:

require_once(“PHP_Compat-1.6.0a3/Compat/Function/str_getcsv.php”);

If you are on shared hosting or cannot get a more recent version of PHP, this library is a lifesaver.

Upgrade PHP from 5.1.6 to 5.2.17 on CentOS

The default install of PHP on our CentOS 5.5 box was 5.1.6, which is very out of date (we are currently using PHP 5.3 elsewhere while we figure out how to get around some very serious problems with 5.4). Unfortunately, we needed to upgrade to PHP 5.2 and no further as 5.3 meant upgrading MySQL and potentially breaking compatibility with our web application.

It used to be that you could add the CentOS testing repositories and just update PHP but as PHP 5.2 is depreciated this option is no longer available. The solution is to use the Atomic repositories which can be added to your CentOS install by typing:

wget -q -O – http://www.atomicorp.com/installers/atomic | sh

This will add a new repository file “/etc/yum.repos.d/atomic.repo” which means we can use their packages as well as those from CentOS. Now we need to make sure that we don’t upgrade our PHP beyond 5.2 so we add a single line to “/etc/yum.conf” under the [main] section:

exclude=php-*5.3*

The exclusion means we will include packages from all repositories other than anything that matches “php-*5.3*” so PHP 5.3 won’t be installed as part of an upgrade.

Now just upgrade PHP and restart Apache:

yum update php

service httpd restart

You can check which PHP version you have using:

php -v

Now obviously you want to use a more recent version of PHP than 5.2 but in the rare case where you have to, the previous commands make things very easy.

Easily connect and use PHP with SharePoint lists using cURL, RSS and NTLM authentication

Connecting to SharePoint from PHP is actually not that difficult if you have the cURL extension installed on your web server. In the case of my XAMMP windows development server I just made sure the following line in php.ini (c:xammpphpphp.ini in my case) was uncommented before restarting Apache:

extension=php_curl.dll

In Ubuntu/Linux you can usually just install the packages for cURL and after restarting Apache it will become available. Just type the following on the command line:

sudo apt-get install curl libcurl3 libcurl3-dev php5-curl

Then restart Apache

sudo /etc/init.d/apache2 restart

Now the following code comes from both Norbert Krupa’s comment on David’s IT Blog and a question about parsing HTML on StackOverflow. The important thing to note is that I needed to use cURL to authenticate my domain user when connecting to my secure SharePoint Services 3.0 test site. Apparently you can get away without using cURL on sites that don’t need authentication but the same cURL code listed below can be used with a blank username and password for the same effect.

The goal of this listing is to connect to SharePoint using a domain user (can also be a local user if SharePoint is set up that way) and retrieve the contents of a SharePoint list. The trick is to supply the RSS feed url, which allows PHP to parse the RSS feed and neatly list the contents of a SharePoint list. An advantage of using RSS feeds of SharePoint lists is that they are secured using the same method as the list itself and require no extra configuration on the SharePoint side of things. You can also set the RSS feed to only show a set number of items or days, which is useful for regularly updated lists.

// generic function to get the contents of an HTML block
function get_inner_html( $node ) {
    $innerHTML= '';
    $children = $node->childNodes;
    foreach ($children as $child) {
        $innerHTML .= $child->ownerDocument->saveXML( $child );
    }
    return $innerHTML;
}

// username and password to use
$usr = 'DOMAINUSERNAME';
$pwd = 'PASSWORD';
// URL to fetch, this is the address of the RSS feed (go into a list and click "Actions" -> "View RSS Feed" to get the url)
$url = "http://www.sharepointsite.com/_layouts/listfeed.aspx?List=%7BCED7CDDC-49C0-4C46-BDE6-CFC2BA993C84%7D";
//Initialize a cURL session
$curl = curl_init();
//Return the transfer as a string of the return value of curl_exec() instead of outputting it out directly
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
//Set URL to fetch
curl_setopt($curl, CURLOPT_URL, $url);
//Force HTTP version 1.1
curl_setopt($curl, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
//Use NTLM for HTTP authentication
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_NTLM);
//Username:password to use for the connection
curl_setopt($curl, CURLOPT_USERPWD, $usr . ':' . $pwd);
//Stop cURL from verifying the peer’s certification
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
//Execute cURL session
$result = curl_exec($curl);
//Close cURL session
curl_close($curl);

$xml = simplexml_load_string($result);

// display results on screen
foreach($xml->channel->item as $Item){
    echo "<br/>($Item->title)";
    $doc = new DOMDocument();
    $doc->loadHTML($Item->description);
    $ellies = $doc->getElementsByTagName('div');
    foreach ($ellies as $one_el) {
        if ($ih = get_inner_html($one_el))
        {
            echo ", $ih";
        }
    }
}

The SharePoint RSS feed is a little interesting as the “$Item->title” object is the main column in the list but the rest of the list is encapsulated in <div> within “$Item->description”, hence the requirement to parse the html.

For a SharePoint list with 3 columns the output will look something like:

(Item 1 Title) , Other Column A: xxxx, Other Column B: yyyy
(Item 2 Title) , Other Column A: zzzz, Other Column B: kkkk

Now the potential for this is great as it allows us to securely synchronise SharePoint lists with external databases, use SharePoint for authenticating PHP applications etc . We are going to be using this for automatically pulling users from a SharePoint list to populate a separate PHP application, whilst keeping user-identifiable data locked away on SharePoint.

Create Certificates and Use SSL with MySQL and the ADOdb Database Abstraction Library for PHP

This post is mainly for my own records and is the combination of 3 tutorials from KrWiki at waterlovinghead, Carl’s Whine Rack and Mad Irish. All 3 are almost 100% what I was looking for.

I needed to set up a secure database connection between our live web server and database server, both running Ubuntu Server. The 2 machines are completely separate and have their own IP address. The procedure for setting up an SSL connection between the two is similar to setting up a standard SSL certificate in Apache. First you create the certificates and sign them, then you configure the client (web server 192.168.0.2) and server (database server 192.168.0.1) to use them, finally you create a special MySQL user that uses SSL and connect using ADOdb. You don’t need to use ADOdb but it makes things a lot easier (see my previous posts).

It’s very important that your MySQL server has SSL enabled (most do by default). You can test this by connecting to your server and typing the SQL command:

SHOW VARIABLES LIKE '%ssl%'

Which should show “have_ssl” as either “DISABLED” or “YES”. If it says “NO” you will need to enable SSL (which might mean recompiling MySQL).

Creating the certificates

You need to create certificates for both the server (our database server) and the client (our web server). There are always a few ways of doing this described in various tutorials but this way is the easiest.

First create a directory somewhere that you will store your certificates in, e.g. “/var/ssl_certificates/” then go into the directory and type the following:

openssl genrsa 2048 > ca-key.pem

Which will generate a 2048 bit RSA private key for the Certificate Authority (CA). Now you need to create a Certificate Authority (CA) certificate, which will be used later to sign our server and client certificates. It is possible to use an external CA but in this case we are self signing, which makes things easier.

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

You will be asked a few details: country name, state or province name, locality name, organisation name, organisational unit name, common name and email address. Set the common name to the name of your server e.g. 192.168.0.1. The ca-cert.pem file created is our CA certificate.

Now you can create the server certificate request:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

This will ask for the same details as the CA certificate but will also ask you to put in a challenge password and optional company name. In our case, we are not going to be using the challenge password as we would have to enter it every time the SQL server started up.

Now sign the request using our CA certificate and key and generate the server certificate:

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now we need to create a client certificate request. We need to create one for each client, but in this case we only need to create one for the web server:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

You will be asked the same questions as for the server certificate request. The difference this time is that you need to put the client hostname in e.g. 192.168.0.2. Now sign this request and generate the client certificate:

openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So now we should have several files in our directory. These are:

  • ca-key.pem – The certificate authority (CA) key
  • ca-cert.pem – The certificate authority (CA) certificate
  • server-key.pem – The server key
  • server-req.pem – The server certificate request
  • server-cert.pem – The server certificate
  • client-key.pem – The client key
  • client-req.pem – The client certificate request
  • client-cert.pem – The client certificate

So now we have the required certificates for both the server and the client we can set up both.

Setting up the MySQL server and client to allow SSL

All you need to do to set up the MySQL server to accept SSL requests is edit the MySQL my.cnf configuration file (usually in either /etc/my.cnf or /etc/mysql/my.cnf). Add the following lines in the [mysqld] section to point MySQL to the required files (in our case in /var/ssl_certificates/):

[mysqld]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/server-cert.pem
ssl-key=/var/ssl_certificates/server-key.pem

Now restart the MySQL service to check it works by typing:

/etc/rc.d/init.d/mysqld restart

If everything is working fine it should just start up like normal.

Now to set up the client (our web server) we need to copy the 3 required client files ca-cert.pem, client-cert.pem & client-key.pem to a directory on the client (e.g. /var/ssl_certificates/ again). Now you edit the my.cnf on the client (usually in either /etc/my.cnf or /etc/mysql/my.cnf) and add the following lines in the [client] section, which you may need to add yourself:

[client]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/client-cert.pem
ssl-key=/var/ssl_certificates/client-key.pem

So now the client and server are set up to use the SSL certificates you created for all SSL connections between the two. Now we set up a user for the database who is forced to use SSL.

Setting up a user to use SSL only

It’s easiest to set up the user on the command line using the “mysql” command on the database server. So log in as your root user (or equivalent) on the database server and create a test database “test_ssl”:

create database test_ssl;

Now create a test user “test_user” with password “testpass” with the correct client hostname “192.168.0.2″ and force them to use SSL when logging in:

grant all privileges on test_ssl.* to 'test_user'@'192.168.0.2' identified by 'testpass' require ssl;
flush privileges;

Now we can create a test table “test” and insert some data so we can query it to test everything is working from PHP:

create table test (test_id int auto_increment primary key, test_data varchar(255));
insert into test set test_data='foo';

Now we go back to ADOdb and PHP to test this all works.

Connect to the database over SSL using ADOdb

Simply create a .php file on your web server with the following code:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("SELECT * from test");
print_r($arr);
?>

This should connect to our database using the test_ssl user and using SSL and print out the following on your web browser:

Array ( [0] => Array ( [0] => 1 [test_id] => 1 [1] => foo [test_data] => foo ) )

Now the great thing about this is that by using ADOdb we don’t need to worry about any kind of special connection string. The only thing that is different between an SSL connection and a standard connection is that SSL ues the “mysqli” command in the data source name rather than “mysql”, the rest of the code stays the same.

NOTE: It’s worth double checking that everything is working by executing "show status like 'ssl_cipher'" as a SQL query in the PHP above:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("show status like 'ssl_cipher'");
print_r($arr->fields);
?>

which gives:

Array ( [0] => Ssl_cipher [Variable_name] => Ssl_cipher [1] => DHE-RSA-AES256-SHA [Value] => DHE-RSA-AES256-SHA )

Combine JQuery, JSON and Zend Framework MVC actions to update page items dynamically using AJAX

In order to populate and dynamically update a table on one of our Zend framework based pages without refreshing the page we had to use a combination of JQuery, JSON and standard Zend views/forms. The JQuery is part of the view and is inserted in <script> blocks while a standard Zend form is used to populate the page with dropdown menus, text boxes etc. JSON is used to handle passing data between the client side JQuery and the server side Zend action.

The following is a simple example, which passes JSON stored by JQuery in a hidden field to a Zend action on the server to update a database. Where possible the code has been simplified (so no long winded database connection etc  etc). As getting Zend framework up and running can be a bit difficult from scratch, this is not covered here. The Zend Framework Quickstart is useful although personally I think it could be clearer for first time users. Although I use modules in Zend to split different sections of my web application into self contained areas, for this example I will keep it as simple as possible and not use modules. To keep things even more simple I am not going to describe any validation or POST handling of the Zend action, we are only concerned with JQuery/Zend integration.

The example action we are going to call is the LIST action in the VISIT controller, which will show a list of visits and allow us to add a new one using a simple one textbox interface. JQuery will be used to pull all the required table data from the server and handle the visual client side aspect of adding a new visit.

The controller VisitController.php will have a very simple LIST action that looks like the following:

public function listAction()
{
  // set the view title to "Visit List"
  $this->view->title = "Visit List";
  // create a new Form_ListVisit() form object and add it to the view
  $newVisitForm = new Form_ListVisit();
  $this->view->form = $listVisitForm;
}

All this action does is set the view title, generate a Form object from a Zend form Form_ListVisit() and set the view form to this object (thereby placing the form on the page). The action will not do anything without a corresponding view script and Zend form, which both need to be created as separate files in the correct location in your file structure (more than likely /views/scripts/visit/list.phtml and /forms/ListVisit.php). Again, use of views and Zend forms is standard Zend Framework practice and is beyond the scope of this simple example.

For the view, currently without any JQuery, it will also be very simple:

<?php
  echo "<h1>".$this->title."</h1>";
  echo $this->form;
  echo "<div class='statusmessage'></div>";
?>

All this does is place the title and the form on the page that is displayed to the user, both the title and the form are set in the controller (see above). In a moment the JQuery will be placed in a <script> tag above this <?php ?> section, although it is a good idea to refactor your code to keep the view as simple as possible by having the JQuery code in a separate .js file. The “statusmessage” div will be used by JQuery to place success/error message on screen when we update the database.

The ListVisit.php Zend form file is more complicated and in the real world can take a while to write, including all the objects, decorators, subforms, validation options etc. In this case we keep it very simple and just add a single hidden field that we will use to store a single JSON string and a textbox to handle user input:

class Form_ListVisit extends ZendX_JQuery_Form
{
  public function init()
  { 
    // add the hidden field used to store our JSON string 
    $hiddenjson = $this->createElement('hidden','hiddenjson');
    $this->addElement($hiddenjson);
    // add the textbox used to handle user input
    $inputfield = $this->createElement('text', 'inputfield', array('Label' => 'Enter new data here'));
    $this->addElement($inputfield);
  }
}

So on loading the NEW action on the VISIT controller we end up with a blank page with only a title that says “Visit List” and a single textbox. The hidden field placed on the page by the Zend form is there but is empty, there is no table on screen and no other links. In this example we are going to add everything else we need, such as the table of visits, link to add new visit and link to save to the database, using JQuery. The communication between the client side and the server is handled using JQuery’s .post method and another Zend action which is set to take in JSON and only return a JSON string.

JQuery is used to generate a simple single column table and add two links to the page. The first link “Add New Visit” will add whatever is in the textbox to our very simple single column table. The second link “Save to Database” will update the database with the values of our table by calling the SAVEDATA action on the VISIT controller. The SAVEDATA action takes in a JSON string, does something to the database and returns a JSON string to the client. The possibilities that result from passing JSON between client and server using this JQuery method are amazing and make it trivial to create AJAX interfaces with JQuery and Zend.

IMPORTANT: I had to include the well known json2.js script by Douglas Crockford in order to get my JSON encoding and decoding working correctly in all browsers. This script saved a lot of headaches and is only 17k (and can probably be minified).

In the view, in the <script></script> at the top we first need to add in the JQuery that will be loaded on document ready (standard JQuery practice):

$(document).ready(function(){
  // create the two links to "Add New Visit" and "Save to Database" and place before statusmessage div
  $( "div.statusmessage" ).before("<a href='#' id='addvisitbutton'>Add New Visit</a>&nbsp;&nbsp;<a href='#' id='savedatabutton'>Save to Database</a>");
  // create the empty simple one column table to store our visits and place it after the statusmessage div
  $('div.statusmessage').after("<table id='visittable'><tbody><tr><th>Visit Name</th></tr></tbody></table>");
  // handle "Add New Visit" button click
  $( "#addvisitbutton" ).click(function() {
    addvisit();
  });
  // handle "Save to Database" button click
  $( "#savedatabutton" ).click(function() {
    savedata();
  });
}

Note that the click handling functions that have been added to the 2 links call either addvisit() or savedata(). These 2 functions are described above and outside the JQuery $(document).ready() as follows:

function addvisit(){
  // get a JSON object from the value of the hidden data field (will be empty initally)
  var jparsed = JSON.parse($("#hiddenjson").val());
  // create a new row to add to the JSON data based on the contents of the text field
  var row = [$("#coughscore").val()];
  // add the row to the end of the table
  jparsed.push(row);
  // set the hidden value to a JSON string representing the contents of the JSON object
  $("#hiddenjson").val(JSON.stringify(jparsed));
  // remove all rows from the visittable table of class "datarow", all new data rows will be of this class
  $("#visittable tbody tr.datarow").remove();
  // iterate through the JSON object  and append the data rows to the visittable table
  for(var i=0;i<jparsed.length;i++)
  {
    rowstring += "<tr class='datarow'>";
    var obj = jparsed[i];
    for(var j=0;j<obj.length;j++)
    {
      rowstring += "<td>" + obj[j] + "</td>";
    }
    rowstring += "</tr>";
  }
  $("#visittable tbody").append(rowstring);
}

When a row is added to the table the hidden field is updated to contain a JSON string describing the whole table. It is this JSON string that is passed to the SAVEDATA action of the VISIT controller by the savedata() function’s .post(), where it is converted to an object by some simple PHP code and then stored in the database:

function savedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend SAVEDATA action on the VISIT controller
  $.post(host+"/savedata",{
    // send the value of the jsondata variable (the JSON string) in the POST as "jsondata"
    jsondata: jsondata
  },
  // handle the returning JSON data from the SAVEDATA action
  function(data)
  {
    // convert the returning JSON data to an object using .eval()
    obj = window.eval(data);
    // set the value of the statusmessage div to the 'notes' field in the JSON returned by the SAVEDATA action
    // (this is either "Succcess" or "Failure" in this example)
    $('.statusmessage').html(obj['notes']);
  },
    "json");
  }
}

The SAVEDATA action is a standard Zend action on the VISIT controller with the addition of a couple of important lines of code to avoid rendering a view:

public function savedataAction()
{
  // disable the Zend layout
  $this->_helper->layout->disableLayout();
  // suppress auto-rendering and displaying a view
  $this->_helper->viewRenderer->setNoRender();
  // retrieve the jsondata string from the POST variables
  $jsondata = $_POST['jsondata'];
  // create an object to store the success or failure message strings and return them to JQuery.
  $data['notes'] = "";
  // decode the JSON string and turn into an object
  if($dec = Zend_Json::decode($jsondata))
  {
    // do important things with the converted JSON object, e..g write to database etc..
    //
    // set the return message to Success
    $data['notes'] = "Success";
  }
  else
  {
    // conversion from JSON string to PHP object failed, so return Error
    $data['notes'] = "Error";
  }
  // encode the returning object into a JSON string
  $json = Zend_Json::encode($data);
  // echo the JSON string back to JQuery, where it is handled according to the definition in the .post() function
  echo $json;
}

So now you can pass JSON objects between the client and the server using JQuery and json2.js. In this example JQuery is used to populate a table client-side and then pass the data for storage in the database on the server.

Alternatively you can use a Zend action and a JavaScript function to pull data from the server and display it as a table. This is really useful for displaying tabular data and is just a modification of the method above. The JavaScript function retrievedata() to pull data from the server using a new RETRIEVEDATA action and JQuery’s .post() function and add it to our visittable table is as follows:

function retrievedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend RETRIEVEDATA action on the VISIT controller
// note that we don't actually post any data this time, we only retrieve data
  $.post(host+"/savedata",{
  },
  // handle the returning JSON data from the RETRIEVEDATA action
  function(data)
  {
    // clean up and convert the returned JSON to a string
    // (this step required as sometimes Zend JSON is not always "correct")
    var json = JSON.stringify(data);
    // convert JSON string to object
    var jparsed = JSON.parse(json);
    // create a blank string ready to store a single html row of the table
    var rowstring = "";
    // iterate through the JSON object adding the html for a row of the table each time
    for(var i=0;i<jparsed.length;i++)
    {
      rowstring += "<tr class='datarow'>";
      var obj = jparsed[i];
      // iterate through each column of the row, adding the html for a table cell each time
      for(var j=0;j<obj.length;j++)
      {
        rowstring += "<td>" + obj[j] + "</td>";
      }
      rowstring += "</tr>";
    }
    // append the newly formed rowstring to the table
    $("#visittable tbody").append(rowstring);
    // set the hidden field to the JSON string
    $("#hiddenjson").val(json);

  },
    "json");
  }
}

The RETRIEVEDATA action (simplified to remove database queries) looks like:

public function retrievedataAction()
{
  $this->_helper->layout->disableLayout();
  $this->_helper->viewRenderer->setNoRender();
  // get array of objects from database using ORM
  // alternatively, use standard SQL connection etc etc
  // e.g. $arr = SomeORMMethod("visits");
  $output = array();
  foreach($arr as $key => $value)
  {
    $row = array();
    $row[0] = $value->visitName;
    $row[1] = $value->visitDate;
    $output[] = $row;
  }
  $json = Zend_Json::encode($output);
  echo $json;
}

You may have noticed that all of this can be done without Zend at all. If you read the JQuery .post() documentation it becomes a bit clearer, especially if you end up not using JSON at all. I know there are other methods out there to do this, but since I’m heavily involved with Zend at the moment I thought it would make sense to try and explain this example, as I used it in my own code. Also note, there is no security at all on this example; you will need to manage sessions, use encrypted data etc to make this secure.

The basic framework of JQuery/JSON/Zend means you can easily create rich MVC apps with AJAX and the flexibility of JQuery means that you don’t even have to use Zend at all! You can apply the same basically identical approach with Microsoft MVC, CakePHP or even with your own frameworks, all you need to do is modify the actions.

Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP

In previous posts I’ve talked about connecting to Microsoft SQL Server from PHP using ODBC and using mssql_connect to connect directly. The most important thing I’ve realised is not to re-invent the adodb wheel, which has shown to be a really great cross-platform way of connecting to databases.

The best thing about adodb is that just by changing the connection string I can talk to MySQL and Microsoft SQL Server without changing any of my code. I’ve used this for performance and migration testing and it’s been perfect, with none of the strange hangs I noticed with a standard ODBC connection. Currently I am using Ubuntu Server 10.10 to host all the PHP, which has overall been a pretty good experience, apart from these strange hangs.

If you are in Ubuntu Server you may need to install some ODBC packages in order to get adodb to work. Some of these packages are not totally necessary but I would recommend them for testing ODBC connections etc:

sudo apt-get install php5-odbc unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Now you need to create a SQL user in either your MySQL or SQL Server database. Make sure you map the SQL Server user as a database owner on the DB you want to connect to and give them the same default schema. The reason for this is I couldn’t get the connection in adodb to work with the actual DB specified (unlike in MySQL).

Download adodb5 and place the directory in the root of your webserver and add the following to the top of your PHP file:

include(‘adodb5/adodb.inc.php’);

Now open up your PHP file and insert the following for SQL Server with IP “the.ser.ver.ip”:

$dbcstring = “mssql://username:password@the.ser.ver.ip”;

Or the following for MySQL:

$dbcstring = “mysql://username:password@the.ser.ver.ip/databasename?persist”;

Create a connection to the database:

$DB = NewADOConnection($dbcstring);

Now you can use the built in commands in adodb to easily pull data from the database and display it on screen. It doesn’t matter which database you are connecting to, the commands are now the same. This is the main advantage of adodb in my opinion, along with the ease of use:

$arr = $DB->GetAll(“SELECT column FROM table”);
for($i=0;$i<count($arr);$i++)
{
echo $arr[$i][0]  . “<br/>”;
}

It’s worth reading the guide on adodb for the commands you can use, such as $DB->GetRow(), $DB->GetOne() and $DB->Execute(). The great thing is that adodb makes everything so simple you can treat your data as arrays or you can go further and make use of some of the more advanced functionality. Either way, by using adodb you massively cut down on code rewrites when you migrate to a new database.

Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

Previously I showed how to connect to Microsoft SQL Server in PHP using mssql_connect(). This works but is not as neat as using ODBC.

Unfortunately when writing this guide I got stuck at the last hurdle when trying to use ODBC and adodb in PHP. I could connect to the database using:

include(‘adodb5/adodb.inc.php’);
$connection=& ADONewConnection(‘odbc_mssql’);
$connection->Connect(‘datasourcename’,’username’,’password’);

Or using odbc_connect:

$connection= odbc_connect(‘datasourcename’, ‘username’, ‘password’);

But selecting data using “$connection->GetAll()” or “odbc_exec()” just resulted in a page that never loaded. Fortunately the ODBC part worked, there was just something I haven’t figured out yet with ODBC in PHP.

This guide will show you how to set up ODBC in Ubuntu using FreeTDS to talk to Microsoft SQL Server. I am trying to work out why the server connects fine but won’t execute queries in PHP. When I find out why I will update this post. To create this guide I went off a post on Ubuntu forums with some additions. There are a lot of steps, but at least some of the programs you use along the way allow you to check things are working as expected before you come to test in PHP.

First you need to install php5-odbc in Ubuntu:

sudo apt-get install php5-odbc

Now you need to add freetds to create a connection and talk to your server, plus a few other handy programs:

sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Edit the example server details or create a new one in /etc/freetds/freetds.conf to point to your server (in my case I called it “sqlserver”):

[sqlserver]
host = ip.add.ress
port = 1433
tds version = 7.0

You can check the connection is set up using sqsh:

sqsh -S sqlserver -U username -P password

Alternatively, you can connect directly to the ip.add.ress:

sqsh -S ip.add.ress -U username -P password

Now in sqsh you can select from a table in your SQL Server database and display it on screen by entering your query and “go” on the next line:

1> select * from databasename.dbo.table
2> go

Hopefully you should see your data on screen. The next step is to configure ODBC so check where the config files are located using:

odbcinst -j

Which should say “DRIVERS: /etc/odbcinst.ini” and “SYSTEM DATA SOURCES: /etc/odbc.ini” which are the files we will be editing.

Now we set up the FreeTDS driver so it can be used by ODBC so open “/etc/odbcinst.ini” (was a blank file in my case) and add:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Open “/etc/odbc.ini” and create the system data source “datasourcename” that we will use everywhere (including in PHP):

[datasourcename]
Driver = FreeTDS
Description  = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = databasename

It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

You can test the connection works using isql:

isql -v datasourcename username password

Now you can use this data source “datasourcename” in PHP, which is the goal! To test the connection works I tried odbc_connect():

$connection = odbc_connect(‘datasourcename’, ‘username’, ‘password’);
if (!$connection) die(“Connection failed”);
else echo “works”;

Which does work. Now I just need to work out why queries such as the following cause PHP to hang:

$query = odbc_exec($connection, ‘SELECT column FROM table’) or die (odbc_errormsg());

Connecting to Microsoft SQL Server from PHP in Ubuntu using mssql_connect()

As part of ongoing testing to find the best way of storing large amounts of data we are considering Microsoft SQL Server, which needs to be accessible from our PHP5 application. The site is hosted on Ubuntu Server 10.10, which makes things a little more interesting. We currently use adodb5 to talk to a MySQL server, which works fine, apart from MySQL being far too slow. It is possible to create an ODBC connection in Ubuntu to talk to SQL Server but for testing the speed of our queries I just used mssql_connect(). In the near future I will move the database connection entirely to ODBC/adodb so we don’t have to rewrite any of our existing code.

Ubuntu doesn’t come with the packages needed for mssql_connect() by default so you need to install them:

sudo apt-get install php5-sybase

Then restart the apache server to apply the changes:

sudo /etc/init.d/apache2 restart

Now you can use mssql_connect and its associated functions in your PHP to connect to your Microsoft SQL Server, as in this example:

ini_set(‘display_errors’, 1);
$server = ‘my.server.ip:1433DATABASEINSTANCE’;
$link = mssql_connect($server, ‘username’, ‘password’);

if (!$link) {
die(‘<br/><br/>Something went wrong while connecting to MSSQL’);
}
else {
$selected = mssql_select_db(“databasename”, $link)
or die(“Couldn’t open database databasename”);
echo “connected to databasename<br/>”;

$result = mssql_query(“select name from table”);

while($row = mssql_fetch_array($result))
echo $row["name"] . “<br/>”;
}

Quick PHP code to test ldap connection

I needed to test that the ldap module in PHP was working (“apt-get install php5-ldap” in linux) so I just placed this in a .php to check it was logging in correctly

<?php
$ldap = ldap_connect(“domain.com”);
$username=”username@domain.com”;
$password=”password”;

if($bind = ldap_bind($ldap, $username,$password ))
echo “logged in”;
else
echo “fail”;
echo “<br/>done”;
?>