CodeIgniter 3: connecting to MS SQL from Linux

ms-sql1-300x120Connecting to Windows/Microsoft SQL from Linux/CodeIgniter remains challenging. As PHP progresses, various old methods of connecting to MS SQL are being deprecated in favour of (e.g.) PDO. Unfortunately, reliable MS SQL server PDO drivers are hard to come by under Linux.

As I’ve written previously, the most successful method I’ve found of connecting from CodeIgniter to MS SQL is using a combination of unixODBC and FreeTDS. So here’s an updated guide for CodeIgniter 3 on Ubuntu 14/PHP 5 or Ubuntu 16/PHP 7.

On the server where your web application runs, install the following packages: unixodbc freetds freetds-dev tdsodbc php5-odbc. For Ubuntu 14:

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

For Ubuntu 16:

apt-get install unixodbc freetds-common freetds-dev tdsodbc php7.0-odbc

Restart Apache:

service apache2 restart

Add the details of your MS SQL server to the FreeTDS config file (at /etc/freetds/freetds.conf), e.g.:

[my-server]
host = my-server.domain.local
port = 1433
tds version = 7.4

Note: that the TDS version shown above is for SQL Server 2012 (version 11). For more information about the TDS protocol version numbers (which don’t follow the Microsoft SQL version numbers), read the official documentation.

Add to /etc/odbcinst.ini (you may need to check the precise location of these .so files):

[TDS]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

Add details of your MS SQL server to /etc/odbc.ini:

[my-server]
Driver = TDS
Description = My Server
ServerName = my-server
Database = MyDatabase

The ServerName above here corresponds to the name of your server in the FreeTDS configuration file. In the CodeIgniter database configuration file, add something like this:

$db['mssql'] = array(
'dsn' => '',
'hostname' => 'dsn=my-server;uid=myusername;pwd=mypassword',
'username' => '',
'password' => '',
'database' => 'MyDatabase',
'database' => '',
'dbdriver' => 'odbc',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);

Then your models should begin something like this:

class WidgetModel extends CI_Model
{
public function __construct()
{
parent::__construct();
// Load MS SQL connection
$this -> widgetdb = $this->load->database('mssql', true);
}

You can get some strange results using this driver. Mainly you’ll have to resort to explicit SQL queries. And certain things won’t work as expected – e.g. using “AS” to rename columns only works on calculated columns.

How-to: Laravel 4 tutorial; part 1 – installation

[easyreview title=”Complexity rating” icon=”geek” cat1title=”Level of experience required, to follow this how-to.” cat1detail=”A good level of familiarity with web hosting will come in handy here, especially if your hosting environment is different from mine. It will also help if you’re comfortable at the command line.” cat1rating=”4″ overall=”false”]

Laravel Tutorials

Introduction

If, like me, you spend much time coding for the web – for pleasure or profit – sooner or later you’re going to find that you benefit from using a development framework. A framework is a collection of scripts that help you create an application much more quickly. Frameworks typically include a lot of the “nuts and bolts” components – scripts that assist with database connections for example, plus components that impose some structure on your programming.

For a long time, my framework of choice was CodeIgniter. CodeIgniter has stagnated of late and concerns have arisen over licensing. Partly as a consequence, many PHP developers like me have searched for an alternative. In the search, I came across a framework that many programmers are turning to: Laravel. My early experiences with Laravel have been extremely positive and I have found many things I prefer about it. In this series of tutorials, I’ll show you how to get up and running with Laravel and begin creating an application. The application will involve some web-scraping, so you may wish to stay tuned for that reason alone.

Before we dive in though, one word of caution: Laravel is a young open source project. Like many such projects, its documentation is less complete than you might wish, particularly when compared to CodeIgniter. In fact CodeIgniter’s great documentation was one of the reasons why I initially chose it as a development framework. Documentation is a core commitment of the Laravel team, but at the time of writing, with the recent release of Laravel 4, I’m finding the documentation is not quite up to scratch. Possibly you’ve found that too, which is why you’ve made it here to this tutorial. In fact one of the worst parts of the documentation at the time of writing is the installation procedure! With that caveat in place, let’s move on – it’s still well worthwhile.

Prerequisites

All my web coding is done within a Linux environment, usually CentOS or Ubuntu Server. For the easiest experience following these tutorials, you may wish to create a similar environment. (I’ve written about that elsewhere.) Alternatively, you should be able to follow the tutorials with some tweaking – but you’re on your own there. At the very least, I recommend you have in place:

