Final Filer Software

The complety free database management system for cemeteries & crematoria

The RegSearch Web Application has  a relatively simple database structure that could easily be used with data from any source, including direct manual data entry. The key is to understand the structure or schema. (you can grab the schema  SQL script here)

There are several tables used by the application

  • tblmaindata - contains the main detail of register and burial records
  • tblregisters - the descriptions of the registers that group burial and cremation records, like "My Cemetery's Burial Register". 
  • tblareas - contains records of areas in cemeteries etc, like "Lawn Cemetery" or "Anglican Cemetery"
  • tblportions - descriptions of portion names - portions are sub divisions on AREAS
  • tblerows - descriptions of the rows within portions
  • tblconfig - contains a single configuration record.
  • tblinforequest - used by the program - does not need any data

Additionally there is tbl_regimages, which is used for storing image files that can be displayed alongside the tblmaindata details.

If can import the data, perhaps from a spreadsheet or CSV file, using PHPmyadmin or similar. The order of importing should be to prepare and then import

  • tblregisters
  • tblareas
  • tblportions
  • tblrows
  • tblmain

The structure of each table is

Table structure for table tblregisters

Column

Type

Null

Default

Code

varchar(6)

Yes

0

Name

varchar(48)

No

 

website

text

Yes

NULL

Image_Test

text

Yes

NULL

Table structure for table tblareas

Column

Type

Null

Default

Code

varchar(6)

No

 

Name

varchar(255)

Yes

NULL

hideallocview

tinyint(4)

No

0

Table structure for table tblportions

Column

Type

Null

Default

Code

varchar(6)

No

0

Name

varchar(32)

No

 
       

Table structure for table tblrows

Column

Type

Null

Default

Code

varchar(6)

No

0

Name

varchar(32)

No

 

Table structure for table tblmaindata

Column

Type

Null

Default

ID

int(11)

No

 

Register

varchar(6)

No

Code links to tblregisters

AreaCode

varchar(6)

No

 Code link to tblareas

PortionCode

varchar(6)

No

 Code link to tblportions

RowCode

varchar(6)

No

 Code link to table Rows

IndexNumber

int(10)

No

0

FirstName

varchar(32)

No

 

LastName

varchar(32)

No

 

ServiceDate

date

No

0000-00-00

CommittalDate

datetime

Yes

NULL

Died

date

No

0000-00-00

Grave

int(5)

No

0

SubAlloc

char(2)

Yes

NULL

Aged

varchar(12)

Yes

NULL

Interment

tinyint(3)

No

0

Status

char(1)

Yes

NULL

serviceTime

time

Yes

NULL

url_fnotice

varchar(128)

Yes

NULL

url_obit

varchar(128)

Yes

NULL

url_remembrance

varchar(128)

Yes

NULL

Too daunting?

If you find all this too daunting or confusing I may be able to help, either by assisting with your data import or adapting the RegSearch Control Panel (CP) to your needs. .

 

 

 

 

 

 

If you are not using the Windows Desktop RegSearch Control Panel application to control the RegSearch web application you can modify the configuration directly in the database:

 

  • In PHPmyadmin  or your preferred MySQL client to open the online database
  • Open the table "tbl_config"
  • The table should contain just one record. If it does not contain any, create a new record. If it contains more than one record, delete (drop) all but one.
  • Now edit the single record

Field Descriptions

Field Comment
contactformurl URL pointing visitor to an online contact form
contactemail The email address any submitted form should be sent to. Usually some site admin
smtpserversmtpserver The SMTP Server to use for sending emails (optional)
smtpuser The smtp server's user account to send emails (optional)
smtppassword The password to send emails  thru the SMTP server (optional)
mailserverport The smtp server port; usually 26 for non secure emails or 465 or 528 for secure emails
mailsecurity "S" to use secure email, 'N' for non secure (SSL must be supported by the server)
UseContactForm enter 1 to make the contact form button visible on the register detail form. Enter 0 if you want it hidden.
UseMoreInfoForm enter 1 to make the More Information form button visible on the register detail form. Enter 0 if you want it hidden.

 

  • contactformurl < URL pointing visitor to an online contact form
  • contactemail 

 

ATTENTION RegSearch Users:

** This only applies to those who newly installed RegSearch before August 24th 2017, i.e. before version 3.21.

Recent and planned updates to the RegSearch  web application suggest that there is an issue with the data schemas out there. I want to try and get them consistent, but will need your help to do that.

Essentially I need a copy of the schema you are using. It should only take couple of minutes to generate:

PHPMyAdmin users:

  1. Open the database containing the RegSearch tables in PHPmyadmin
  2. DO NOT select any table
  3. Click EXPORT - you should see a page titled "Exporting tables from "<your-db>" database
  4. Click "Custom - display all possible options"
  5. Uncheck "Structure" and "Data" in the tables selection box
  6. Now check "Structure" only on the following tables, where they exist (some may not in your version):
    • servicetypes
    • ALL tables beginning with the prefix "tbl" and "tbl_"
    • All tables beginning with the prefix "tbl" and "sec_"
    • ALL VIEWS, i.e. table names beginning with "view_"
  7. Scroll down do the bottom of the page and click "Go"
  8. Save the result SQL file and email it to me (if you do not have my email please contact me first and I'll follow up).

If you are using another PHP client, it will probably have similar features to export schema. But please note I only need the schema, not the data.

I'm asking everyone to do this so I can be confident at RegSearch version 3.22 database schemas are consistent. It will also make it much easier for me to  to create more reliable schema modification scripts .

The RegSearch program is web based. It's default database server is Mysql.

The easiest way to create a connection to the database from your desktop so you can get to it with MS-Office, LibreOffice and other Windows based software is to use ODBC.

Setting up an ODBC connector for mysql is not difficult:

  1. Download and install the MySQL ODBC driver. The driver is mature and quite robust in my experience.
  2. On Windows (10 is assumed), press the Windows and the S keys at the same time.
  3. In the Search field type ODBC
  4. You should see the ODBC App link - click it to start it up.
  5. In the ODBC dialog click the ADD button on the right
  6. Select the MySQL driver from the list
  7. Click FINISH
  8. Fill in the fields for the connection parameters:
  • Data Source Name - A unique name, perhaps "RegSearcch_MySQL"
  • Description - optional
  • TCP/IP server - the IP address of your MySQL server, usually the server hosting your website - if in doubt talk to your server admins.
  • The port for MySQL is usually 3306.
  • A valid user name for the MySQL server
  • The password
  • Click TEST
  • If all is well, select the database that contains the RegSearch tables.

Again, if in doubt about any of the technical parameters, talk to your server admins.

Click OK to close the configuration. Click OK to close the ODBC app.

You can now connect to the MySQL database using your Windows desktop applications.

Contribute!