List Editor

From FMYiRCWiki

Freemed-YiRC - List Editor

1 Summary
2 List Editor Components
- 2.1 List Editor DB Tables
  - 2.1.1 List Editor DB Tables Diagram
  - 2.1.2 DB Tables Field Descriptions
- 2.2 List Editor PHP Code Files
3 Usage Example
- 3.1 Database Table Records
- 3.2 Programming Code

Summary

The Freemed-YiRC List Editor is a facility for Freemed-YiRC developers/programmers to deal with lists (both Reference and Linked) in a common and centralized manner.

[edit]

List Editor Components

The Freemed-YiRC List Editor is made up of two primary components:

The List Editor DB tables
The List Editor PHP Code Files

[edit]

List Editor DB Tables

In order for the Freemed-YiRC List Editor to be able to interface with lists (in the form of database tables) it must be instructed on their structure. This is where the List Editor DB tables come into play.

fy_listeditor_lists
- This table should contain one, and only one, record for a given list in which the Freemed-YiRC List Editor will interface with.
fy_listeditor_columns
- This table should contain one or more records for each entry in fy_listeditor_lists. This table should contain one record for each of the columns in the pertinent table which the Freemed-YiRC List Editor will be used to edit. For example, if a given list/table has three items/columns, then there should be three records in fy_listeditor_columns.
fy_listeditor_dropdownitems
- If any of the items/columns has a column_type of Drop-Down, then this table should contain a list of the drop-down items. One record per drop-down item per drop-down. i.e., if a given list has only one column defined as a drop-down, and that drop-down list has three items on it, then this table should contain three records for that list. However, if a given list has two columns/fields marked as drop-downs, and one drop-down has three items while the other has four, then there should be seven records for that list.

[edit]

List Editor DB Tables Diagram

[edit]

DB Tables Field Descriptions

This information is taken from the DB/mysql/makedb file. Consult that file as it may contain updated information.

[edit]

fy_listeditor

list_dbtable_name: The database table name. This is not shown to the user.
list_descr: A short descriptive name for this list. This is shown to the user.
list_info: OPTIONAL - If provided, any info in this field will be displayed to the user to provide custom help information while working with this list.
list_record_descr: A simple (one word, if possible) description to use when showing descriptive items such as "Edited X", "Added X", "Delete X?"... this is the "X" to be displayed to the user.
list_type: The list type. This is used to provide the user important information regarding guidelines on modifying data in this list.
- Reference: A reference table contains a simple list of items whose values are referenced by the item value itself. For example, given a list of fruit (Apple, Orange, Banana), if the item "Apple" is chosen, the value "Apple" itself is stored. This type of table should only be used in situations where the items on the list will not change values. Editing of existing items on this list should be kept to an absolute minimum. The benefit to a reference list is that database tables storing these values are easier to report on, as there is no need to do a database lookup to retrieve an item's actual value. A negative about this method is that if one were to change the value of "Apple" on this simple list to "Apples", this could create a situation where other database tables referencing this data will no longer offer "Apple" as a new item option, only "Apples". In addition, any attempts to report on all records using the new value of "Apples" will not return older records which previously stored the value "Apple".
- Linked: A linked table contains a list of items whose values are referenced not by the items value, but it's database record ID instead. For example, given a list of fruit (Apple, record 1; Orange, record 2; Banana, record 3), if "Apple" is chosen, the value "1" would be stored ("Apple's" record number). This means that any time this data needs to be reported on, a lookup would need to be performed to this list to reveal the value for record "1" (i.e., "Apple"). However, if the value of "Apple" were changed to "Apples", all existing records referencing this would not need to be edited/modified as a lookup to the list is needed regardless any time the record needs to be reported on. The negative here, again, is that any records making use of values in a linked list require lookups to determine the actual value of a given item.
list_viewrec_perm: The Security Database permission required to view items on this list. This is not shown to the user. This permission can not be left blank.
list_createrec_perm: The Security Database permission required to create new items on this list. This is not shown to the user. This may be left blank if you do not wish to allow creation of new records on this list.
list_editrec_perm: The Security Database permission required to edit existing items on this list. This is not shown to the user. This may be left blank if you do not wish to allow editing of records on this list.
list_deleterec_perm: The Security Database permission required to delete existing items on this list. This is not shown to the user. This may be left blank if you do not wish to allow deletion of records on this list.