  • Apache web server
  • Shell access to the server (preferably SSH)
  • Root access to install Composer globally (not essential)
  • Git must be installed in your environment.

Installing Composer

With the latest release (4) Laravel has taken a leap forward in several areas. One such area is the management of third party libraries and packages. Laravel previously made use of an external project called “Composer“, to install dependent packages. With Laravel 4, you now use Composer to install Laravel itself. To install Composer, from a root login shell, do the following:

cd /usr/local/bin
curl -sS https://getcomposer.org/installer | php
mv composer.phar composer

Provided /usr/local/bin is in your $PATH environment variable, you will now be able to call Composer with “composer [options] command [arguments]“.

Installing Laravel



The beauty of Composer is the simplicity it brings to library/application installation. There is an overwhelming range of tutorials on how to install Laravel with Composer. Please bear in mind that many of these were written while Laravel 4 was in beta or even alpha. Now it has been released, the installation is quite straightforward. Having prepared a new environment for a web site (a virtual host or whatever), navigate to the directory above the default web root directory. Then install using Composer. Eg:

cd /home/geek/domains/test.geekanddummy.com/
composer create-project laravel/laravel

You should see a fair bit of output indicating that Composer is creating a directory “laravel” and pulling in all the dependencies for a basic installation. The laravel directory contains a folder entitled “public“, intended to be your web root. Your easiest way to complete the configuration is to point your web site at that directory. For example, using Virtualmin, you would go to Server Configuration –> Website Options and change “Website documents sub-directory” from “public_html” to “laravel/public”.

Having done that, when I browse to my test web site, I see:

Laravel landing page

Installing other frameworks

Now would be a good time put to put Twitter’s Bootstrap and jQuery in place, if you’re planning to use them. Naturally, we’ll use Composer for this. You might use other frameworks in your web applications – check out Packagist to see if anyone has made a Composer package available.

Composer demands a tutorial all of its own, but I’ll keep it simple here. I’m going to make my new Laravel application depend on the latest compatible branches of Bootstrap and jQuery. This will potentially allow us to upgrade these two frameworks with a simple Composer command at a later date.

In the root of your Laravel application you’ll find the main Composer configuration file, composer.json. You don’t need to get your hands dirty editing the file, just from a shell in that root directory, issue the following commands:

composer require "components/jquery":"*"
composer require "twitter/bootstrap":"*"

This updates the composer.json file to include these dependencies and goes ahead and downloads them. It can take a while – be patient.

You’ll end up with jQuery files under ./components/jquery and Bootstrap files under ./vendor/twitter/bootstrap. These are locations not visible to your web server (the root is at ./public, you’ll recall). This is a particular problem in the case of Bootstrap. For now, here’s a quick-and-dirty way of accessing these files. I’m on the lookout for a more elegant solution, but this will get you up and running rapidly. Navigate to the “public” folder in a login shell and issue the following commands:

mkdir -p assets/css
mkdir assets/img
mkdir assets/js
ln -s ../../../vendor/twitter/bootstrap/img/glyphicons-halflings.png ./assets/img
ln -s ../../../vendor/twitter/bootstrap/docs/assets/css/bootstrap.css ./assets/css
ln -s ../../../vendor/twitter/bootstrap/docs/assets/css/bootstrap-responsive.css ./assets/css
ln -s ../../../components/jquery/jquery.min.js ./assets/js

And so on, for whichever bits you’ll use. This will only work if your web server allows following symbolic links. The .htaccess directive “Options +SymLinksIfOwnerMatch” may help here, but that’s outside the scope of this tutorial.

Configure your development environment

I use NetBeans for development. If you don’t already have a preferred IDE (integrated development environment), I recommend you check it out. Another favourite is Eclipse. You could use an ordinary text editor, but then you’d be missing out on a lot of things that can make your coding more comfortable and efficient.

Having installed Laravel and the other frameworks on my web server, next I use NetBeans to pull the code across to my development environment. In the NetBeans “New Project” wizard, select the option “PHP Application from Remote Server”. In the remote configuration, ensure that you choose as your “upload directory”, the laravel. From there, you’ll want to download the app and public directories.

Conclusion

That’s it for today’s tutorial. Next time, we’ll look at orientating ourself within the framework (“what goes where?”).

From CodeIgniter to Laravel | part 4: managing databases

UPDATE: I have re-written this article for the new Laravel 4. You’ll find the updated article over at Geek & Dummy.

Contents


AVZ DatabaseFor almost all my previous web design, I’ve used phpMyAdmin to administer the databases. I speak SQL, so that has never been a big deal. But Laravel comes with some excellent tools for administering your databases more intelligently and (most importantly!) with less effort. Migrations offer version control for your application’s database. For each version change, you create a “migration” which provides details on the changes to make and how to roll back those changes. Once you’ve got the hang of it, I reckon you’ll barely touch phpMyAdmin again.

Configuration

So let’s assume that I’m creating a new website about ducks. When I created the virtual host, Virtualmin also created my “ducks” database. I’m going to create a MySQL user with the same name, with full permission to access the new database. Here’s how I do that from a root SSH login:

echo "GRANT ALL ON ducks.* TO 'ducks'@localhost IDENTIFIED BY 'newpassword';" | mysql -p
Enter password:[root MySQL password]

This creates a new MySQL user, “ducks” and gives it all privileges associated to the database in question. Next we need to tell Laravel about these credentials. The important lines in the file application/config/database.php are:

<?php
return array(

//...

	'default' => 'mysql',

	'connections' => array(

//...

		'mysql' => array(
			'driver'   => 'mysql',
			'host'     => '127.0.0.1',
			'database' => 'ducks',
			'username' => 'ducks',
			'password' => 'newpassword',
			'charset'  => 'utf8',
			'prefix'   => '',
		),

//...

	),

//...

);

Initialise Migrations

The migration environment must be initialised for this application. We do this using Laravel’s command line interface, Artisan. From an SSH login:

php artisan migrate:install
Migration table created successfully.

This creates a new table, laravel_migrations, which will be used to track changes to your database schema (i.e. structure), going forwards.

My ducks application will have a single table to start with, called “ducks” [Note: it is significant that we’re using a plural word here; I recommend you follow suit]. This table does not yet exist; we will create it using a migration. To kick this off, use the following Artisan command:

php artisan migrate:make create_ducks_table
Great! New migration created!

This will create a new file named something like “2013_04_15_085356_create_ducks_table.php”. If, like me, you’re developing remotely, you’ll need to pull this new file into your development environment. In NetBeans, for example, right-click the migrations folder, click “download” and follow the wizard.

You can deduce from the naming of the file that migrations are effectively time-stamped. This is where the life of your applications database begins. The migrations file will look a bit like this:

<?php

class Create_Ducks_Table {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		//
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}

}&#91;/php&#93;

