PHP and MySQL on Windows Azure: Starting with Git

By now, I'm sure you've heard of SitePoint's and Windows Azure's Push The Web Forward contest. If not, it's a contest that requires you to sign up for a trial account on Windows Azure, and deploy any app there to be eligible for a draw in which you can win up to $3000 per app. You can even check out the current entries by visiting the gallery.

As a developer, you can never have enough technologies in your toolbelt, so let's take a look at this behemoth of an environment.

Setting up

In this article, we'll go through the procedure of setting up a Hello World PHP app on Windows Azure and connecting it to the database on that very environment. We'll also deploy via FTP and we'll set up auto-deployment through Github.

If you haven't done so yet, please sign up for a free trial account. Once you reach the management dashboard (seen in the screenshot below), we'll set up our starting website.

Creating a new PHP project on Windows Azure

Near the bottom of the screen, click New -> Compute -> Website -> From Gallery, as seen in the screenshot below.

This should open the gallery of web site starter templates, which offers anything from Ruby apps to ready-to-use forums. We're aiming for a from-scratch approach, so scroll down until you reach "PHP Empty Site", and on the next screen, provide an arbitrary URL name for it. I chose SitePoint01 for this exercise.

A few seconds later, you app should be up an running. In fact, you should see it in the list of Web Sites and it should have a status of "Running".

Click the name of the app to enter its dashboard, and take some time to look around – get familiar with the various screens. The first screen you see is the "Quick Start" screen, designed to get you up and running with the most common features quickly, and the rest are self explanatory.

If you visit your app in the browser now, you'll get an empty screen. This is fine. After all, we didn't do anything yet. Let's configure a database before moving on.

Configuring a database on Windows Azure

Go to "Linked Resources" in your app's dashboard, and click "Link a resource". We want to create a new resource, and we'll choose MySQL database. On the next screen of the wizard, give it a name, agree to the terms, and click the "Complete" tick in the bottom right corner of the popup window.

After a few seconds of setting up, the resource should be ready and linked.

If you go to the dashboard now, the first option under "Quick Glance" should be "View Connection Strings", which, when clicked, produces a popup with database credentials you can use to access the database we've just set up.

To manage our database, we'll use Oracle's MySQL Workbench in this article (feel free to use any other SQL management tool if you prefer). When you create a MySQL database on Windows Azure, the database is actually created on ClearDB. ClearDB databases can be connected to remotely, and that's what we'll do.

Download MySQL Workbench and opt to create a new connection. In the window, fill in the data from the previous screenshot – the host name, user name and password. You can even download SSL keys from ClearDB (see screenshot below) and use them in the SSL tab of the connection in MySQL Workbench to make your connection more secure.

After pushing Test Connection, Workbench should connect successfully. After pushing connect, it should display an empty database.

To set up a basic table with some sample data we'll be retrieving, paste the following SQL statements into the SQL tab in Workbench, and click the "Execute" button, represented by the thunderbolt icon in the SQL tab's toolbar. Naturally, replace "sitepoint01" with the name of your own database.

USE `sitepoint01` ;

CREATE TABLE IF NOT EXISTS `sitepoint01`.`hello_world` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

START TRANSACTION;
USE `sitepoint01`;
INSERT INTO `sitepoint01`.`hello_world` (`id`, `data`) VALUES (1, 'I was retrieved from the database!');

COMMIT;

We now have data in our MySQL database. Keep in mind that you can also use Workbench's excellent Model editor to build tables and relations graphically, then export it all directly to your remote database. Remember to save often, though – Workbench is a fantastic tool, but it can be very buggy and often crashes.

Uploading files to Windows Azure

Uploading files to Windows Azure apps can be done in two ways: through source control, or via FTP.

Using FTP to upload files to Windows Azure

If you go to your app's dashboard, you should see the FTP Host Name in the right hand side menu – but no user. We'll need to set this up to try it out. Under "Quick Glance" again, click "Set Up Deployment Credentials" to create an FTP user. Once done, make sure you have an FTP client installed on your machine – I'll use FileZilla for the purpose of this article.

Connecting couldn't be more straightforward. Simply enter the credentials into the New Site dialog of FileZilla as shown below, and click connect.

