Creating a PHP web site with a ClearDB MySQL Database, all with a FREE Azure Student Account
As a student developer, you can be NEVER aware of too many technologies, having an experience of a varity of services and technologies simply helps demonstrate your skills and competences.
With the new offering from DreamSpark you can spin up web sites and databases using various technologies including PHP, Node.JS and .Net and MySQL.
In this blog I will be talking about using PHP on a Microsoft Azure Student Sunscription using the free MySQL ClearDB Database.
In this blog I will cover:-
Step 1. Setting up Azure
Step 2. Creating a new PHP WebSite on Azure
Step 3. Configuring a MySQL database on Azure
Step 4. Uploading files to Azure
Step 5. Connecting a Github repo to Azure
Step 6. Connecting a PHP app to a MySQL database on Azure
Right lets get started
Step 1. Setting up Azure
Creating a simple Hello World PHP Web app running on a MYSQL ClearDB database hosted on Azure.
In this blog we will be connecting to the database.
Deploying the web site via FTP
Setting up continuous integration and deployment through Github.
Prerequisites please ensure you have signed up to DreamSpark.com and activated your Azure Subscription see this blog.
Login into you Azure Portal via https://www.azure.com
Step 2. Creating a new PHP WebSite on Azure
Choose New Web + Mobile then select Web App as seen in the screenshot below.
Provide an arbitrary URL name for your web site and click create
This process should take a few minutes, after this your 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 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 a successfully messgae. T
This is fine. After all, we didn't do anything yet. Let's configure a database before moving on.
Step 3. Configuring a MySQL database on Azure
Go to Create – Data and Storage – MySQL Database
We want to create a new MySQL Database,
On the next screen of the wizard, give it a database name and select create
After a few minutes later, the resource should be ready and linked.
If you go to the dashboard now you should see your database
To manage your database, click manage Database
You can also use other tools such as Oracle's MySQL Workbench or 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 "DatabaseName" with the name of your own database.
1: USE `sitepoint01` ;
2:
3: CREATE TABLE IF NOT EXISTS `DatabaseName`.`hello_world` (
4: `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
5: `data` VARCHAR(45) NULL,
6: PRIMARY KEY (`id`))
7: ENGINE = InnoDB;
8:
9: START TRANSACTION;
10: USE `DatabaseName`;
11: INSERT INTO `DatabaseName`.`hello_world` (`id`, `data`) VALUES (1, 'I was retrieved from the database!');
12:
13: 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.
Step 4. Uploading files to 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 Azure
If you go to your web site dashboard, you can create a FTP account
We will need to set up a FTP publishing/deployment account to try it out you can get the full url of the FTP and FTPs server from the Settings – properties screen
Url should be in this format ftp://**************.ftp.azurewebsites.windows.net
Under Settings – Publishing Deployment Credentials - 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/
you should 1 single file hoststarting.html remove this and upload your php file.
Create Index.php in your favourite code editor and change the contents to the following:
1: <?php
2: echo "Hello World";
3: ?>
Then, upload index.php to Azure.
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.
Step 5. Connecting a Github repo to Azure
To deploy via Github
First step is add git ignore to the host directory you can download it here .gitignore and README.md you need to create a readme.md in your favourite txt editor.
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 committed.
If you're on Windows, and you haven't installed Git tools for Windows yet, do it.
You can also download the Git Student Developer Pack from DreamSpark The GitHub Student Developer Pack assembles a great set of tools and services, free to students! Get your own domain name with SSL, explore crowdsourcing, email services, and much more. Combined with the free Visual Studio Community 2015 and Visual Studio Online, it’s an unbeatable combination to get your coding project off the ground. Get your GitHub Student Developer Pack today 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:
1: git init
2: git add .
3: git commit -am 'First commit'
Now create a new repository on Github. I called mine "mywebsite". Add the origin to the repo we just initialized locally, as per Github's instructions.
1: git remote add origin git@github.com:Lee/mywebsite-azure1.git
2: git push -u origin master
simply replace Lee/mywebsite with your details
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 then setting then publishing – continuous deployment 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 another 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.
Step 6. Connecting a PHP app to a MySQL database on Azure
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):
1: <?php
2: // Replace with your own
3: $sUsername = 'enteruser';
4: $sPassword = 'enterpassword';
5: $sHost = 'enterhostname';
6: $sDb = 'enterdbName';
7: ?>
To ensure these secrets never get committed simply add the following lines at the top of our .gitignore file – this addition simply ensure that git ignores your config.php file and settings.
1: # Configuration
2: 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:
1: <?php
2:
3: require_once 'config.php';
4:
5: try {
6: $oConn = new PDO('mysql:host='.$sHost.';dbname='.$sDb, $sUsername, $sPassword);
7: $oConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
8:
9: $oStmt = $oConn->prepare('SELECT data FROM `hello_world`');
10:
11: // VERY IMPORTANT HERE, RUN THE QUERY:
12: $oStmt->execute();
13:
14: $oResult = $oStmt->fetchAll();
15:
16: foreach ($oResult as $aRow) {
17: print_r($aRow['data']);
18: }
19:
20: } catch(PDOException $e) {
21: echo 'ERROR: ' . $e->getMessage();
22: }
23:
24: ?>
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 PHP WebSite with a MySQL database all on Microsoft Azure is really easy.
You have also learn that 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.