[edit]

fy_listeditor_columns

list_dbtable_name: The database table name this column is linked to. This is not shown to the user.
column_order: A number (starting from 1) which indicates the numerical order this field(column) should appear in the list(table). This is not shown to the user.
column_sort_order: If this is set to greater than 0, this indicates the numerical order this field should be used to sort the records in this list. If this field is not to be used in sorting results, set to blank or 0. This is not shown to the user.
column_dbfield_name: The database field name of the field in question. The database is listed in the fy_listeditor_lists table in the list_dbtable_name field (fy_listeditor_lists.list_dbtable_name).
column_descr: A short descriptive name for this column. This is shown to the user.
column_info: OPTIONAL - If provided, any info in this field will be displayed to the user to provide custom help information while working with this column.
column_type: - The type of field.
- Input: Indicates this is a one-line data input field.
  - column_length should be used to indicate the length limit for the field. This is for use with DB field type char(#) - where # is the length (should match what is put in column_length). This should not be used for number-only input, use type "Number" or "Decimal" instead.
  - column_value_min and column_value_max can be used to indicate the minimum and maximum number of characters allowed in the field, although column_length will override column_value_max if column_length is smaller.
- Text: Indicates this is a multi-line data input field. There is no limit to the length. This is for use with DB field type TEXT.
  - If column_length is greater than 0.0, the integer portion (non decimal) will be used to mark the width of the text box (i.e., the number of columns). The decimal portion will be used to mark the height of the text box (if the decimal portion is greater than .00 (.01=1 row, .10=10 rows, etc...).
- Drop-Down: Indicates this is a drop-down selection field (HTML SELECT). The drop-down values to appear in this list should be added to the fy_listeditor_dropdownitems table.
  - column_length should appropriately match the DB field configuration.
  - column_value_min and column_value_max are not used with Drop-Down items.
- Number: Indicates this is an Integer number input field (i.e., no decimals, if decimals are wished, use the Decimal type instead).
  - column_length should be used to indicate the length limit for the field.
  - column_value_min can be used to indicate the smallest number allowed. If this is not specified it will default to zero. If negative numbers should be allowed, this must be defined as the largest(smallest?) negative number allowed.
  - column_value_max can be used to indicate the largest (positive) number allowed. If this is not specified no maximum restrictions will be placed on the number.
- Decimal: Indicates this is a decimal number input field.
  - column_length should be used to indicate the length limit for the field (including decimal precision (.01=1 decimal place, .10=10 decimal places).
  - column_value_min can be used to indicate the smallest number allowed. If this is not specified it will default to zero. If negative numbers should be allowed, this must be defined as the largest(smallest?) negative number allowed.
  - column_value_max can be used to indicate the largest number allowed. If this is not specified no maximum restrictions will be placed on the number.
- Date: Indicates this is a date field.
  - column_value_min_harddate and/or column_value_max_harddate can be used to specify a "hard" date for the date range... i.e., if column_value_min_harddate is set to 6/30/2010, the lower end of the date range will be set to that.
    - If a "hard" date is specified, that takes precedence over column_value_min or column_value_max.
  - column_value_min and column_value_max can be used in one of two ways.
    - Firstly, if one of these is set to a 9 digit positive or negative number starting with the number 9, then the 9 will be dropped (leaving the remaining 8 digits), and those 8 digits will reflect a number of years to either add or subtract from the current year to form the lower end of the date range (if column_value_min is set using this method) and/or the higher end of the date range (if column_value_max is set using this method). If the current year is 2011 and column_value_min is -900000005 (negative), then the lower end of the date range will be set to 1/1/2006 (2006=2011-5). Likewise, if column_value_min is 900000005 (positive), the lower end of the date range will be set to 1/1/2016 (2016=2011+5). If column_value_max is set using this method, the higher end of the date range will be set to 12/31/X.
    - The second method for column_value_min and/or column_value_max is setting it to a positive or negative number which is not 9 digits and does not start with the number 9. Using this method, the current date will be subtracted by this number of days (if it's a negative number) or added by this number of days (if it's a positive number) to come up with the lower end of the date range (if column_value_min is set using this method) and/or higher end of the date range (if column_value_max is set using this method). If the current date is 2/21/2011 and column_value_min is set to 5 (positive), then the lower end of the date range will be 2/26/2011 (2/21/2011 + 5 days). Likewise, if column_value_min were set to -5 (negative), then the lower end of the date range will be 2/16/2011 (2/21/2011 - 5 days). If column_value_min is set to zero or BLANK, then the lower end of the date range will default to January 1st of the previous year. Likewise, if column_value_max is set to zero or BLANK, then the higher end of the date range will default to December 31st of the following year. If column_value_min is set to 0.01 (positive), then the lower end of the date range will be the current date during data input (i.e., no past dates allowed). Likewise, If column_value_max is set to 0.01 (positive), then the higher end of the date range will be the current date during data input (i.e., no future dates allowed).
- Time: Indicates this is a time field.
column_value_min: - See notes for field column_type for usage info.
column_value_max: - See notes for field column_type for usage info.
column_not_required: - If set to "X", this indicates that this field may be left blank. For drop-down, date, and time type fields this will automatically add a blank entry, this blank entry will be selected by default and displayed to the user when a new list item is being created.

[edit]

fy_listeditor_dropdownitems

list_dbtable_name: The database table name which the column this drop-down is linked to. This is not shown to the user.
column_dbfield_name: The database field name of the field in question. The database is listed in the fy_listeditor_lists table in the list_dbtable_name field (fy_listeditor_lists.list_dbtable_name).
dropdown_descr: The description/display text to show for this descr/value pair. This is what will be displayed in a drop-down selection list, i.e., this is what the user will see.
dropdown_value: The value to be returned for this descr/value pair. This is what will be returned and/or stored in a database or used for a report. i.e., the user will NOT see this, unless this value matches the description. If you do not wish the value to differ from the visible description, then make the dropdown_value contents equal that of dropdown_descr (i.e., both dropdown_value and dropdown_descr should be the same).
dropdown_order: OPTIONAL. This may be used to determine the order of the options presented in the drop-down. By default the list editor will order by dropdown_order then dropdown_descr. If dropdown_order is left blank for all drop-down entries for this list/column, then dropdown_descr will be used.

[edit]

List Editor PHP Code Files

The Freemed-YiRC List Editor PHP code files are used to provide an interface to the end-user for dealing with the lists which are set up in the List Editor DB tables. The only thing the developer has to do to provide the end-user with this functionality is to provide a link to listeditor.php with arguments pointing to the specific list in question (more about this in the Usage Example section).

The PHP Code Files are as follows:

listeditor.php
listeditor_edit.php
listeditor_delete.php

NOTE: The developer does not need to make any modifications to these PHP code files to use the List Editor, the developer need only create records in the above described DB tables and provide a link to listeditor.php with arguments pointing to the specific list to view/edit.

[edit]

Usage Example

For this example, we're going to be using the example module View System Time which is documented in the Module Programming Guide article. Please follow this guide, implement the module, and give yourself access to the module.

In the above module we created a link to a settings menu. On this menu we have one item, Format, which is a simple drop-down item. What we're going to do in this example is replace the code for editing that setting with a simple link to the Freemed-YiRC List Editor. After all, the settings for the View System Time module is simply a one-item list. If we had used the Freemed-YiRC List Editor in the first place, we wouldn't have had to create the clock_settings.php code file (as they say, hindsight is 20/20).

[edit]

Database Table Records

The first thing we're going to do is make the appropriate entries into the List Editor DB tables:

INSERT INTO fy_listeditor_lists (list_dbtable_name, list_descr, list_info, list_record_descr, list_type, list_viewrec_perm, list_createrec_perm, list_editrec_perm, list_deleterec_perm) values ('fy_clock_settings', 'Clock Settings', 'This is a reference list of settings for use with the \"View System Time\" example module.', 'Setting', 'Reference', 'clock', '', 'clock_edit', '');

INSERT INTO fy_listeditor_columns (list_dbtable_name, column_order, column_sort_order, column_dbfield_name, column_descr, column_info, column_type, column_length, column_value_min, column_value_min_harddate, column_value_max, column_value_max_harddate, column_not_required) VALUES ('fy_clock_settings', 1, 1, 'clock_time_type', 'Format', 'In what format would you like to view the system time?', 'Drop-Down', '8', '', NULL, '', NULL, '');

INSERT INTO fy_listeditor_dropdownitems (list_dbtable_name, column_dbfield_name, dropdown_descr, dropdown_value, dropdown_order) VALUES ('fy_clock_settings', 'clock_time_type', 'AM/PM', 'standard', '1');
INSERT INTO fy_listeditor_dropdownitems (list_dbtable_name, column_dbfield_name, dropdown_descr, dropdown_value, dropdown_order) VALUES ('fy_clock_settings', 'clock_time_type', '24-Hour Military Format', 'military', '2');

Let's break these down one-by-one:

INSERT INTO fy_listeditor_lists (list_dbtable_name, list_descr, list_info, list_record_descr, list_type, list_viewrec_perm, list_createrec_perm, list_editrec_perm, list_deleterec_perm) values ('fy_clock_settings', 'Clock Settings', 'This is a reference list of settings for use with the \"View System Time\" example module.', 'Setting', 'Reference', 'clock_edit', '', 'clock_edit', '');

What are we doing here?

Setting list_dbtable_name to fy_clock_settings, since this is our target list/table.
Setting list_descr to Clock Settings, this is a short, simple description of the list we're working with.
Setting list_info to This is a reference list of settings for use with the \"View System Time\" example module.. This is a longer description which will show up on the List Editor screen, to give the end-user an idea of what they're working with. This can be very long, as it is not limited in length. If the particular list we're working with is complicated, then this field can contain detailed information to help guide the user.
Setting list_record_descr to Setting. This is a very short, simply, description of what to call each item. For the purpose of this example, each item (although there is only one), is a setting for the View System Time example module.
Setting list_type to Reference. This indicates the pertinent list is a Reference List (as opposed to a Linked list). For more information on this, please see the lists article.
Setting list_viewrec_perm and list_editrec_perm to clock_edit. In the View System Time example module, only a user with the clock_edit permission can access and edit the settings, so that's what we're telling the List Editor to restrict users to. We're leaving list_createrec_perm blank because we don't want the user to be able to create multiple settings records, there should only be one for this example. If there were more than one, then the module may get confused as to which record it should use! For the same reason we're leaving list_deleterec_perm blank, as we don't want the end-user to be able to delete settings records. In the module example we already created one setting record up in the SQL data. We should only have one at all times, so we do not want the end-user to be able to create new records, nor delete existing records.

Next up we have:

INSERT INTO fy_listeditor_columns (list_dbtable_name, column_order, column_sort_order, column_dbfield_name, column_descr, column_info, column_type, column_length, column_value_min, column_value_min_harddate, column_value_max, column_value_max_harddate, column_not_required) VALUES ('fy_clock_settings', 1, 1, 'clock_time_type', 'Format', 'In what format would you like to view the system time?', 'Drop-Down', '8', '', NULL, '', NULL, '');

Since the fy_clock_settings only has one column/item (aside from the ID column, which is automatically assigned by the system upon record creation) we only need this one entry.

What are we doing here?

Setting list_dbtable_name to fy_clock_settings, since this is our target list/table. This matches what we did in the fy_listeditor table, this is important! This is how the Freemed-YiRC List Editor knows that this item/column record is linked to the pertinent list/table.
Setting column_order to 1. Since we only have one setting, it gets the number 1. If we had multiple settings, this would be used to indicate in what order the settings appear to the user.
Setting column_sort_order to 1. Again, since we only have one setting, this matters little in this particular example. However, if we were working with a more complicated list that had more than one item/column and more than one record, then we might want to sort the records a little differently than how we're showing the order of the columns to the user.
Setting column_dbfield_name to clock_time_type. This is the name of the database table field that we created in the fy_clock_settings table in the View System Time example module.
Setting column_descr to Format. This is a short, simple description for this specific item/column. For the purposes of the View System Time example module, this setting relates to the clock display format.
Setting column_info to In what format would you like to view the system time?. This is optional, but can be specified to provide the user with some instructions or examples specific to this item/field.
Setting column_type to Drop-Down. We wish to provide the user with a drop-down list of options for this setting. That's what we're specifying here.
Setting column_length to 8. In our module example, we created the clock_time_type as a char(8) field, which means this field is 8 characters long. So we're letting the List Editor know that any selections should not be more than 8 characters long.
Leaving the column_value_min, column_value_min_harddate, column_value_max, and column_value_max_harddate fields blank, as a Drop-Down column doesn't make use of these.
Leaving the column_not_required field blank. Since this field (clock_time_type) IS required to have data in it (not be blank), we are NOT putting an X in column_not_required. This alerts the List Editor that if the end-user tries to leave this field blank, they will get an error and not be able to save their work until this is fixed.

Finally, we're going to create the drop-down item values for the clock_time_type setting. In the View System Time example module, we had hard-coded these values into clock_settings.php. Here, instead, we're putting them into a database table.

INSERT INTO fy_listeditor_dropdownitems (list_dbtable_name, column_dbfield_name, dropdown_descr, dropdown_value, dropdown_order) VALUES ('fy_clock_settings', 'clock_time_type', 'AM/PM', 'standard', '1');
INSERT INTO fy_listeditor_dropdownitems (list_dbtable_name, column_dbfield_name, dropdown_descr, dropdown_value, dropdown_order) VALUES ('fy_clock_settings', 'clock_time_type', '24-Hour Military Format', 'military', '2');

What are we doing here?

In both these entries we're setting list_dbtable_name to fy_clock_settings. We did the same thing in the fy_listeditor_columns and fy_listeditor_lists tables. This is necessary to link all these records in these three tables together.
In both these entries we're setting column_dbfield_name to clock_time_type. This tells the Freemed-YiRC List Editor that these two records (drop-down item values) are to be used for the clock_time_type item/column.
Setting dropdown_descr to AM/PM (for the first record) and 24-Hour Military Format (for the second record). This is what the end-user will see on the screen when they browse the drop-down items.
Setting dropdown_value to standard and military. This is what will be inserted into the fy_clock_settings database table field clock_time_type. One of these two values is what clock.php expects to find for settings values.
Setting dropdown_order so that AM/PM shows up first (1) and 24-Hour Militiary Format shows up second (2).

[edit]

Programming Code

Now that the database records have been created, all we need to do now is to provide the end-user with a way to access the settings using the Freemed-YiRC List Editor.

To do so, we want to replace the following code in clock.php:

if ($perm_edit=="X")
{
        fy2_html_winblank();
        fy2_html_winlink("CENTER","","clock_settings.php", "Edit Settings");
}

With:

if ($perm_edit=="X")
{
        fy2_html_winblank();
        fy2_html_winlink("CENTER","","clock_settings.php", "Edit Settings");
        fy2_html_winlink("CENTER","","listeditor.php?fy_table=fy_clock_settings&fy_back=clock.php", "Edit Settings (Using List Editor)");
}

All we're doing is adding another link. We're labeling this one as Edit Settings (Using List Editor) so you can visually distinguish between the different links.

We're passing the fy_table variable along to listeditor.php with the name of the database table/list in question (fy_clock_settings). This matches the value we used in all three of our database tables for the column list_dbtable_name. The only other variable we're passing is fy_back. This simply tells the List Editor where to return the end-user after they've exited the List Editor. Here, it's simply back to clock.php.

If, after testing this out, you find that the new method (using the List Editor) will work, then you can safely remove the first link, and the clock_settings.php file as well!

NOTE: If you are returning the end-user back to a script which requires variables, you must NOT separate these variables with the ampersand character ("&")! If you do, those variables will instead be passed to the List Editor and will not be returned correctly. Instead, replace all instances of the ampersand character ("&") with a carrot character ("^"). This will preserve the contents of your fy_back variable. For example, if we wanted to pass two variables named var1 and var2 to clock.php when the end-user exits the List Editor, then we'd use the following link to access the List Editor:

listeditor.php?fy_table=fy_clock_settings&fy_back=clock.php?var1=value1^var2=value2

Retrieved from "http://www.freemed-yirc.com/freemed-yirc/wiki/index.php/List_Editor"

Categories: Documentation | Terms

List Editor

From FMYiRCWiki

Contents

Summary

List Editor Components

List Editor DB Tables

List Editor DB Tables Diagram

DB Tables Field Descriptions

fy_listeditor

fy_listeditor_columns

fy_listeditor_dropdownitems

List Editor PHP Code Files

Usage Example

Database Table Records

Programming Code

Views

Personal tools

Navigation

Search

Toolbox