As soon as the directory tree opens, you can navigate to /site/wwwroot/ where you'll find two files: favicon.ico and index.php. Download the index.php file with FileZilla into a local folder you decide will hold the app's contents, open it in your favorite code editor and change the contents to the following:

<?php
echo "Hello World";
?>

Then, upload the file back to Azure. If you're asked to overwrite the original file, do it. Now if you visit your web app's URL (the URL can be seen in the Dashboard among the other data in the right hand side column), you should see the infamous Hello World message.

However, using FTP to upload your site's files is so very… nineties. Let's enter the Information Era.

Connecting a Github repo to Windows Azure

To deploy via Github, we'll need to "pimp" our app's local folder a bit first. Add the following files into it: .gitignore and README.md.

These just make for a clean repository. Every repo should have a README file, no matter how trivial, and a .gitignore file which makes sure no junk is commited.

If you're on Windows, and you haven't installed Git tools for Windows yet, do it. These tools come with a bash app (among other useful items) which makes the Git repo setup process identical on all operating systems.

Now cd into the app's local folder, and run the commands:

git init
git add .
git commit -am 'First commit'

I'm currently on Windows, and this is what the process looks like:

Now create a new repository on Github. I called mine "sitepoint-azure1". Add the origin to the repo we just initialized locally, as per Github's instructions.

git remote add origin git@github.com:Swader/sitepoint-azure1.git
git push -u origin master

With our app's repo now online, let's configure it in Azure's management panel. Go to either the dashboard of your web site, or to the Quick Start screen, and click the "Set up deployment from source control" option. In the pop up that appears, select Github, click next, authorize whatever Azure is asking you to authorize and find the repo you just created in the list of offered repositories.

A few seconds should go by and the deployment should be ready. In fact, as soon as it finishes processing, your app's files on the Azure instance will already be updated. If you refresh the view in your FTP client now, you'll notice our README and .gitignore files are there too. It's all automatic. It really is that simple! Now whenever one of your fellow devs updates your Github app and you accept their pull request into the master branch, Azure will automatically take care of keeping it up to date. You can even force a sync manually with the buttons in the footer of the Deployment tab if you feel like it isn't doing a quick enough job.

We'll demonstrate how it auto-updates in the next section.

Connecting a PHP app to a MySQL database on Windows Azure

First, let's consider some security. When connecting to the database, we need to let PHP know which credentials to use to connect to MySQL. But if we include them directly into the file, everyone will be able to see our password in Github. Hence, create a config.php file in the app's local folder with the following contents (replace the credentials to match yours, of course):

<?php
// Replace with your own
$sUsername = 'user';
$sPassword = 'pass';
$sHost = 'host';
$sDb = 'db';

Then, make sure it never gets commited by adding the following block at the top of our .gitignore file:

# Configuration
config.php

Finally, upload the config.php file to the server manually via FTP. This makes sure it stays put – it will be ignored by the auto-deployment and by our local Git repo.

Now re-open the local index.php file, and change its contents to the following:

<?php

require_once 'config.php';

try {
    $oConn = new PDO('mysql:host='.$sHost.';dbname='.$sDb, $sUsername, $sPassword);
    $oConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $oStmt = $oConn->prepare('SELECT data FROM `hello_world`');
    $oResult = $oStmt->fetchAll();

    foreach ($oResult as $aRow) {
        print_r($aRow['data']);
    }

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Save, run git commit -am 'Reading from DB' in your app's folder, followed by a git push.

Observe the deployment screen in your app's management panel – the deployment history will be growing with every push to the master branch. Your app should already be up-to-date – Azure instantly pulls the Github content.

If you now re-visit your app's URL, you should get an "I was retrieved from the database!" message displayed.

Conclusion

As you can see, building and deploying an app on Windows Azure is dead simple. You can have an automated deployment process set up in minutes, all hosted on a highly scalable and reliable infrastructure with built-in metrics and beautiful dashboards to make managing your app as easy as possible.

Armed with this knowledge, why not have a go at the PTWF contest and see if you can take some prizes home?

If you'd like to see a more specific Azure topic covered, or you just have some general feedback, please leave it in the comments below!