PHP and MySQL on Windows Azure: Starting with Git

Share this article

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.

Key Takeaways

  • Developers can easily set up a PHP project on Windows Azure and connect it to a MySQL database within the same environment, with the option to deploy via FTP or auto-deployment through Github.
  • Azure allows for the simple configuration of a MySQL database that can be managed using Oracle’s MySQL Workbench or any other SQL management tool, and can be connected to remotely.
  • Uploading files to Windows Azure apps can be done through source control or via FTP, with the ability to connect a Github repo to Windows Azure for more streamlined updates and deployment.
  • Connecting a PHP app to a MySQL database on Windows Azure involves creating a config.php file with the necessary credentials, ensuring it never gets committed by adding it to the .gitignore file, and manually uploading it to the server via FTP.

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!

Frequently Asked Questions (FAQs) on PHP, MySQL, and Windows Azure

How do I connect PHP to MySQL on Windows Azure?

Connecting PHP to MySQL on Windows Azure involves a few steps. First, you need to create a MySQL database on Azure. Once the database is created, you can use the MySQLi or PDO extension in PHP to establish a connection. You will need the host name, database name, username, and password to connect to the database. Once the connection is established, you can execute SQL queries to interact with the database.

How do I deploy a PHP application on Windows Azure using Git?

Deploying a PHP application on Windows Azure using Git involves creating a local Git repository for your PHP application, pushing the application to Azure, and then configuring Azure to use Git for deployments. You will need to install the Azure CLI and Git on your local machine, and then use the ‘az webapp create’ command to create a new web app on Azure. Once the web app is created, you can use the ‘git push azure master’ command to push your application to Azure.

How do I use Azure Pipelines with MySQL?

Azure Pipelines can be used to automate the deployment of your MySQL database changes. You can create a pipeline that includes a task to deploy your SQL scripts to your MySQL database on Azure. The Azure MySQL Deployment task can be used to execute SQL scripts against your MySQL database.

How do I connect to a MySQL database on Azure from a PHP application?

To connect to a MySQL database on Azure from a PHP application, you can use the MySQLi or PDO extension in PHP. You will need the host name, database name, username, and password to connect to the database. Once the connection is established, you can execute SQL queries to interact with the database.

How do I create a MySQL database on Azure?

Creating a MySQL database on Azure involves using the Azure portal to create a new MySQL database server, and then using the MySQL command-line tool or a MySQL client application to create a new database on the server. You will need to specify the server name, server admin login name, password, and location when creating the server.

How do I use Git to deploy a PHP application on Azure?

To use Git to deploy a PHP application on Azure, you need to create a local Git repository for your PHP application, push the application to Azure, and then configure Azure to use Git for deployments. You will need to install the Azure CLI and Git on your local machine, and then use the ‘az webapp create’ command to create a new web app on Azure. Once the web app is created, you can use the ‘git push azure master’ command to push your application to Azure.

How do I automate the deployment of MySQL database changes using Azure Pipelines?

To automate the deployment of MySQL database changes using Azure Pipelines, you can create a pipeline that includes a task to deploy your SQL scripts to your MySQL database on Azure. The Azure MySQL Deployment task can be used to execute SQL scripts against your MySQL database.

How do I use the MySQLi or PDO extension in PHP to connect to a MySQL database on Azure?

To use the MySQLi or PDO extension in PHP to connect to a MySQL database on Azure, you need to install the extension and then use the ‘mysqli_connect’ or ‘pdo’ function to establish a connection. You will need the host name, database name, username, and password to connect to the database.

How do I use the Azure CLI to create a new web app on Azure?

To use the Azure CLI to create a new web app on Azure, you need to install the Azure CLI on your local machine, and then use the ‘az webapp create’ command to create a new web app. You will need to specify the name, resource group, and plan for the web app.

How do I use the MySQL command-line tool or a MySQL client application to create a new database on a MySQL server on Azure?

To use the MySQL command-line tool or a MySQL client application to create a new database on a MySQL server on Azure, you need to connect to the server using the host name, username, and password, and then use the ‘CREATE DATABASE’ SQL command to create a new database.

Bruno SkvorcBruno Skvorc
View Author

Bruno is a blockchain developer and technical educator at the Web3 Foundation, the foundation that's building the next generation of the free people's internet. He runs two newsletters you should subscribe to if you're interested in Web3.0: Dot Leap covers ecosystem and tech development of Web3, and NFT Review covers the evolution of the non-fungible token (digital collectibles) ecosystem inside this emerging new web. His current passion project is RMRK.app, the most advanced NFT system in the world, which allows NFTs to own other NFTs, NFTs to react to emotion, NFTs to be governed democratically, and NFTs to be multiple things at once.

AzureCloudcloud developmentgithubmicrosoftmicrosoft azureMicrosoft Windows AzuremysqlPHPwindowsWindows Azure
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week