As you can probably guess, in the "up" function, you enter the code necessary to create the new table (to move "up" a migration) and in the "down" function, you do the reverse (to move "down" or to roll back a migration).

<h1>Create first table</h1>

Your first migration will probably be to create a table (unless you have already created or imported tables via some other method).  Naturally, Laravel has a class for this purpose, the <a href="http://laravel.com/docs/database/schema" target="_blank">Schema class</a>.  Here's how you can use it, in your newly-created migrations php file:

[php]<?php

class Create_Ducks_Table {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('ducks', function($table) {
				$table->increments('id');              // auto-incrementing primary key
				$table->string('name', 255);           // varchar field; length 255 characters
				$table->date('birthdate')->nullable(); // can be empty
				$table->boolean('can_fly')->default(TRUE);
				$table->integer('children')->unsigned();
				$table->text('biography');
				$table->timestamps(); // special created_at and updated_at timestamp fields
		});	
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::drop('ducks');
	}

}

To run the migration (i.e. to create the table), do the following at your SSH login:

php artisan migrate
Migrated: application/2013_04_15_085356_create_ducks_table

That creates the table, as described. And if you need to roll back:

php artisan migrate:rollback
Rolled back: application/2013_04_15_085356_create_ducks_table

This removes the table.

By examining the Schema class documentation, you’ll see how you can use future migrations to add or remove fields, create indexes, etc. In my next tutorial, I’ll have a look at using databases in your application.

AVZ Database image copyright © adesigna, licensed under Creative Commons. Used with permission.

From CodeIgniter to Laravel | part 3: installing external libraries

UPDATE: I have re-written this article for the new Laravel 4. You’ll find the updated article over at Geek & Dummy.

Contents


LibraryAs a fan of CodeIgniter, I was very pleased when the Sparks project came on the scene, offering a relatively easy way to integrate third-party libraries/classes into your project. Laravel has a similar and arguably more feature-rich analog in Bundles. With a bit of luck, the third-party library you require has already been converted to a Laravel bundle, making installation a snip.

Let’s say, for example, we’re going to build a web-scraping application. The first two libraries I’d consider adding to the project would be the Requests HTTP library and the PHP Simple HTML DOM Parser.

From an SSH login, at the root of your project, issue the following command:

php artisan bundle:install requests phpsimplehtmldom

You should be greeted with the following results:

Fetching [requests]...done! Bundle installed.
Fetching [phpsimplehtmldom]...done! Bundle installed.

The file application/bundles.php will probably contain code along the following lines:

<?php

/*
 &#91;...various comments...&#93;
*/

return array(

	'docs' => array('handles' => 'docs'),

);

Register the new libraries like this:

return array(

	'docs' => array('handles' => 'docs'),
	'requests' => array('auto' => TRUE),
	'phpsimplehtmldom' => array('auto' => TRUE),

);

And use like this:

		$HDM = IoC::resolve('HtmlDomParser'); // Give us a hook into the library; Requests doesn't need this

		// Request the HTML page
		//$headers = array('Accept' => 'application/html');
		$headers = array();
		//$options = array('auth' => array('user', 'pass'));
		$options = array();
		$request = Requests::get('http://some.domain/some/page', $headers, $options);
		if($request->status_code != 200) {
			// Handle error retrieving page
		}

		$dom = $HDM::str_get_html($request->body);

		// Options
		$options = $dom->find('#somediv option');
		foreach($options as $option) {
			echo $option->value.' | '.$option->innertext."<br />";
		}

There’s a lot more to this IoC thing than meets the eye. To be frank, it’s above my head. I’m also not convinced I fully understand registering bundles. But, like CodeIgniter, learning is a process of immersion in the framework. I’m pretty sure than in a couple of years I’ll laugh at the code above. So all I ask is please be gentle in the comments. 😉

Library image copyright © Janne Moren, licensed under Creative Commons. Used with permission.

From CodeIgniter to Laravel | part 2: orientation

UPDATE: I have re-written this article for the new Laravel 4. You’ll find the updated article over at Geek & Dummy.

Contents


Signpost at North Point, Barbados, Feb.1998I’ve used CodeIgniter for many years, but I have always, I confess, proceeded knowing just enough to get by. So forgive me if my approach seems a little clunky. I have never, for example, used CodeIgniter’s routes. I like my web application files nicely categorised into Model, View, Controller, Library and, if absolutely necessary, Helper.

Controllers

So for now, I want to carry on using Controllers, if that’s okay with you. Controllers are stored under application/controllers. Sound familiar?

Here’s a sample controller:

<?php

// application/controllers/news.php
class News_Controller extends Base_Controller {

	public function action_index() {
		echo "News index page.";
	}

	public function action_item($item) {
		echo "News item $item.";
	}

}
?>

In CodeIgniter, that’s all you would have needed to do, due to automatic routing. In Laravel, you need also to add the following to application/routes.php:

Route::controller('news');

To view these pages, you just visit yourdomain/news (/index is implied) and yourdomain/news/item/x (where x will probably refer to a specific news item, possibly by data id).

Note the naming of the functions – action_item, etc. The part after the underscore represents a “method” or page of your web site. Laravel’s routing magic makes sure you get the correct function. If you’re creating a RESTful API, you can use additional function names beginning get_, post_, etc. Check the Laravel documentation for more.

Views

Views are pretty straightforward and similar to CodeIgniter. Place them in application/views. Extending the example above, our controller could now look like this:

<?php

// application/controllers/new.php
class News_Controller extends Base_Controller {

	public function action_index() {
		echo "News index page.";
	}

	public function action_item($id) {
		$data = array('id' => $id);
		return View::make('results', $data);
	}

}

?>

