Home Tutorial Mods Configurator Bookstore Links Email JPDeni

Plan your database

Before you get started, think about what information you want to keep in your database. Consider if you want to have the date a record is added, if users will "own" records, what type of key value you want to use.

Now would probably be the best time to talk about the key value. Each record must have a unique key, which is one field that holds a unique value for each record. There can be just one field that is designated as a key.

The most common type of key value is a counter. Each time a record is added, the counter updates to the next number. It is really the easiest key to use. Note that the counter can not be reliably used to tell you how many records are in your database. It is merely a way to be certain of having a unique value for every record.

Sometimes you will want to use something other than a counter for your key. For example, a database of books may have the ISBN number for each book as the key. Or you may want to guarantee that each user has only one record in the database, in which case you could set the key to be equal to the userid field. Take time to think about your database structure. It is possible to add or delete fields later on, but the further you go setting up your database, the more work it is to make changes and the more likely it is that you will make a mistake.

Create your field definitions

At this point, you have a choice. You can continue on with making your own .cfg file, or you can have the DBMan Configurator do it for you. The Configurator will take you through the next several steps in the tutorial, including

• Create your own configuration
• Set permissions
• Create your own forms and displays

Or you can choose to create your own configuration by hand.

In the .cfg file, you will see the format for the field definitions. The easiest thing to do is to follow the patterns in the demo file. Here's a little more information on each element of the definition:

Field name

You can choose pretty much any name for your fields. Keep the following points in mind:

• You cannot have two fields with the same name.
• You will be using the field names in your tables and displays, so you probably will not want them to be too long.
• Do not use any quotation marks within your field names. This includes the use of a ' for an apostrophe.
• You may use spaces in your field names, but you must enclose the whole name  in single quotes, as in 'Field Name'.
• It is better not to have a space in the name of the key field.

Position

These are numbers from 0 to whatever number you need for all of your fields. Important!! Do not skip any numbers and do not repeat any numbers!!

Field Type

This is mostly used for sorting purposes. The options are

• alpha -- Sorts by ASCII code.
• numer -- Sorts numerically -- Note the spelling!!
• date -- Sorts by date

Note that no checking is done within the script to assure that the data entered in the field matches the field type.

Form length

This is the size of the input field that is generated when the autogenerate feature is active.

• For text fields -- set this to the length of the field you want
• For textarea fields -- use the format of number of columns x number of rows
• For radio, checkbox and select fields -- set this to 0

Maximum length

This prevents users from entering large amounts of data and overpowering your database file. The script will check to assure that entries do not exceed the number you set here.

Not null

Allows you to set which fields are required and which are not.

• 1 means the field is required. If a user submits a record with no data in this field, an error message will be generated
• 0 means the field is optional

Default

