How to Develop a PHP/MySQL Web Application for Mobile Customers Using Vertabelo and Other Cloud Services

Rafał Strzaliński, Senior Software Engineer at Vertabelo

by
Rafał Strzaliński
Senior Software Engineer at Vertabelo

Posted: January 15, 2014

I’m gonna show you how to develop a simple web application. From database design to deployment and finally some front end development. Everything will be made in the cloud. There will be no need to install anything on your laptop. Sounds like late night TV commercials? But it’s not :).

Database Design

Let’s start with the database design:

  1. Log in to Vertabelo. Sign up if you don’t have an account yet (you can use a free trial).
  2. Create a new database model. Choose MySQL as a database engine:

    Create a new database model for MySQL

  3. Design your database. I’ve created only one table. Don’t laugh! Gonna keep this demo simple. In real life, database designers spend some hours ... or days ... at this step.




  4. Now, you can generate a script that will create tables in your database. Database gurus call it “DDL script”. Click the “SQL” icon in the top toolbar:

    Click the 'Generate SQL script' icon in the toolbar

    And then click the “Generate” button:

    Generating SQL script for your database model

  5. Download the script and store it on your computer.

    In this example, the file is called lamp-demo_create.sql.

Pick a Hosting Solution

Now it’s time to deploy it somewhere. If you already have your own server, just skip this part. If not, pick a hosting solution. I’ve chosen DigitalOcean.com. They looked promising and I wanted to verify their claims.

A few steps to prepare a server:

  1. Sign up there.
  2. Create a virtual sever. They call it droplet. Pick “Ubuntu” as a base image. It takes less than 2 minutes.

  3. To setup the server, just follow this instruction: How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu.

Prepare Your Database

You’ve got a working server. You receive an IP address, login and password by email. I didn’t care about security in this example but if you really want to use the DigitalOcean hosting, make sure you immediately change root password, create your own user, etc. They have good how-tos. Let’s prepare the database:

  1. Copy the script to the server (use provided IP address):

    scp lamp-demo_create.sql root@IP.ADDR.PUT.HERE:
    
  2. Log in to the server:

    ssh root@IP.ADDR.PUT.HERE
    
  3. Call MySQL console (password is empty):

    mysql
    
  4. Create database and grants:

    create database demo;
    grant usage on *.* to demo@localhost identified by 'demo';
    grant all privileges on demo.* to demo@localhost ;
    
  5. Call a script generated by Vertabelo:

    mysql -u demo -pdemo demo < lamp-demo_create.sql
    
  6. Connect to the database:

    mysql  -u demo -pdemo demo
    
  7. Add some data:

    insert into message values(1,'hello');
    insert into message values(2,'world');
    select * from message;
    
    +----+---------+
    | id | content |
    +----+---------+
    |  1 | hello   |
    |  2 | world   |
    +----+---------+
    

Front End Development

You’ve got a running database with plenty of data ;). Now it’s time for a front end.

  1. Log in to you server:

    ssh root@IP.ADDR.PUT.HERE
    
  2. Remove the default page:

    rm /var/www/index.html
    
  3. Open file /var/www/index.php and edit its content:

    <?php
    
    $con=mysqli_connect("localhost","demo","demo","demo");
    if (mysqli_connect_errno())  {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    
    $result = mysqli_query($con,"select * from message order by id");
    
    while($row = mysqli_fetch_array($result))  {
      echo $row['content'];
      echo "<br>";
    }
    
    mysqli_close($con);
    
    ?>
    
  4. Save it and open the web page: http://IP.ADDR.PUT.HERE/

You should see data from the database. The page looks ugly, so I’ll show you how to make it nicer without being an HTML wizard. I’ll embed our application on a site created using ActiveMobi.com. Clever me :). ActiveMobi is an easy to use mobile site builder. They provide some really nice looking templates.

Let’s do some front end coding:

  1. Sign up at ActiveMobi.com.
  2. Pick a template you like.
  3. Add an HTML component. Edit its content.

  4. Publish and preview.

The Result

You’ve got a nice looking application that retrieves content from the database.

We made a simple mobile application.

If PHP/MySQL is not your pair of shoes just let me know and maybe I’ll be able to help you.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Learn how to process trees and graphs in SQL, and how to effectively organize long SQL queries. View course Discover our other courses: