Introduction

Assumptions: You want to create a multi-user mobile web app that's going to be accessible anywhere on the globe. You want this app to be fast in terms of creating, updating and deleting records. You want all your records to be persisted on a server and easily accessible whenever anyone logs in with whatever device they are using. This app should run directly from the web, however can be easily ported to a hybrid app.

My previous article, spoke a lot about creating crud web applications using JQuery Mobile. A lot has taken place eversince then and whilst I am yet to have a similar article using WebSQL and IndexedDB, I wanted to be able to create something that anyone could use, irrespective of device and the information will be available anywhere. Whilst this can be done using any backend database like MySQL, MongoDB etc, I opted for the single file records approach.

What does this mean? This means that each record on the server is stored as a single json file.

When a user reads a record from the displayed record, php is used to read the file record from the server and then displayed. This is performed using an Ajax call. There are pros and cons in relation to this approach, however for small databases that you might want to create, its a perfect vehicle. However, the speed of your server and your client computer are of utmost importance in the performance of this approach.

Some silly assumptions: You are familiar with JQuery Mobile, you also know some JavaScript and are able to make Ajax calls, you know how to set up a web server and file permissions. You are also familiar with JSON.

Background

We are going a Project Tracker Mobile application with just two models, Projects and People. The People model will store details about the people resources in our projects and the Projects will store simple details of our projects. Because people should have controlled access to the application, we will add authentication for this app.

3. Priority - compulsory, radio button, this could be low, medium, high

4. Due date - compulsoty, date picker

5. Percent Complete - slider to indicate percentage complete

5. Owner - dropdown list derived from People file

6. Notes - text area

This is translated into this UI here as depicted with Figure 1.

Figure 1: Project Screen

Pressing Cancel will take a user to the Projects Listing Screen and Pressing Save, will save the project record as a single json file on the web server Project folder. The file name being the Project Name e.g. Write-CodeProject-Article.json as depicted in Figure 1.1. below

Figure 1.1. : Project JSON file record.

Each person will have the following fields

1. Full Name - compulsory, text

2. Email Address - compulsory, email

3. Reports to - compulsory, text, the person this person will report to for governance purposes.

This is translated to be Figure 2 below.

Figure 2: People Screen

and the Person record stored as the following JSON file record.

Figure 2.1 Person JSON File

From the above image, one can see from the folder structure how the records are organized, and the various php files that make this possible. I will show how this is all glued together with the code below.

Each user will have the following fields

1. First Name - compulsory, text

2. Last Name - compulsory, text

3, Email Address - comulsory, email

4. Password - compulsory, unique, password

5. User Role - compulsory, dropdown for Administrator/User

6. Active - compulsory, checkbox

This is translated as Figure 3 below

Figure 3: User screen

with the resulting JSON record being as per Figure 3.1 below

Figure 3.1: User json file record

You will note that the password stored here is encrypted to hide it from the normal eye. My article here discussed how to encrypt and decrypt passwords using the Stanford Crypto Library.

ajaxSave...php - this file gets a passed json string and save it to the web server using the primary key of the file as a file name.

ajaxDelete...php - this file gets a passed primary key and deletes the json file from the server

ajaxGet...php - this loops through all available records in that folder and returns a \n delimited string of each of the files names. These are parsed to get the details of each file and the results stored as json objects.

NB: A folder is created on the folder for each model e.g. all users will be stored under a User folder on the server.

Using the code

In my article, Create CRUD web app using JQuery Mobile and LocalStorage I explained in detail how to create a crud web app and one can refer to that for more details. As CRUD methods are basically the same for each model, I will deeply delve on 1 model here, the Project Model, as both the User and Person model follows the same methodolody in Adding, Updating and Deleting Records.

MyProjects is a web app that will store and retrieve details of our projects. Users of MyProjects should be authenticated and MyProjects should be accessible from the internet/web. There is a governance structure of the project hierachy here and we need to also keep that in mind. Each project has an owner allocated to it.

As MyProjects will run from a webserver, PHP should be installed on the server and permissions should be set for the folder that will host MyProjects projects, users and people.

Starting MyProjects

An end user is provided with a link of the website where myprojects is hosted, for example, mine is here as a demonstration. As soon as the user opens the link, a welcome screen is shown. If a user does not have an account, they can Sign Up. As this is a live application, the process of user sign ups should be properly controlled but due to a demonstration here, a loose sign up process has been created.