If you would like any text to appear in a field when the form is generated, enter it here. Also, if you would like an option automatically selected in a select field or radio field, enter the selection here. Note that it must exactly match the spelling in the list of options for that field (which we'll get to in a minute).

Valid Expression

You can set a "regular expression" for the script to check to see if the data is in the correct format. Regular expressions can be rather complicated, but here are a few that people have found useful.

• '^http://' -- This is for URLs. It requires that the entry starts with http://
• '.+\@.+\..+' -- This is for email addresses. It requires that the entry has the pattern something@something.something. Note that the pattern only needs to be somewhere within the field in order to be recognized as a match.

Set the key field

Just below the field definitions, find

     $db_key = 'ID';

Enter the name of the field that you intend to use as the key field. Remember that the field name must be entered exactly as it is in the field definition. The case of the letters counts, so "ID" is not the same as "id" or "Id."

The next part the default.cfg file determines whether the script will keep track of the key.

     $db_key_track = 1;

If you are using a counter for the key, leave it set to 1. If you are not, change the 1 to 0.

Set autogenerate

For the time being, it is best that you use the autogenerate feature to create your forms and record displays. We'll get to creating your own custom forms and displays later, but right now we're more interested in making sure your field definitions are correct.

A few lines down from the $db_key_track line, find

     $db_auto_generate = 0';

Change the 0 to 1.

Define option fields

If you want to include any select fields, radio fields or checkboxes, define the options that you want users to be able to choose from.

Just to be certain we're all using the same language, I've included samples of each of the different option fields below:

Radio field	Option1 Option2 Option3
Checkbox field	Option
Select field	--- Option1 Option2 Option3 Option4

For each field you entered a 0 as the form length, define the options for the field.

The format for entering the options for each field type is

Fieldname1 => 'Option1,Option2,Option3',
Fieldname2 => 'OptionA,OptionB,OptionC'

Keep in mind:

• All of the options for a field must be on the same line
• Do not put spaces between your options.

  Right:  FieldName => 'Option1,Option2,Option3'
  Wrong:  FieldName => 'Option1, Option2, 
                        Option3'
  

• The field name must be spelled exactly the same as in the definition list. Field names are case sensitive!
• If you have a space in your field name, enclose the field name in single quotes:

  'Field Name'
  

Insert your field defintion in the appropriate place in the default.cfg file:

Select fields

%db_select_fields    = ( 
    Fieldname1 => 'Option1,Option2,Option3',
    Fieldname2 => 'OptionA,OptionB,OptionC' 
);

Radio buttons

%db_radio_fields    = (
    Fieldname1 => 'Option1,Option2,Option3',
    Fieldname2 => 'OptionA,OptionB,OptionC'  
);

Checkbox fields

%db_checkbox_fields    = (
    Fieldname1 => 'Option',
    Fieldname2 => 'Option'
);

If you are going to use checkbox fields, you have a couple of options as to how to do it. It's all a matter of personal preference.

If you define your checkbox field as follows:

     %db_checkbox_fields    = ( Popular => 'Yes' );

The corresponding field on your form will look like this:

Popular: Yes

If, instead, you define your checkbox field as follows (and you define your own form later on):

     %db_checkbox_fields    = ( Popular => 'Popular' );

The corresponding field on your form can look like this:

Popular

The choice is yours, depending on whether you want to create your own form and how you want it to look.

Set the user id field

Scroll down almost to the bottom of default.cfg to find

     $auth_user_field    = 9;

If you included a field which will contain the user id of the person who added the record, enter the field number of that field in place of the 9. If you do not have a field for the user name, enter -1 in place of the 9.

It is very important that you set this field correctly. Do not think that just because you are not using an authorized user field you can ignore it. This is probably the most common error people make.

Save default.cfg.

Give a name to your database

Open html.pl and scroll down to the first line below the comments.

##########################################################
##                     HTML Globals                     ##
##########################################################
# Put any globals you like in here for your html pages.
$html_title  = 'Database Manager Demo';

Enter the name of your database in place of Database Manager Demo. Be aware of the fact that the quotation marks are the same as apostrophes. If you have an apostrophe in your title, you might have some real problems. For example, I could not just have

$html_title  = 'JPDeni's DBMan-ual';

The script would think that the quotation ended after the i. If you want to use an apostrophe, use double quotes instead of single quotes.

$html_title  = "JPDeni's DBMan-ual";

Save html.pl.

Prepare your files

• Open default.db. Delete everything in the file and then save it.
  (Alternatively, create a new, empty file and save it with the name default.db.)
• Open default.log. Delete everything in the file and then save it.
  (Alternatively, create a new, empty file and save it with the name default.log.)
• Open default.count. Replace the number in the file with a 0. Save the file.

Upload and test

Using the same procedure you used when you first uploaded the files, upload the new versions of

• default.cfg
• default.db
• default.log
• default.count
• html.pl

You shouldn't have to set the permissions again, but be absolutely certain that you again upload them in ASCII format.

Log on and try out your new configuration. Add some records, view them, modify, delete -- really put it through its paces to make sure it all works okay.

Go have yourself a nice cool drink and congratulate yourself for being a very clever person. You deserve it.