Introduction
When it comes to repetitive code, writing the same code for different apps for different clients, this always proves as a tedious exercise and that’s why we always want to have a quick way around it. If you are a software developer, you are aware of the For Next loop and the Do While or Do Until loops. These exist to eliminate the need to do things over and over again by running a loop. In most cases, software developers talk about RAD, an easy way of generating User Interfaces (UI) or Backends (databases) with a simple clicks of buttons.
RAD tools generally generate source code and screens for one easily, by just giving them some properties e.g. database connections and they spill out source code. The reason that they exist in the first place is 1. elimination repetition and 2. eliminating the need to retype and recreate source code over and over again that does CRUD functionality for example, by doing it all for you.
Thus in essence, someone else writes the code for you and you dont, after specifying some terms and conditions for your app. In case of a RAD tool, someone smart wrote it to create the source files, the database connection strings, the database creation statements etc.
I bet in all those instances, the issue was, we are faced with doing almost the same steps over and over again,e.g. creating a CRUD (Create, Update, Delete) database app, create user interfaces, backends etc, why don’t we write a RAD tool that can do that for us and then we tweak the end result to meet the client needs? Eureka! Yes, we are lazy when it comes to doing repetitive tasks all over again and thus will find ways to work smart, by exactly doing that, find and develop RAD tools to help us with our apps, at the end have reliable software apps using the latest technologies available.
I have been developing software for a while now, actually I think I have passed the 10,000 hours mark a long time ago. Sadly with that in mind, with the new developments when it comes to mobile devices, I had due to curiosity, start the process all over again and educate myself on app development for mobile. It’s been two years now doing that and fortunately I have managed to get some useful tools to help me along my dreams.
Three weeks ago I was faced with a coding issue of having to generate php scripts for my database app. The app had to be available for both Android and iOs and will use CRUD functionality for a remote MySQL server. I wanted something simple and straightfoward. After some Googling around the net for something that could help me achieve what I wanted to no avail. I decided that I should just do it myself.
One of the challenges I was facing is that I have never used PHP with MySQL. I had used MySQL in a lot of apps that I have done, but with Microsoft Technologies, so how was I going to do this?
Case Scenario: I have 10 or more tables in my database that are somehow related. In some I will update one field depending on another field in a table. So as much as I will use CRUD functionality for my app, some CRUD methods will be so small and so specific that the number of actions that I will perform in my database will be a lot but small. I didnt want to write each and every script and the available PHP generators whilst superior were not giving me what I wanted. I started thinking. A while later, I conceptualized, developed and created Php.Show, a RAD tool that I believe I will use over and over again in all cases where I will develop apps that will use PHP, whether Desktop or Mobile with MySQL.
Some silly assumptions: You are able to create databases using MySQL. You are familiar with PHP and writing its scripts. You can upload PHP files to a webserver. You can debug PHP scripts and are able to call php scripts. You have a know-how of JSON.
Background
This article will delve in the output of Php.Show and a follow up article will detail how I developed Php.Show with B4J (Basic 4 Java) a free RAD tool to create Java apps using VB like syntax from Anywhere Software.
PHP.Show generates PHP source code, i.e. scripts that are for CRUD functionality for your MySQL database. One is able to create a table, its fields (and properties) and this generates INSERT, UPDATE, DELETE, SELECT statements for your specified table in one php file for you to upload to your web server and call it within your developed app.
Php.Show is a Java app developed using vb like syntax. For me that’s also a new sphere and I will touch into that in another article. The advantage of having such a tool is the elimination of the tedious tasks of having to write the same php scripts over and over again for different tables in your database. Added to that is also the functionality to create your own small CRUD scripts, ability to select the fields for those particular actions to perform. That was the smart functionality I wanted mostly.
When one starts the application, Figure 1 appears, I will explain the various sections in detail. With this we will also look into the generated php script. This will also give you an idea in terms of how CRUD php functionality is generated. Thus whilst you are provided an opportunity to use a RAD tool to generate PHP scripts, you learn also how to use PHP. You can however get a wealth of information from W3School about this, here.
Figure 1
As you can see above, there is a section to specify the database connection, create a table and create a field and then other actions.
Selecting Help > About, gives one an idea of what Php Show.
To generate the php script file from your selected mysql table, you select File > Compile. Let’s create a new php file.
Step 1: MySQL database connection
To do this, we have first to specify the database connection that our MySQL php file will connect to. Figure 2 details this.
Figure 2
Type in the Host Computer, username, password and database. This gets translated into the following php script on compilation, using the details you specified in this section
Step 2: Create the table
Figure 3
Figure 3 above has two sections, a listview to list all created tables and also a section to create a new table, save it, delete it and also actions.
Create a new table
1. Click on New icon in Table Properties
2. Type in table name
3. Click Save
Update table name
1. Select table name from the list
2. Update the table name
3. Click Save
Delete a table
1. Select table name from the list
2. Click Delete
Figure 4: Confirm Table Delete
4. Confirm Delete (you cannot undo this action)
Step 3: Create fields for the table
By default, when creating a table, the assumed actions are CRUD related using the primary key field that you specify for the table. This primary key field is assumed as an incremental field. For this process to be completed, each field that will be part of the php script should be specified.
1. Ensure that the table to add the field to has been selected and its name shows on Table Name
2. In the Fields Properties, select New icon
3. Type in the field name.
4. Select the Field Type (currently there is String and Int)
5. Primary: check this if this field is the primary key. When primary, this field will be the one used for Delete and Update statements in php scripts.
6. Get: check this if you want to generate a SELECT statement using this field. By default, a “get” action is created with the primary field however if you want another field to be used in a SELECT statement, you can also check this. For example, let’s say you have a table called Contacts, it has fields, id, firstname, lastname, age. The id field is the primary key, you will select Primary in this case. If for example you also want to run a select statement with lastname = ?, when creating the lastname field, you can then check Get to tell the generator that we will also run a query to fetch all surnames with ?.
7. Sort: if you want your SELECT statements to return sorted results using some field, you can check the field.
8. Click Save. This updates the field listview.
Update a field
1. Select the field from ths field listing
2. Change the field name or type or other details like Primary, Get and Sort
3. Click Save
Delete a field
1. Select the field from the listing
2. Click Delete
3. Confirm Field Delete (this action cannot be undone)
Step 4: Compile the PHP script file
1. Select the table to process from the table list
2. Click File > Compile.
The php script file will be generated for you and saved. Let’s create a contacts table to show how this works for more clarity.
Example: Creating the Contacts php script.
1. I will use a fictitions database. So create your MySQL properties like this.
2. Add a contacts table. On Table Properties, click New, type “contacts” on table name, click Save
3. Add fields, id, firstname, lastname, telephone like this. NB: each time click New to create a field.
Let me explain what we just did.
1. id: We created an id field. This is the primary field thus checking primary. It’s an auto-increment field thus the field type Int.
2. firstname: We created a firstname field. We want to also get records with a firstname thus checked Get.
3. lastname: We created a lastname field. We want to sort the records by lastname, thus checked Sort
NB: Each time you add a new field, click New on field properties and when done, click Save. It’s always better to ensure that your field names do not have spaces.
For now we are done, Click the contacts table in the list, Click File > Compile and let’s look at the generated php script. Remember, the purpose of this tool is to generate your CRUD scripts. So for the specified table, we will have SELECT, INSERT, DELETE and UPDATE statements for our contacts table for the fields created. Let’s explore this after you confirm that you want to generate the script file.
MySQL database backend contacts table
This is just an example contacts table in terms of how it will look like in your mysql backend database sitting on your webserver. Note that the id field is set to auto, meaning it will auto increment by itself. So what we have done above with Php.Show is to create the same structure but for our app. Perhaps in future versions of this RAD tool I will add functionality to read the database automatically, we will see about that.
Please note that creating this database table structure with PHP.Show does not create your table for you in the backend database.
Output: PHP script file
After the compilation, the generated file will be opened for you. I use NotePad++ for my coding at times, so the file will be opened with that in my case. Let me jump ahead and just explain how you will call this file to execute from your web server.
As you will note, the generated file will be contacts.php. This means that all database operations in relation to this will be will stored here. The main determinant of what action to perform is maintained by specifying an action variable in your call. The switch statement within the php script file picks up on this action and then performs the various actions needed.
Calling Contacts.php CRUD functionality
1. Creating a contact (The INSERT SQL statement)
Call:
/contacts.php?action=create?firstname=Anele&lastname=Mbanga&telephone=01123567989
Script: (this uses a switch method)
When you run the call on your webserver, the script to create will be executed. I have defined variables within the script based on the field names you created. First this gets each variable of the querystring passed to the server and then runs an INSERT into statement to feed the contacts table with the information you specified. Becuase the id field is managed by the MySQL database, it is not passed as a variable on our call.
Database:
2. Reading a contact (The SELECT SQL statement)
To read a record from the database, the SELECT SQL statement is used passing it a where clause that will indicate the records that will meet your criteria. In this instance, we are retrieveing a single record by id.
Call:
/contacts.php?action=read&id=1
Script:
case "read":
// get a single record from the table using the primary key
$id = $_GET["id"];
$sql = "SELECT * FROM contacts";
$sql = $sql . " WHERE id = $id";
$sql = $sql . " ORDER BY lastname";
$result = $conn->query($sql);
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
print json_encode($rows);
break;
This will call the method to get the contact record specified by the id and return this as a JSON string. You can also do another call to get all records as will be depicted below. Did you notice the ORDER BY Clause above? Remember, when we created the lastname field, we checked Sort to tell the app that whatever records are returned should be sorted by lastname. You can select multiple fields to be included in the sort. Their sequence will depend on how they are added in the app.
3. Update a contact (The UPDATE SQL statement)
To update an existing contact record, we need to know the primary key field value. From the database screen above, this has been created as id=1 in our backend. The generated php script by Php.Show assumes that when you do an update, you update all fields. I will show you how to update specific fields with the Actions sections later on.
Call:
/contacts.php?action=update&id=1&firstname=Anele%20Mash%lastname=Mbanga&telephone=01123567989
Script: (this uses a switch method)
//process the actions
switch ($action)
{
case "update":
// update the table record using the primary key
$id = $_GET["id"];
$firstname = $_GET["firstname"];
$lastname = $_GET["lastname"];
$telephone = $_GET["telephone"];
$sql = "UPDATE contacts SET ";
$sql = $sql . "firstname='$firstname',";
$sql = $sql . "lastname='$lastname',";
$sql = $sql . "telephone='$telephone'";
$sql = $sql . " WHERE id = $id";
$result = $conn->query($sql);
break;
Database:
As you have noted, in essense, this code only updated the first name to be ‘Anele Mash’. You might be wondering why pass a complete querystring whilst only updating the firstname? Remember, these scripts are for CRUD functionality where your user interface will read from the database and display on screen controls and then read the same screen controls and update the server. Note the single quotes around the string variable fields.
4. Deleting a contact (The DELETE SQL statement)
The last section for the Create-Read-Update-Delete basic database functionality is the delete method. As the id field is the field specified as primary, we will use that to delete the record from the contacts table.
Call:
/contacts.php?action=delete&id=1
Script: (this uses a switch method)
case "delete":
// delete a record from the table using the primary key
$id = $_GET["id"];
$sql = "DELETE FROM contacts";
$sql = $sql . " WHERE id = $id";
$result = $conn->query($sql);
break;
The script above gets the id from the passed querystring and then executes a delete statement to remove the record from the table. From now on we will look at other methods we added to retrieve records from the database.
5. Read all contacts
Cal
/contacts.php?action=readall
Script:
case "readall":
// get all records from the table with a sort order if specified
$sql = "SELECT * FROM contacts";
$sql = $sql . " ORDER BY lastname";
$result = $conn->query($sql);
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
print json_encode($rows);
break;
NB: For small databases with small tables this might work well as it returns all records in the database table with a single call. For very large databases, this might prove challenging on webservers with limited resources and multiple users with large tables. In such cases, you can echo back each record to the user individually.
6. Get contact by firstname
But I also wanted to return contact records based on the firstname, thus the Get check when I specified the properties of that field.
Let’s look at what that did below. In cases where you want to return records with a particular value on a field, you could also mark your field with a Get. we did that for the firstname.
Call
/contacts.php?action=getfirstname&firstname=Anele
Script:
case "getfirstname":
// get records by firstname with sort order if specified
$firstname = $_GET["firstname"];
$sql = "SELECT * FROM contacts";
$sql = $sql . " WHERE firstname = '$firstname'";
$sql = $sql . " ORDER BY lastname";
$result = $conn->query($sql);
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
print json_encode($rows);
break;
This call will select all contacts with firstname Anele from the database table.
Actions
The following section will discuss the issue of the flexible queries called Actions. The sections above did not provide any flexibility in creating your php scripts as they are cut in stone in terms of what they will do depending on the fields you have created. Actions enable one to do that perfectly. For example, you just want to select or update or delete a contact based on the telephone number or any field for that matter.
To create such scripts, one creates an action.
1. Actions Step 1: Create an action
1. Select the table name to add / update / delete action for
2. On the Table Properties, click Actions. The actions screen appears.
3. Click New and type in the Action Name, e.g. telephone
4. Specify the Primary Key (for example you want to select/update/delete your contacts by Telephone, so specify telephone here. This is the flexibility we are talking about.
5. There are methods that you can select to be created, in this case you can check SELECT, INSERT,DELETE and UPDATE.
6. On field names, select firstname and click >, lastname then >, telephone then >.
7. Click Save. This should be like this..
8. Select telephone on Other Actions, click Preview button, this will generate the php scripts for you for this action. Let’s see what this has done.
Select contacts by telephone
case "select_telephone":
$telephone = $_GET["telephone"];
$sql = "SELECT firstname,lastname,telephone FROM contacts";
$sql = . " WHERE telephone = $telephone";
$result = $conn->query($sql);
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
print json_encode($rows);
break;
From your call e.g. /contacts.php?action=select_telephone&telephone=0123456789
This will return all records with that telephone number. This is a result of checking SELECT when we were creating our action.
Delete contacts by telephone
case "delete_telephone":
$telephone = $_GET["telephone"];
$sql = "DELETE FROM contacts";
$sql = . " WHERE telephone = $telephone";
$result = $conn->query($sql);
break;
As you can see above, when this script is called via /contacts.php?action=delete_telephone&telephone=0123456789, all contacts with this telephone numeber will be deleted.
Insert contact based on telephone action
This statement is the same as discussed earlier on, however, should you have not included all the fields in the action, only those fields will be included in this insert script.
Update contact by telephone
case "update_telephone":
$firstname = $_GET["firstname"];
$lastname = $_GET["lastname"];
$telephone = $_GET["telephone"];
$sql = "UPDATE contacts SET ";
$sql = . "firstname=$firstname,";
$sql = . "lastname=$lastname,";
$sql = . "telephone=$telephone";
$sql = . " WHERE telephone = $telephone";
$result = $conn->query($sql);
break;
When we were creating the action, we also checked the UPDATE. This meant that for our action, we will also have an update script based on the telephone number.
Recompiling the table script again will include all the actions you created in one file. That’s all folks, hope you enjoyed this and it provided some green lights about PHP MySQL intergration. In my upcoming article I will deal with how this RAD tool was created for us to peek at the source code making this possible.
Points of Interest
Whilst there are various PHP generators out there, I wanted something easy to work with and also with the flexibility I wanted to meet my needs in helping me with my mobile app development using MySQL as a backend. There I had tables with relationships however these relationships were small to an extent of just updating one field on another table based on another. I wanted functionality for re-use and the ability to create code repetitively without having to type scripts but just generate them.
RAD tools make the life of software developers easy and thus the ability to work smart as at times we are faced with having to perform repetitive tasks over and over again. Faced with such challenges, these tools provide methods for developers to be able to work quickly meet strict deadlines that at times would not be met should normal traditional methods of doing things be followed.
There is a vast array of RAD tools in the net doing different things. This is my second RAD tool being discussed here in CodeProject. The first one is here, to create CRUD JQuery Mobile Apps quickly, it’s called JQM.Show. Now that I have just learned PHP and MySQL intergration, that tool will be updated to speak to MySQL backends. I’m so excited right now.
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