Figure 4: Sign In

The sign in screen above allows a user to sign in to use the application and create, update and delete project, persons, users details. As authorization has not been added here, the process of CRUD operations per model should be tightly controlled for data centric secure web apps.

The Sign In screen is defined with the html below. All view definitions are stored within the index.html file as per attachment above.

What really happens when one clicks Sign In? This reads a user record from the webserver.

The code behind this button works like this. The user email address and password are read from what has been input on the screen using JQuery syntax. Then a php call to ajaxGetUser is made to read a file from the web server that is made up of the email address, thus for me, this file will be anele@mbangas.com.json. If the file exists, the password entered is compared to the password that is stored on the file, if these match, the first check is fine. The second check is whether the account is active or not. If the account is not active the second check fails and the end user gets warned. If all goes well, the user is taken to the main springboard of the application.

app.SignInUser calls the ajaxGetUser.php file which basically reads the user file using the email from the web server and returns a json string of the file. This is then parsed for easy reading into an {} object.

as indicated in app.SignInUser method. All operations to read files from the webserver using a get method will follow the same approach. The main determinant is the folder the content is being read from. In this particular case its the User folder.

What happens when Sign Up is clicked? This facilitates the creation of a record of a user on the webserver.

When a user clicks Sign Up, he/she is taken to the add user screen as depicted in Figure 3 above. When all the user details are entered, the user then clicks Save.

What happens when Save is clicked? A new user is being added to the webserver, creating a new record.

As soon as the user has entered their details and click Save, the following method is called to save the user details to the web server.

The details entered by the user are read from the screen and stored in UserRec, this object is then passed to app.addUser to save it to the server. Lets look at pgAddUserGetRec() that gets the entered screen details.

The script above uses JQuery syntax to read the details from the screen and save them as an object. This object gets passed to app.addUser and this basically does this.

// add a new record to server storage.
app.addUser = function (UserRec) {
// define a record object to store the current detailsvar Email = UserRec.Email;
// cleanse the record key of spaces.
Email = Email.replace(/ /g, '-');
UserRec.Email = Email;
//convert record to json to write to servervar recordJSON = JSON.stringify(UserRec);
// save the data to a server file, use the post method as it has 8MB minimum data limitationvar req = Ajax("ajaxSaveUser.php", "POST" , recordJSON);
if (req.status == 200) {
//show a toast message that the record has been saved
toastr.success('User record saved.', 'MyProjects');
//find which page are we coming from, if from sign in go back to itvar pgFrom = $('#pgAddUser').data('from');
switch (pgFrom) {
case"pgSignIn":
$.mobile.changePage('#pgSignIn', {transition: pgtransition});
break;
default:
// clear the edit page form fields
pgAddUserClear();
//stay in the same page to add more records
}
} else {
//show a toast message that the record has not been saved
toastr.error('User record not saved. Please try again.', 'MyProjects');
}
};

The email address is the primary key for each user and it gets cleaned for empty spaces here. The passed user object read from the screen is then JSON stringified to convert it into a string. Then an ajax call to ajaxSaveUser.php is made posting the json string to it to save it into the web server. If the user is successfully saved, a toast is shown telling the user the record was saved. This is checked with the status message of 200 that gets returned by the ajax call. As the user in this case accessed this screen from the sign in screen, as soon as tehy save their details, they will be taken back to the Sign In page to sign in.

This Add User screen as depicted in Figure 3 has a panel and also a menu button on the header but these are hidden because the user in this instance is Signing Up.

The ajaxSaveUser.php file that saves the user details to the web server.

Remember, a stringified json object is posted to the web server when this method is called. The contents of that string are read and saved as $record. This is then decoded with json_decode to make it an easily accessible array so that we can get the email address, which is the primary key to the user record. We then read the email address to a varible $email by executing ->Email in our decoded user record.

During the first run, the User directory might not exist, so we then check if that folder exists, if not, its created. We then define the complete file name for this record by defining the jsonFile as

$jsonFile = "User/" . $Email . ".json";

Remember, the email address read does not have a .json extension, thus we add it here. The file is then opened and the contents of our user record written as is (as a string) to the file.

