Welcome to Five Labs, a step-by-step guide to building applications using Five.

This lab guides you through creating a custom online database application that interacts with the Google Maps API. You will develop your application locally using Five's free download. To get started, make sure Five is installed, and up and running on your computer. Please note that the final application uses some of Five's paid features, such as creating an HTTP client, and that a subscription is required to test the application with all its features. However, you can follow all of the steps of this lab inside Five's free version.

Lab Objectives & Outcomes

By the end of this lab, you will have developed a custom online database application that

1. Stores data that is retrieved from the Google Maps & Google Places API in its own MySQL database,

2. Gives end-users the ability to create, read, update, and delete the data stored inside your database, using a web GUI, and

3. Gives end-users the ability to add additional data to their dataset.

The entire application is built inside of Five and contains:

1. Two tables in our custom-built MySQL database,

2. One SQL query, and

3. Three JavaScript functions.

In total, we wrote 108 lines of custom code in SQL and JavaScript to build the entire application. Everything else that is required to build & deploy the applications comes from inside Five.

Keen to learn how? Keep on reading!

Application Requirements

First up, let's define our application requirements.

Let's say we are software engineers with a palate for the finer things in life: when we're not programming, we enjoy good food and wine.

We would now like to build our custom application that fetches all restaurants in a certain postcode and lets us add our custom reviews to each restaurant that we have visited.

The application is supposed to let us:

1. Search restaurants by postcode. We'd like to enter a postcode in a form field, and upon clicking a search button, the application is supposed to present us with a list of restaurants operating in the area. This data is supposed to be retrieved using the Google Maps API.

2. Select relevant restaurants from the search results. We would like to select those restaurants that sound interesting to us and store them permanently inside our MySQL database.

3. View all stored restaurants, as well as additional information about them, such as their opening hours retrieved from the Google Places API.

4. Add additional information about each restaurant, such as an ability to track if the restaurant has been visited before, and our verdict on their food.

As a non-functional requirement, we would like the application to be a web application that runs in any browser and is responsive to screen size.

Steps To Develop The Application

Here are the steps required to build the application:

1. Create a new application inside Five,

2. Define your MySQL database tables and fields inside Five,

3. Add three JavaScript functions and a process to the application:

• The first JavaScript function GetRestaurants gets restaurants by postcode from the Google Maps API,

• The second JavaScript function SaveRestaurants lets us fetch additional data about each restaurant, such as the opening hours from the Google Places API, and insert their data into our database,

• The third JavaScript function SaveRestaurantButton is a client-side function to the call server-side function described in 2.

• We also need one process to associate the GetRestaurants function with a DoRun event in our end-user application.

1. Write a SQL query SelectRestaurants to display all saved restaurants in a report.

2. Create a form and a dashboard for your end-users to view and edit saved restaurants.

Let's go through each step with code samples one by one.

Step 1: Creating a New Application Inside Five

Launch Five, and click the yellow + button to create a new application. Give it any name and click save. Don't worry about any of the other application settings. Next click on Manage to start developing your application.

Step 2: Define Your MySQL Database Tables and Fields

The next step in building an application is to define our MySQL database tables and fields. Our application has a total of two tables:

The first table is named Restaurants and temporarily stores all restaurants that we retrieve from the Google Maps API. The second table is named SavedRestaurants, and permanently stores all restaurants that we wish to add to our database.

Five comes with a handy table wizard that makes creating database tables a breeze. Simply follow our Table Wizard documentation to create the tables required for this application.

Here is how each table should look once you have created them from inside Five.

Step 3: Writing JavaScript Functions Inside of Five

Now, let's work on the most important part of the application: the functions that help us retrieve the data from the Google Maps API. Click on Functions, and then on the plus button to add a new function. We have added comments to each function below to explain their workings. You can delete those comments if you do not require them.

The GetRestaurant Function

When you interact with an API, you typically make GET requests to retrieve data from web servers. So let's start with the GetRestaurant JavaScript function. Remember this function is designed to retrieve information from the Google Maps API, so you will also need to have an API key to make this function work properly.

function GetRestaurants(five, context, result)  {
    const API_KEY = 'Insert Your API Key Here';
    // Getting the PostCode by the user else keep the default Values as 4000
    const POST_CODE = context.PostCode ? context.PostCode : '4000'
    //URL for our API 
    const url = `https://maps.googleapis.com/maps/api/place/textsearch/json?query=restaurants%20in%20${POST_CODE}%20QLD&key=${API_KEY}`;
    // Setting Up Our httpClient and sending a get request to places API
    const client = five.httpClient();
    let httpResult = client.get(url);
    if (httpResult.isOk() === false) {
      return five.createError(httpResult);
    }
    let sql; // SQL query
    let queryResults; // result for the query
    let place_id; // place id for each restaurant
    let guid; // GUID for the primary key for our table
    // Delete Data from the table as we repopulate with every call
    sql = 'DELETE FROM Restaurants'
    queryResults = five.executeQuery(sql, 0);
    // mapping through each restaurant from the API
    httpResult.response.results.map(item => {
      // getting the id and generating GUID
      place_id = item.place_id;
      guid = five.uuid();
      // inserting values into the table
      sql = `INSERT INTO Restaurants (RestaurantsKey,PlaceID,name, address,status) VALUES (?,?,?,?, 'Not Saved')`
      queryResults = five.executeQuery(sql, 0, guid, place_id,item.name,item.formatted_address);


      if(!queryResults.isOk()){
        return five.createError(queryResults);
      }
    })
    return five.success(result);
}

When reading through the function above, you might be wondering why our INSERT INTO statement contains four question marks ?,?,?,? for its values. Inside Five, question marks are used as placeholders for the values from the variable in a INSERT INTO statement.

The SaveRestaurants Function

Next up, is the SaveRestaurants JavaScript function. Remember this function is designed to fetch additional data about each restaurant, such as the opening hours from the Google Places API, and inserts their data into our database.

function SaveRestaurants(five, context, result)  {
    const API_KEY = 'Insert Your API Key Here';
    // getting the place_ID for the place details API to get details of a restaurant
    const place_id = context.PlaceID
    five.log(JSON.stringify(context))
    const API_URL = `https://maps.googleapis.com/maps/api/place/details/json?place_id=${place_id}&key=${API_KEY}`
    const client = five.httpClient();
    let httpResult = client.get(API_URL);
    // getting the opening hours
    const hours = httpResult.response.result.current_opening_hours ? httpResult.response.result.current_opening_hours.weekday_text.toString() : 'Opening Hours Not Defined'
    const openingHours = hours.split(',').join('\n')
    // getting the phone number
    const phoneNumber = httpResult.response.result.formatted_phone_number ? httpResult.response.result.formatted_phone_number : 'Phone Number Not Defined'
     let guid = five.uuid();
    // saving data into SavedRestaurants Table
    let sql = `INSERT INTO SavedRestaurants (SavedRestaurantsKey,Name, Address,PhoneNumber,OpeningHours) VALUES ('${guid}','${context.Name}','${context.Address}', '${phoneNumber}', '${openingHours}')`
    let queryResults = five.executeQuery(sql, 0);
    if(!queryResults.isOk()) {
        return five.createError(queryResults);
    }
    // updating the status in Restaurants to Saved
    sql = `Update Restaurants SET Status = 'Saved' Where RestaurantsKey = ?`
    queryResults = five.executeQuery(sql, 0, context.RestaurantsKey);
    if(!queryResults.isOk()) {
        return five.createError(queryResults);
    }
    five.commit()
    return five.success(result);
}

The SaveRestaurantsButton Function

Last is the SaveRestaurantsButton JavaScript function. Remember this function is a client-side function to the call server-side SaveRestaurants function.

/**
 *
 * Using the Client Side Function to call server side function : SaveRestaurants
 *
 */
function SaveRestaurantsButton(sender, context, result)  {
    let variables = Object.assign({}, form.field);
    const functionName = 'SaveRestaurants';
    var _five = five; // need to preserve five as returning will null this object.
    five.executeFunction(functionName, variables, null, null, null, function (result) {
    });
    return five.success(result);
}

The DoRun Event

We also need one process to associate the GetRestaurants function with a DoRun event in our end-user application. Why? Because we want this function to run upon the click of a button. This is called a process inside of Five.

Click on Processes in the left-hand menu, and create a new process. Click on events, and select the GetRestaurants function in the Do Run drop-down box. Your process should look like this:

Step 4: Write a SQL Query Inside of Five

Our final application is supposed to display all restaurants we've visited inside a query. To achieve this, we will write a short SQL query called Restaurant Report. Click on Queries, and create a new query that looks like this:

Select
    RestaurantsKey,PlaceID,Name,Address, PhoneNumber,
    'Save Details'as'Save Details'
from Restaurants
    Where Status = 'Not Saved'

Step 5: Creating The Form & Dashboard

Last, create a form for your end-users to see all saved restaurants. To do so, you can use Five's Form Wizard and select the SavedRestaurants table. Five will automatically fill in all required information for creating the form for you, so all you need to do is follow the steps of the wizard.

Last, we'd like to add a dashboard to our end-user application. The dashboard will let us enter a postcode, and display the results from our GetRestaurants function below it. To do this, click on Dashboards, and create a new dashboard with any name and a 1-column and 5-row grid. In Actions, put GetPostcode into cell A1, and cell A2.

Conclusion

And that's it. You've finished building an application that retrieves restaurant information and stores them inside your own MySQL database! In this tutorial, you have used several of Five's features: from creating and managing your own MySQL database to writing JavaScript functions and using Get requests to fetch information based on your search parameters. You have also created forms and dashboards for your end-users to interact with your data.

If you have any other questions or would like to learn more about building applications using Five, visit our user community to learn from other users or to get answers to your questions!