Note that data can also be passed through to a view like this:

	public function action_item($id) {
		return View::make('item', $data)
		    ->with('id' => $id);
	}

And then your view (application/views/item.php) could be like this:

<h1>News flash</h1>
<p>This is news item <?php echo $id; ?>.</p>

Obviously your real views will be more syntactically complete.

Models

Models are created under application/models. Unlike CodeIgniter, Laravel comes with its own object relational mapper. In case you’ve not encountered the concept before, an ORM gives you a convenient way of dealing with database tables as objects, rather than merely thinking in terms of SQL queries. CodeIgniter has plenty of ORMs, by the way, it just doesn’t ship with one as standard.

Laravel’s built-in ORM is called “Eloquent”. If you choose to use it (there are others available), when creating a model, you extend the Eloquent class. Eloquent makes some assumptions:

  • Each table contains a primary key called id.
  • Each Eloquent model is named in the singular, while the corresponding table is named in the plural. E.g. table name “newsItems”; Eloquent model name “newsItem”.

You can override this behaviour if you like, it just makes things a bit more convenient in many cases.

Example model application/models/newsItem.php:

<?php
class NewsItem extends Eloquent {

}

(You can omit the closing ?> tag.)

Because the Eloquent class already contains a lot of methods, you do not necessarily need to do more than this. In your controllers, you could for example now do this:

$items = NewsItem::all();

foreach ($items as $item) {
  // Do stuff here
}

This is barely scratching the surface. Head on over to the official Laravel documentation for much more on all this.

Signposts image copyright © Andrea_44, licensed under Creative Commons. Used with permission.

From CodeIgniter to Laravel | part 1: installation

UPDATE: I have re-written this article for the new Laravel 4. You’ll find the updated article over at Geek & Dummy.

Contents


Laravel logoIn my final “how-to” guide on self-hosted web design, I gave a special mention to a relatively new PHP framework, Laravel. I’m very familiar with CodeIgniter, but due to some of its limitations (including concerns over licensing, going forward), many developers have been moving to Laravel. Laravel has been on my “must try it out” list for some time. In this article, I’ll take you through initial installation and some of the issues I faced.

Prerequisites

Since this “how-to” follows my DIY web hosting series, I’m going to assume we’re starting with a similar setup: a virtual web site powered by Apache and PHP on Linux. Other environments work too. See the Laravel documentation for more details.

Installation

The Laravel setup guide is a little unclear about the correct location for its files. Here’s what I’ve found to work. This guide is for Laravel 3.2, but I suspect it will also hold true for later versions in due course. (At the time of writing, 4.0 is still in beta.)

If you’ve created a virtual web site with Virtualmin, you’ll have a new public_html directory at, for example, /home/fred.bloggs/domains/my.funky.site/public_html. Change to the directory above public_html (e.g. /home/fred.bloggs/domains/my.funky.site and proceed as follows, from an SSH login. Make sure you perform these steps as the web site owner (e.g. su fred.bloggs):

wget http://laravel.com/download -O laravel.zip
unzip laravel
rm laravel.zip
mv laravel-laravel-*/* .
mv laravel-laravel-*/.[gt]* .
rmdir laravel-laravel-*
mv public/* public_html/
mv public/.h* public_html/
rmdir public
chmod -R ug+w storage/views

If you read the Laravel installation instructions linearly, you’ll see that you need to make a change to application/config/application.php. It contains a line like:

'key' => 'YourSecretKeyGoesHere!',

The recommendation is that you insert 32+ random characters in place of YourSecretKeyGoesHere!. There’s another way of doing this, if (and only if) the key field is blank. Laravel comes with a CLI (command line interface) called Artisan. If you issue the command php artisan key:generate, a random key will be generated for you automatically and inserted into the file.

To remove “index.php” from your URLs, edit that same file (application/config/application.php). Find the line 'index' => 'index.php', and change it to 'index' => '',. Laravel comes with a .htaccess file in the public directory, which contains the necessary Apache magic to make this all work.

Having done the above, fire up your web browser and point it at your new site. You should arrive at a default introductory Laravel page:

laravel_default

Other frameworks

Now would be a good time put to put Twitter’s Bootstrap and jQuery in place, if you’re planning to use them. Check the download URLs are current before you do this:


wget http://twitter.github.com/bootstrap/assets/bootstrap.zip
unzip bootstrap
rm bootstrap.zip
mv bootstrap/css/* public_html/css/
mv bootstrap/js/* public_html/js/
mv bootstrap/img/* public_html/img/
rm -rf bootstrap
cd public_html/js
wget http://code.jquery.com/jquery-1.9.1.min.js

Configure your development environment

I use NetBeans for development. If you don’t already have a preferred IDE (integrated development environment), I recommend you check it out. Another favourite is Eclipse. You could use an ordinary text editor, but then you’d be missing out on a lot of things that can make your coding more comfortable and efficient.

Having installed Laravel and the other frameworks on my web server, next I use NetBeans to pull the code across to my development environment. Before you do this, still in your SSH shell, you may wish to delete some of the typical directories that Virtualmin creates but which you don’t really need:

rmdir cgi-bin/ homes/

In the NetBeans “New Project” wizard, select the option “PHP Application from Remote Server”. In the remote configuration, ensure that you choose as your “upload directory”, the directory above public_html, since that contains the private (non-web-accessible) Laravel directories, which we will also be playing with.

Once the code has copied across, you’ll want to exclude a couple of Virtualmin-generated directories from further synchronisation. In the project’s properties, go to “Ignored folders”. Add there /logs and /public_html/stats.

Where next

I’m slowly writing additional tutorials, while I take time to learn the framework. See the contents list above for other posts in this series. I also recommend you check out Dayle Rees’ tutorials. Happy coding!

Easily host your own web sites | part 5: Web design (geek/hacker)

Contents


Rambling introduction. Sorry.

beardy guysIn the last post in this series, I looked at some options for creating a website with relative ease. Perhaps the word “easily” should not be present in the title of this, the last episode.

If you’re a budding geek/hacker, sooner or later running WordPress blogs is going to get old. You could spend a lifetime learning how to bend WordPress to your will (which would be like dancing on shifting sand – it’s constantly changing underneath you and you’ll die from exhaustion) or you could set your mind to doing the thing from scratch. Or near enough from scratch.

I first got into programming (at the age of 11 or so) when the “hacker” community demographic genuinely consisted of bearded recluses. Social skills were an inconvenience. If you couldn’t say it with ones and zeroes, it wasn’t worth saying. Later on, when I took Computer Science at university (along with Law), little had changed. Less than three percent of the people in my year for CompSci were women. Less than three percent had discovered personal hygiene products. It wasn’t the same three percent, by the way.

I say this by way of apology. My path to web development was long and winding. I had experience with many different programming languages before my eyes were opened to the wonders of web-centric coding. This gave me quite an advantage. Diving into this material can be intimidating, if you’ve little or no experience of code. Let me just reassure you: all you need to do is assimilate a little bit of knowledge at a time. Eventually it will all start to hang together. With the tools I’ll show you, you’ll be hammering out awesome code before you can say Xyzzy.

Disclaimer: proceeding down this path may not turn you into a geek. Or cause you to grow a beard. Please try to contain your disappointment, if so.

The building blocks

Many (but by no means all) web sites are built using a combination of:

  • HTML: the language which describes the components of a web page. It has much more to do with layout/structure than with how things actually look. HTML is probably the easiest bit to learn. In your browser, you can right-click on most web pages and select “view source”. What you’ll be looking at is largely HTML code. Start learning HTML at W3Schools. Whenever I need a quick reference for HTML, I go here.
  • CSS: Cascading Style Sheets describe how a web page should look. Colour, positioning, borders, etc. Reference here. Tutorials here. Learn how to do fancy things with CSS at CSS Play and goggle at others’ CSS prowess in the CSS Zen Garden.
  • JavaScript: There seems to be an increasing trend to use “CamelCase” when writing “JavaScript”, these days. When I started learning, it was simply “javascript” – not to be confused with Java. JavaScript is a true programming language, which runs in your browser. It is loaded along with the HTML and CSS and actions are performed on the basis of the JavaScript code instructions. Reference here. Tutorials here.
  • PHP: A hugely popular server-side (so-called because it runs at the web server end, not in the browser) programming language. PHP is by no means the only – nor necessarily the best – web programming language out there. Ruby, Python, Java and ASP.NET all have a strong presence in modern web sites. PHP is the one with which I and many other far superior web developers are familiar. It’s idiosyncratic and inconsistent at times, but I have found it to be easy to learn and for the most part well documented. The official PHP site is an excellent reference tool. If you learn PHP, you’ll be using this site’s function search box regularly. The W3Schools tutorials don’t cut the grade for me here. Start learning PHP at the home of PHP.
  • MySQL: one of the most popular database management systems on the planet. If you need to store a lot of data (or even a little), you’ll benefit from a database engine powering your web site. Learn about using MySQL within PHP here. The official reference is here.

Gasp. That’s a lot to get hold of, isn’t it? Don’t worry – you’ll see that a lot of this learning dovetails together. For example, once you’ve learnt some JavaScript, picking up PHP will be that bit easier, because they have much in common.

Frameworks

You can get by, just knowing the building blocks. But if you want to create efficient, reusable code, without continually reinventing the wheel, you’d do well to make use of some frameworks. Each language has many frameworks and you could spend a lifetime trying them all out. I will steer you in the direction of a small few, but by all means try out as many as you have time for. Each is different from the other, which can be bewildering, but it also means you’re likely to find frameworks which fit or even enhance your style of working.

  • Twitter’s Bootstrap: built by the mighty Twitter, Bootstrap gives you consistent CSS styles, which (amongst other things) help to ensure your web sites look very similar in different browsers. Sooner or later you’ll be stung by cross-browser differences. Making use of a CSS framework such as Bootstrap will reduce the impact of this pain. Bootstrap is more than just a CSS framework, it also has JavaScript plugins. There’s much to explore here and unless you’re already a CSS/design guru, it will make your web sites look better.
  • jQuery: a phenomenal JavaScript library – it enables you to do phenomenal things on the browser side, with very little coding, which would otherwise take you hours of work. jQuery is not the only such library, but it is one of the best. It is further enhanced visually by the excellent jQuery UI.
  • CodeIgniter: I have built many web sites with this PHP development platform. It was my first introduction to the MVC (Model, View, Controller) paradigm, where you split out your database work (Models) from your presentation/pages (Views) and your application/business logic (Controllers). CodeIgniter has some flaws, including the suggestion (a view shared by many) that it would benefit from a ground-up rewrite. But it is well-built, fast, solid and will help you to concentrate on the unique features of your web site, without having to reinvent the wheel all the time. Your resulting code will be easier to understand and maintain.

Special mention

There’s a rapidly up-and-coming PHP framework that deserves some attention at this point, Laravel. I have not personally had chance to experiment with it yet, but I intend to use it for my next web application. I understand it addresses many of the shortcomings of other PHP frameworks and apparently CodeIgniter developers are flocking to it in droves.

Graphics

As I’m sure you can tell, graphic design is not my strong suit. I am amongst other things a web developer, not a web designer. It’s rare to find someone who’s good at both. Nevertheless, even if you’re not graphically inclined, there are still a lot of things you can do to give yourself a “leg up”. Here are my top graphics tips:

  1. Don’t be afraid to copy. There are very few genuine innovators out there. Most fashion is a rehash of other fashion. It’s fine not to be original. See what works, figure out why it works, and put your own spin on it.
  2. Get a good graphics package. Don’t be put off by its name; download GIMP. It’s like Adobe Photoshop at the great price of free. If you’re not short of cash, I can also recommend Adobe Fireworks, which is an excellent but expensive web-focussed design package.
  3. Follow the GIMP tutorials. Really. Do it.
  4. Use an online colour scheme generator.
  5. Soak up some design inspiration from Designpiration or Web Creme.

That’s me done. Now go forth and conquer the interwebs.

Photo of bearded gents copyright © Igal Koshevoy, licensed under Creative Commons. Used with permission.

Rackspace API for CodeIgniter

Logo_lockup_version-2 SPOTRackspace is a great email hosting company, providing, amongst other things, a handy API for creating bespoke email solutions. The exercise of integrating that API into your application is of course left to the end user. I’ve spent some time working on a Rackspace API library for the PHP programming framework, CodeIgniter. This is not functionally complete – I have only implemented the interfaces that I needed – but it should provide a useful springboard for your own projects.

Configuration

In /system/application/config/RackspaceAPI.php:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

$config['user_key']   = 'your user key';
$config['secret_key']     = 'your secret key';
$config['user_agent']     = 'name of your app';
$config['api_version']    = 'v0'; // amend if necessary
$config['rackspace_host'] = 'api.emailsrvr.com'; // amend if necessary

/* End of file RackspaceAPI.php */
/* Location: ./system/application/config/RackspaceAPI.php */

Library

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Uses curl and pecl_http
 */
class Rackspace_API {
    
  /**
   * Store recent http_message
   * @var object
   */
  protected $_http_message;
  
  /**
    * CI object
    * @var object
    */
  protected $_ci;

  /**
   * Rackspace config items
   */
  protected $_user_key;
  protected $_secret_key;
  protected $_user_agent;
  protected $_api_version;
  protected $_rackspace_host;
  
  function __construct() {
    $this->_ci =& get_instance();
    $this->_ci->config->load('RackspaceAPI', TRUE);
    $this->_user_key = $this->_ci->config->item('user_key', 'RackspaceAPI');
    $this->_secret_key = $this->_ci->config->item('secret_key', 'RackspaceAPI');
    $this->_user_agent = $this->_ci->config->item('user_agent', 'RackspaceAPI');
    $this->_api_version = $this->_ci->config->item('api_version', 'RackspaceAPI');
    $this->_rackspace_host = $this->_ci->config->item('rackspace_host', 'RackspaceAPI');
  }


  /**
   * Get info about a domain
   * @param string $domain
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function getDomainInfo($domain) {
    return $this->genericGet('/customers/me/domains/'.$domain);
  }

  
  /**
   * Get all domain names
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | array (domains)
   */
  public function getDomains() {
    $obj = $this->genericGet('/customers/me/domains');
    if(!$obj->error){
      // Reformat into an array of domains
      foreach($obj->result->domains as $domain) {
        $domains[]=$domain->name;
      }
      $obj->result = $domains;
    }
    return $obj;
  }


  /**
   * Get info about a mailbox ($domain@$id)
   * @param string $domain
   * @param string $id
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function getMailboxInfo($domain, $id) {
    return $this->genericGet('/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id);
  }
   
 
  /**
   * Used by Get functions above - generalised use case
   * @param string $url - see the API; constructed by the calling function
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  private function genericGet($url) {
    $this->get(
        $url,
        'application/json');
    if($this->_http_message->getResponseCode() == 200) {
      // Call worked.  JSON is missing enclosing brackets, apparently needed by json_decode
      $json = '['.$this->_http_message->getBody().']';
      if(is_string($json)) {
        $obj = json_decode($json);
        $result->error = false;
        $result->result = $obj[0];
      } else {
        // JSON failure
        $result->error = true;
        $result->result = 'Failed to parse JSON';
      }
    } else {
      // API call failed
      $result->error = true;
      $result->result = $this->_http_message->getHeader("x-error-message");
    }
    return $result;
  }
  

  /**
   * Create a mailbox ($domain@$id)
   * @param string $domain
   * @param string $id
   * @param string $first: First name
   * @param string $last: Last name
   * @param string $name: Display as
   * @param string $office: Name of office/profit centre
   * @param string $locno: Office/profit centre number
   * @param string $password
   * @param string $fwd: comma-separated forwarding address(es) - max 4 off domain
   * @param string $save: save forwarded email - 'true' or 'false'
   * saveForwardedEmail
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function addMailbox($domain, $id, $first, $last, $name, $office,
          $locno, $password, $fwd, $save='true') {
    $fields = array(
        'password' => $password, 
        'emailForwardingAddresses' => $fwd,
        'firstName' => $first,
        'lastName' => $last,
        'displayName' => $name,
        'saveForwardedEmail' => $save,
        'organization' => $office,
        'organizationUnit' => $locno);
    return $this->genericPost( '/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id, $fields);
  }


  /**
   * Used by Post functions above - generalised use case
   * Note: Rackspace API suggests use POST to add, PUT to edit
   * @param string $url - see the API; constructed by the calling function
   * @param array $fields - data to be POSTed
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  private function genericPost($url, $fields) {
    $this->post(
        $url,
        $fields,
        'application/json');
    if($this->_http_message->getResponseCode() == 200) {
      $result->error = false;
      $result->result = $this->_http_message->getBody();
    } else {
      // API call failed
      $result->error = true;
      $result->result = $this->_http_message->getHeader("x-error-message");
    }
    return $result;
  }


  /**
   * Edit user's forwarding
   * @param string $domain
   * @param string $id
   * @param string $fwd: comma-separated forwarding address(es) - max 4 off domain
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function changeForwarding($domain, $id, $fwd) {
    $fields = array(
        'emailForwardingAddresses' => $fwd
        );
    return $this->genericPut( '/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id, $fields);
  }
  
    
  /**
   * Edit user's location
   * @param string $domain
   * @param string $id
   * @param string $office: Name of office/profit centre
   * @param string $locno: Office/profit centre number
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function changeLocation($domain, $id, $office, $locno) {
    $fields = array(
        'organization' => $office,
        'organizationUnit' => $locno);
    return $this->genericPut( '/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id, $fields);
  }
  
    
  /**
   * Edit user's name
   * @param string $domain
   * @param string $id
   * @param string $first: First name
   * @param string $last: Last name
   * @param string $name: Display as
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function changeName($domain, $id, $first, $last, $name) {
    $fields = array(
        'firstName' => $first,
        'lastName' => $last,
        'displayName' => $name);
    return $this->genericPut( '/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id, $fields);
  }
  
    
  /**
   * Edit user's password
   * @param string $domain
   * @param string $id
   * @param string $password
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function changePassword($domain, $id, $password) {
    $fields = array(
        'password' => $password);
    return $this->genericPut( '/customers/me/domains/'.$domain.'/rs/mailboxes/'.$id, $fields);
  }
  
    
  /**
   * Used by Put functions above - generalised use case
   * Note: Rackspace API suggests use PUT to edit, POST to add
   * @param string $url - see the API; constructed by the calling function
   * @param array $fields - data to be PUT
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  private function genericPut($url, $fields) {
    $this->put(
        $url,
        $fields);
    if($this->_http_message->getResponseCode() == 200) {
      $result->error = false;
      $result->result = $this->_http_message->getBody();
    } else {
      // API call failed
      $result->error = true;
      $result->result = $this->_http_message->getHeader("x-error-message");
    }
    return $result;
  }


  /**
   * Delete a mailbox
   * @param string $domain
   * @param string $id
   * @return stdClass Object ( 'error'  => bool,
   *                           'result' => string (error message) | stdClass Object
   */
  public function deleteMailbox($domain, $id) {
    return $this->genericDelete("/customers/me/domains/$domain/rs/mailboxes/$id");
  }
  
  
  /**
   * Used by Get functions above - generalised use case
   * @param string $url - see the API; constructed by the calling function
   * @return stdClass Object ( 'error'  => bool,
   *                           ['result' => string (error message)]
   */
  private function genericDelete($url) {
    $this->delete($url);
    if($this->_http_message->getResponseCode() == 200) {
      // Call worked.
      $result->error = false;
    } else {
      if($this->_http_message->getResponseCode() == 500) {
        // Internal server error
        $result->error = true;
        $result->result = 'An internal server error occurred deleting  object.  Url: '.$url;
      } else {
        // API call failed
        $result->error = true;
        $result->result = $this->_http_message->getHeader("x-error-message");
        
      }
    }
    return $result;
  }
  

  
  // The remainder of this file is mostly lifted from Rackspace's examples: http://api-wiki.apps.rackspace.com/api-wiki/index.php/PHP_Examples_(Rest_API)
  private function get($url_string, $format) {
      $headers = array("Accept: $format");
      $curl_session = self::construct_session($url_string, $headers);
      $this->_http_message = self::send_request($curl_session);
  }

  private function post($url_string, $fields, $format) {
      $headers = array("Accept: $format");
      $curl_session = self::construct_session($url_string, $headers);
      curl_setopt($curl_session, CURLOPT_POST, true);
      curl_setopt($curl_session, CURLOPT_POSTFIELDS, $fields);
      $this->_http_message = self::send_request($curl_session);
  }

  private function put($url_string, $fields) {
      $curl_session = self::construct_session($url_string, array());
      curl_setopt($curl_session, CURLOPT_CUSTOMREQUEST, 'PUT');
      curl_setopt($curl_session, CURLOPT_POSTFIELDS, $fields);
      $this->_http_message = self::send_request($curl_session);
  }
  
  private function delete($url_string) {
      $curl_session = self::construct_session($url_string, array());
      curl_setopt($curl_session, CURLOPT_CUSTOMREQUEST, 'DELETE');
      $this->_http_message = self::send_request($curl_session);
  }

  private function send_request($curl_session) {
      $response = curl_exec($curl_session);
      curl_close($curl_session);
      /* Reponse string may contain two HTTP sessions, if there was an initial
         "HTTP/1.1 100 Continue" response.  So strip that first response out.  Eg:
                  HTTP/1.1 100 Continue
                  Via: 1.1 [proxy]

                  HTTP/1.1 400 Bad Request
                  Via: 1.1 [proxy]
                  Connection: Keep-Alive
                  Proxy-Connection: Keep-Alive      
                  ...     
       * 
       */
      $response = preg_replace('|HTTP/1.1 100.*HTTP/1.1|isU', 'HTTP/1.1', $response);
      return new HttpMessage($response);
  }

  private function construct_session($url_string, $existing_headers) {
      $headers = array_merge(
              self::authorization_headers(), $existing_headers);
      $url = self::construct_uri($url_string);
      $curl_session = curl_init($url);
      curl_setopt($curl_session, CURLOPT_HEADER, true);
      curl_setopt($curl_session, CURLOPT_HTTPHEADER, $headers);
      curl_setopt($curl_session, CURLOPT_RETURNTRANSFER, true);
      return $curl_session;
  }

  private function authorization_headers() {
      $time_stamp = date('YmdHis');
      $data_to_sign = $this->_user_key . $this->_user_agent .
          $time_stamp. $this->_secret_key;
      $signature = base64_encode(sha1($data_to_sign, true));
      $headers = array();
      $headers[] = "User-Agent: " . $this->_user_agent;
      $headers[] = 'X-Api-Signature: ' .
          $this->_user_key . ":$time_stamp:$signature";
      return $headers;
  }

  private function construct_uri($url_string) {
      $url = 'http://' .  $this->_rackspace_host . '/' . $this->_api_version . $url_string;
      return $url;
  }
}

?>

Example

Example usage:

function testRackspace() {
    $this->load->library('Rackspace_API');
    $client = new Rackspace_API();
    $obj = $client->getMailboxInfo('somedomain.com', 'test.user');
    if($obj->error) {
      echo 'Error: '.$obj->result;
    } else {
      var_dump($obj);
    }
  }

Image copyright © Rackspace Ltd. All rights acknowledged.

Connecting to Windows/MSSQL 2008 from Linux/CodeIgniter/PHP

Update: I’ve written a new article, covering CodeIgniter 3 and Ubuntu 14/16. Read it here.

Microsoft SQL Connecting to Microsoft SQL Express 2008 from Linux/PHP is a lot trickier than I expected. These notes are really for my own benefit so I can reproduce the setup, but maybe they’ll help you too. One of the problems is that many existing PHP drivers for MS SQL have difficulty talking to SQL 2008. Here’s a workaround using FreeTDS and ODBC.

My web application is built using CodeIgniter, the PHP application framework. It resides on an Ubuntu Server box, running Apache. Prerequisites on that Ubuntu Server (which I installed using Aptitude, but your favourite package manager will do):

  • unixODBC
  • FreeTDS
  • FreeTDS development package/(header files and libraries)

To my freetds.conf file (in /etc/freetds on my server) I added a section that looks something like this:

[my-server]
host = my.server.local
port = 1433
tds version = 9.0

Note: TDS version 9.0 should work with SQL 2008.

In /etc/odbcinst.ini, add the following ODBC driver (32-bit):

[TDS]
Driver = /usr/lib/odbc/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib/odbc/libtdsS.so

or 64-bit:

[TDS]
Driver = /usr/lib64/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib64/libtdsS.so

(You may need to check the precise location of the driver and setup files.)

And to /etc/odbc.ini, I inserted a DSN similar to the following:

[my-server]
Driver = TDS
Description = My Server
ServerName = my-server
Database = my-db

Generally within CodeIgniter, I am connecting to MySQL databases and that’s how my default connection is configured. I therefore added a second configuration to my database.php configuration file, like this:

$db['my_server']['hostname'] = "dsn=my-server;uid=myusername;pwd=mypassword";
$db['my_server']['username'] = '';
$db['my_server']['password'] = '';
$db['my_server']['database'] = '';
$db['my_server']['dbdriver'] = 'odbc';
$db['my_server']['dbprefix'] = '';
$db['my_server']['pconnect'] = TRUE;
$db['my_server']['db_debug'] = TRUE;
$db['my_server']['cache_on'] = FALSE;
$db['my_server']['cachedir'] = '';
$db['my_server']['char_set'] = 'utf8';
$db['my_server']['dbcollat'] = 'utf8_general_ci';

Now the ODBC driver within CodeIgniter can produce queries that MS SQL doesn’t like. We can fix this with a hack. You really REALLY shouldn’t do it this way (!) but to get things working and as described >here<, I edited the CodeIgniter core file system/database/drivers/odbc_driver.php. The function _from_tables() has a line reading:

return '('.implode(', ', $tables).')';

I changed it to this:

return implode(', ', $tables);

(In other words, we’re removing the spurious parentheses.)

I created a database method m_my_server.php like this:

/**
 * NOTE: We're using a feature-incomplete driver here.  Don't attempt to use
 * CodeIgniter's ActiveRecord Class or num_rows().  Use bare queries instead.
 */
class M_my_server extends Model {

  var $my_server;

  function M_my_server() {
      parent::Model();
      $this->my_server = $this->load->database('my_server', TRUE);
  }

  function get() {
    $query = $this->my_server->query('SELECT TOP(100) * FROM dbo.tblUserSummary');
    $result = $query->result_array();  // note ->num_rows() doesn't work with this driver
    if(count($result) > 0) {
      return $result;
    } else {
      return false;
    }

  }
}

/* End of file m_my_server.php */
/* Location: ./application/models/m_my_server.php */

At the SQL Server end, I set up a new standard SQL user (myusername/mypassword) rather than attempting to get Windows authentication to work (I bet it wouldn’t).

My SQL instance wasn’t listening to TCP/IP connections by default. I fired up SQL Server Configuration Manager and browsed to SQL Server Network Configuration –> Protocols for [my db instance]. Then you have to right-click TCP/IP and click Enable.

With all that in place, the following controller produces successful results:

  function SQLtest() {
    $this->load->model('m_my_server');
    $result = $this->m_my_server->get();
    if($result) {
      print_r($result);
    } else {
      echo 'nada';
    }
    exit;
  }

It’s not ideal; for one thing, bare SQL queries involve learning Microsoft’s particular dialect of SQL (whereas I’m used to MySQL). The tables I’m querying are generated by Microsoft Forefront TMG though, so I’m basically stuck with MSSQL. At least now I can query those tables from my favourite PHP application framework.

Image copyright © Microsoft. Used with permission from Microsoft.