If you have been following closely, I have already demonstrated CR of our CRUD application. We read a user details for sign in and also created a user with Sign Up already. This is the same approach that is followed throughout the application to CRUD operations that are herein. We will now demonstrate Updating Users and deleting users from the database so that the UD portion of our CRUD operation is completed. After that we will talk about MyProjects some more.

From the springboard that appears after a succesful sign in as depicted in Figure 5, select Users. This will list all available Users in MyProjects.

Figure 6: Users (Creating a similar listing was discussed in almost all the referenced articles)

Currently there is just myself on the list of users captured. If you select a user from this list, the screen to update the user will be shown. This app uses different screens to add records and also update records. As much as this may seem duplication effort, I have found it easier to maintain my code with that approach.

Figure 7: Edit User

As you can see the footer navigation bar above, there are three buttons on it. 1. Cancel - to go back to the Users listing, 2. Update to save the user details to the web server and 3. Delete to delete the user details. This screen is resembles the Add User screen as depicted in Figure 3 with the exception of one added button, that is the Delete Button.

What happens when a user clicks the Update button? An existing user record is updated on the webserver.

Clicking Update on this screen will read the details from the user form and save these to a database. This behaves the same way as discussed in the Sign Up screen above. The same method is applied by due to the difference in form and page names, app.UpdateUser is called instead.

From the above, when the update button is clicked, the user details are read from the screen and saved into a UserRecNew object by pgEditUserGetRec(). This object is then passed to app.updateUser method which calls the ajaxSaveUser.php script.

This then concludes our CRU part of CRUD that we are talking about here. We read a user record with ajaxGetUser.php, created and updated a record with ajaxSaveUser.php and then we will talk about the Delete method.

The only way to delete a record with this approach is when you select the record from its listing and then clicking the Delete button from the edit screen. Figure 7 above is a typical example of this edit screen.

What happens when Delete is clicked? A record is deleted from the server.

When a user opts to delete a record, a prompt is provided to confirm whether the user wants to delete a record or not as demonstrated below. I have created another user here to demonstrate this.

Figure 8: Confirm Message Box

If the end user is sure to delete the record, he/she should select Yes on this screen and the record will be removed from the webserver.

Creating prompts like this has been discussed in the previous articles, but anyway, lets refresh. The prompt that appears here is actually a page with a data role of dialog.

In this case, the email address of the user, as the user has been defined as a primary key for users is read and stored in a variable named Email. This is cleaned for any empty characters. The header of the message box is set with Confirm Delete, with a title being the email address we want to delete and a prompt of 'Are you sure you want to delete this user? This action cannot be undone.' shown to the user. Then the actions to be undertaken when a Yes / No button are selected by the user set.

When the user selects a Yes button, the app.deleteUser method will be executed.

$('#msgboxyes').data('method', 'deleteUser');

and when a No is selected, then app.editUser will be executed.

$('#msgboxno').data('method', 'editUser');

basically going back to the edit screen.

How do these methods deleteUser and editUser methods get called?

If you note from the code above, data properties have been set for the message box before it is shown, then it is shown with a pop with.

When a user opts for a No, the method to execute is read from the data-method attribute that has been set before the confirm button is shown. The topage and id data attributes are also read and then the provided page is shown.

This approach is followed because the app uses the same messagebox, alerts for all available models. When a user deletes a person from the People screen or deletes a project from the Projects screen, end users will see the same prompting screen but it will act differently according to what is passed to it for each model.

For user deletion, the method to be called will be deleteUser, this is depicted below:

Delete user uses the email address to delete the users. An ajax call to delete a record is executed by..

var req = Ajax("ajaxDeleteUser.php/?Email=" + Email);

if the execution is succesful, the user json file record will be deleted from the webserver and a toast message shown to the user that the record was deleted. Our previous articles dealt with the toast scripts. As soon as the record is deleted/not deleted, the page is changed to User listing page, with the given transition.

<?php//delete the json record file from the server$Email = $_GET['Email'];
unlink('./User/'.$Email.'.json');
?>

ajaxDeleteUser.php basically executes the unlink php method to delete the <user>.json file from the web server.

Designing the MyProjects web app...

For each project, an owner should be defined first. Thus, the first step in recording our projects, we need to add our people. In the springboard after logging in, select People, this will list all available captured people and also makes available a button at top right to named New to create a new person.

