In this tutorial I will aim to show you how simple is making a small application in WhizBase, how to add, remove and update data in the DB. I will make a small address book application where you can add, browse, update and remove addresses.
I will use a MS Access DB (.mdb file), but you can apply this tutorial for any DB you want, there is no significant difference, and the concept is the same.
We will make one default page which will show us all the records sorted alphabetically, then two files for update, one for delete and two for addition.
I will not go through the SQL or the Access file creation, I will just describe what we need in the DB. We will need one table named contacts . It will have the following fields:
Whatever DB type you use, you will need these fields, so create the table and let s begin. I have created my contacts.mdb access DB file.
This file will connect to the DB and select all the contacts sorted alphabetically by first name:
In our first file we connect to the DB file contacts.mdb, which is located in the same folder as default.wbsp. In WhizBase we give instructions for DB connection at the beginning, so we first make [FormFields] section which is the start of the file header. We specify some parameters, WB_BaseName is the path of the DB, WB_Command will be Q as for Query. WB_maxrec will be 20 records, we want our address book to paginate the records in 20 rows each page. And WB_Order will be Firstname, because we want to sort the report alphabetically by Firstname.
<!--WB_BeginTemplate--> will mark the end of the file header. We now make the design of our address book. <!--WB_BeginDetail--> and <!--WB_EndDetail--> are used to loop through the query results. WhizBase brings you the data automatically, so you do not need to assign any variables or arrays. You need just to put the data placeholders in the code.
wbf[fieldname] is the placeholder, as the system loops through the query result, for each row we take the value of the field name we want to show, in our case we are showing the id, firstname, lastname, tel and insertiondate.
We made three links, one for edit, another two for delete and add commands. Take a look at the edit and delete links, you will see that we are giving a wbf_id parameter. We will need that later.
To add records we need a plain HTML form, so we will not use any WhizBase in this file, but take a look at the input names.
WhizBase picks up all parameters sent by POST or GET method and put them in one server array. Some parameters have a special meaning for WhizBase, any parameter beginning with wbf_ will be treated as a DB entry, it uses these parameters as values for comparison for Query and Delete commands (WHERE clause in SELECT statement), and as values to be entered into the table for Update and Add commands. This simplifies working with DB's, no need to write complex and long queries, WhizBase makes it automatically.
As we want to add 3 information in the DB - firstname, lastname and telephone, we use three parameters wbf_firstname, wbf_lastname and wbf_tel as inputs. Fields ID and insertiondate will be added automatically.
Save this file as add.htm, and let's move to file addc.wbsp that does all the adding work.
Every server side scripting language can receive inputs from visitors and process data from the DB. That makes them better than client side scripting languages. WhizBase as a server side scripting language also receive inputs, and processes them.
In add.htm we collected 3 parameters from visitor and sent them using post method. All the data we want to add to database have wbf_ prefix in the name, so WhizBase will process and filter them automatically. Let us look at this code and see how WhizBase make an insert to the DB:
In this file we have connected to the DB by wb_basename, selected the table contacts with wb_rcdset, and gave an addition command by setting wb_command = A where "A" stands for Addition.
WhizBase took the 3 parameters with prefix wbf_ and added them in the table automatically, but we have two more fields in the contacts table - "id" and "InsertionDate". The "id" is set in the table as autonumber so it will be set automatically, but "InsertionDate" we must add somehow, and we do not want visitor to change it. We could add it as a fourth parameter, but that would not be very safe, because visitors might find the ways to change it. So, since we want to add it on server-side safely, we use wb_forced variable, which will force any parameter we want (in this case wbf_InsertionDate) and ignore the value(s) sent by form. 6/19/2013 is used to pass the system date and time.
Deleting records in WhizBase is very easy and secure, you cannot delete all records at once, and you cannot inject the query or hack it. And it is very similar to addition.
Do you remember the parameter we sent in the delete link, "wbf_id" we will need it now. To delete a record by id we need that id so we submit it automatically by wbf_id.
There are three new commands in this code, let go through them. "wb_command=d" is telling the server to execute delete command where "D" stands for delete. "wb_uid" is the command where we specify the name of the unique field (different value for every record) that we will use to prevent deletion of more than one record at the time. In our case it is field "ID", and without this, command delete is not safe enough. Finally "wb_redirect" is a simple redirect command where we tell the server to redirect visitor to the location we want. In our case we want the application to go back to the address book.
When we want to edit a record we first need to show the values we have in the DB for specific record, so we can edit it and then save it. So, we have two steps with the DB here, the first is to select the record we want to edit from the DB, after editing it we need to save the changes in the DB.
This is very simple code, we talked about all its elements before, just notice that we did not use wb_maxrec because we always edit one record at the time, and we also did not use wb_order for the same reason.
For editing the record we will need to send its "id" so we use <input type='hidden' name='wbf_id' value='9' />.
WhizBase selected our record automatically by the id we provided in the link with wbf_id.
The last file we will do in this article is editc.wbsp where we will save the record. In the previous file we have sent all needed parameters by POST with a prefix "wbf_".
In this file we have just one new command "WB_Command=U" which tells the server to make an Update. Note that for Update command we must set the WB_UID.
With this we have finished our simple address book application in WhizBase. As you can see, in WhizBase it is very easy to work with the DB, and you do not need to know any SQL or make any queries.