Figure 9: People Listing

Selecting a person from the list opens up a Person Edit screen as shown in Figure 2 above. One can then update a persons details by selecting Save or can delete a users details by selecting Delete. An ajaxSavePerson and an ajaxDeletePerson will be executed in such cases.

The primary key for the person's file is the FullName of the person and using that to edit a persons record is used. You might be wondering how all available people are loaded and listed in this listview when the records reside on the server. Let me explain. I will explain with the projects details going forward. If you are on the People screen, select the Menu button at the top left. A panel will appear and then select the Back button. This will take you to the springboard. From the springboard, select Projects, this will list all your projects as depicted in Figure 10 below.

Figure 10: Projects Listing

If you dont have projects yet, select New and add a few projects. The same methods to add a user that was explain above applies.

Listing Projects: How is this done from the server?

As we have indicated, each record is stored as a single json file on the webserver for all records that we store, whether users, people or projects. To list records in a listview, a pagebeforechange method is executed,

app.checkForProjectStorage runs app.getProject to read all existing records from the server, if the are existing records, these are loaded to ths listview by app.displayProject. If there are no projects, the end user is told that there are no projects available.

Lets explore

//get records from JSON.var ProjectObj = app.getProject();

Why are we calling app.getProject()? app.getProject method is used a lot within the app as you will note from the source code. From the look of things, its called 4 times already. You will note that the method to edit a project does not get the project json file record directly but reads all records first into an array and then get the record we are editing from that array. Wow, that's a mouthful already. Yes, that's a very long way of doing things, why read all records when you can just read the json project file you want? That was done on purpose for demonstration purposes only. On a live environment, doing such a read will pull a lot of server resources and might even crash your server. Instead when performing a read, read the json file record you want for an edit as that is quicker and not resource intensive.

The app.getProject method executes ajaxGetProject.php to get all project.json file names from the server. Existing file names are returned as delimited \n string. Then we loop through all these file names and read each one of them and save details of each file into a json {} object. When all these records are read, these are then sorted by the primary key, this being the ProjectName. Note that ajaxGetProject.php is called twice within the app.getProject method. First no file name is parsed and then the second time when the file names are read, the file name is passed. See code lines with //NB// above.

For each project, we get the project name, this will be shown in the title of the listview, the countbubble shows the percentage complete of each project and the description shows the status and priority. This is done via a loop through each project {} element that exists from everything read from the server.

This is all done before the project listing is shown.

Projects Listing Html Definition:

<divdata-theme="a"id="pgProject"data-role="page"><divdata-position="left"data-display="reveal"data-position-fixed="true"id="pgProjectPnl"data-role="panel"><uldata-role="listview"id="pgProjectPnlLV"><li><adata-transition="slide"href="#pgAddProject">New</a></li><li><adata-transition="slide"href="#pgRptProject">Report</a></li><li><adata-transition="slide"href="#pgMenu">Back</a></li></ul></div><headerid="pgProjectHdr"data-role="header"data-position="fixed"><h1>MyProjects</h1><adata-role="button"id="pgProjectMenu"href="#pgProjectPnl"data-icon="bars"data-transition="slide"class="ui-btn-left">Menu</a><adata-role="button"id="pgProjectNew"data-icon="plus"data-theme="b"class="ui-btn-right">New</a></header><divid="pgProjectcontent"data-role="content"><h3>Projects</h3><uldata-role="listview"data-inset="true"id="pgProjectList"data-filter="true"data-filter-placeholder="Search Projects"data-filter-reveal="false"><lidata-role="list-divider">Your Projects</li><liid="noProject">You have no projects</li></ul></div></div>

In its simplest definition, the screen to list projects is basically empty as its details are updated during runtime based on captured projects. This screen has a slide panel to enable access to project reports, go back to the projects listing screen and add a new project.

Clicking New will take one to add a new project as depicted in Figure 1 above.

Project HTML Definition.

As you saw in Figure 1, the project screen is just a simple screen to with basic controls to add a project details as per specifications given. This is defined by this html code here.

app.addProject then saves the record to the server by call ajaxSaveProject.php (saving records was discussed in detail above)

// add a new record to server storage.
app.addProject = function (ProjectRec) {
// define a record object to store the current detailsvar ProjectName = ProjectRec.ProjectName;
// cleanse the record key of spaces.
ProjectName = ProjectName.replace(/ /g, '-');
ProjectRec.ProjectName = ProjectName;
//convert record to json to write to servervar recordJSON = JSON.stringify(ProjectRec);
// save the data to a server file, use the post method as it has 8MB minimum data limitationvar req = Ajax("ajaxSaveProject.php", "POST" , recordJSON);
if (req.status == 200) {
//show a toast message that the record has been saved
toastr.success('Project record saved.', 'MyProjects');
//find which page are we coming from, if from sign in go back to itvar pgFrom = $('#pgAddProject').data('from');
switch (pgFrom) {
case"pgSignIn":
$.mobile.changePage('#pgSignIn', {transition: pgtransition});
break;
default:
// clear the edit page form fields
pgAddProjectClear();
//stay in the same page to add more records
}
} else {
//show a toast message that the record has not been saved
toastr.error('Project record not saved. Please try again.', 'MyProjects');
}
};

To update project details, an end user should locate the project of interest from project listing and then select it, this will open the Edit Project Screen as depicted in Figure 1. The end user can then update its details and click Save or perhaps click Delete if they want to delete the project.

Because the project name is the primary key for projects, this is what gets passed to ajaxDeleteProject.php, which is represented by...

<?php//delete the json record file from the server$ProjectName = $_GET['ProjectName'];
unlink('./Project/'.$ProjectName.'.json');
?>

which basically justs deletes the project file from the web server, all from clicking the Yes button from this prompt.

Figure 11: Confirm Project Delete

For our projects, there is a reporting / governing structure as defined by the people who we capture. You will recall that in the People screen, one has to indicate who a person reports to.

Figure 12: Project Governance Tree

This reporting structure can then be demonstrated with the following d3 tree chart that can also be exported to a picture when a user clicks Export (top right). Creating such a tree has been greatly discussed in the Enhancing MyFamily.Show article I wrote. This is accessible from selecting Menu from People screen and selecting Relationships > ReportsTo, as depicted below.

Figure 13: People Listing Side Menu

One is also able to generate excel reports from captured information for users, people and projects. From each listing selecting Menu provides access to the slide menu. Below is a report of the Projects captured as an example

For reading the web server files, we specify the URL and perform resulting functions based on the return req.status. For writing, we define the method as POST and the data passed being the stringified json text.

We have also added iscroll.js here for all the listview controls. Where forms should scroll, similar methods can be applied to the code to make an element scroll.

Whilst with this approach it seems easy to develop mobile web apps that can be accessible from any device with data that will sit on a server and be accessible to everyone having access to the server, the speed of the server and how your app is designed will affect the performance. It is thus imperative that your code is optimized as much as possible, especially for read and writes to the server.

The challenge here is still generating queries based on the saved information as each record is stored as a single json file on the server. For very small database applications this works perfectly where there is not going to be queries that need to be executed. This approach also ensures that each user can edit/update/delete on record at a time without having to keep all records loaded on the edit screens.

This kind of access means that as soon as a record is updated by another user, it will be available as soon as when saved. There has been no attempt made to lock the records on edit though.

I will however make an attempt to show a progress bar as in some systems, reading all records from the server might have some delays.

Share

About the Author

I'm a Bachelor of Commerce graduate, fell inlove with ICT years back with VB5. Used Pick & System Builder to create a windows app. Very curious, developed my first web database app called Project.Show using ExtJS. Published on Google Play Store, learned JQuery Mobile, a project manager at best. My first intranet app eFas with MySQL.

Fear closes people to a lot of things and we hold ourselves back being held by it. Thus the sooner you believe you can't do something, the sooner everything will work towards that belief. Believe in yourself at all times because you can do anything you set your mind to it!

I have a very beautiful woman and four kids, the best joys in the world. East London, South Africa is currently home.

Awards:

Best Mobile Article of February 2015 (First Prize)
http://www.codeproject.com/Articles/880508/Create-a-CRUD-web-app-using-JQuery-Mobile-and-Loca

Best Mobile Article of May 2015 (Second Prize)
http://www.codeproject.com/Articles/991974/Creating-JQuery-Mobile-CRUD-Apps-using-JQM-Show-Ge