MYSQL SETTINGS
(Database Support)

Configuring MySQL with Biz Mail Form

Important Note: It is best to start off by disabling the sending of form results to MySQL until you have successfully setup all other parts of the form. This is important because if MySQL is not setup properly on the server or if anything is incorrectly configured inside the script, the result will be an internal server error.

Understanding MySQL

MySQL is a storage database engine that works similarly to Flat File databases. The major advantages to MySQL over a flat file database are built-in query capabilities, better structure, speed, reliability and a smaller chance of data corruption.

MySQL OVERVIEW
Writing data to a MySQL database has several setup variables that to be configured before getting started. First, it is important to understand that MySQL must be properly installed on your server/host before enabling the MySQL function. A great program to administrate your MySQL databases is PHPmyAdmin.

Second, before you can use MySQL with Perl you must have "DBI" installed on your server.

To download the DBI drivers needed to run MYSQL with Perl go to:
http://dev.mysql.com/downloads/dbi.html

Third, before trying to use this script to write data to MySQL, make sure that the MySQL tables being are properly setup. Use PHPmyAdmin to test inserting records.

All the features in this section of the readme are found and configured in sql_settings.cgi.

<input type="hidden" name="SQL_write" value="1">

The hidden form field, name="SQL_write", defines whether or not to write data to MYSQL. If set to 1 as seen above, data will be written to MYSQL. If set to 2 this feature will be turned off.

$SQL_encryption_key

This variable implements an important security feature that is used when the MYSQL powered success page feature is used.

$SQL_encryption_key defines the encryption key for the primary key used. This feature restricts just anyone from accessing the form results success page.

For security purposes, it is important that the default value "SQL ENCRYPT KEY" is changed. The value should be between 10-15 Charachters.

Example Config:

$SQL_encryption_key = "MY SECURITY KEY";

$SQL_entries{""}

$SQL_entries{""} defines the MySQL field names with corresponding form field names. The format should be "MYSQL FIELD NAME" followed by the = sign followed by the form field name. Each set of entries should be seperated with a coma.

$SQL_entries{"1"} = "first_name=firstname,last_name=lastname,email=email,phone=phone,fax=alt_phone,ip=CONFIG_ip";
$SQL_entries{"2"} = "sql_field=form_field,sql_field2=form_field2";
$SQL_entries{"3"} = "";
$SQL_entries{"4"} = "";

For instance, if we are to look at the config for $SQL_entries{"1"} we see that the first part of the value of the variable contains first_name=firstname.

Since first_name appears first, this indicates that first_name is a MySQL field name.

Thus far, we have pulled out which MySQL field to which we want to write data. The next step is to specify which form field will write data to this MySQL field. The value that will be written will be whatever appears in the corresponding form field specified after the = sign.

Therefore, the = sign delimates or separates the MySQL field from the corresponding form field. The value entered into this corresponding form field will be the data that is written to the specifed MySQL field.

In this case firstname is what appears after the = sign indicating that firstname is the form field. Since firstname is the form field that will write data to the corresponding MySQL field, indicated in this example as first_name, then, if in our HTML form we have a field that looks like the following: Then if in our HTML form we have a field that looks like the below example shown:
<input type="text" name="firstname" value="Value written to MySQL">

Then the value entered in the field in the example above will be written to the MySQL field. In this case it is the MySQL field first_name.
The current value of the form field shown in the example is: Value written to MySQL

Therefore, if this form field in the example above is left un-edited, then the value writen to the MySQL field first_name will be: Value written to MySQL

Additionally, you can write data to an unlimited amount of MySQL fields. To write data to additional MySQL fields, simply place a comma after the previous form field name that was specified. Repeat this process until all of the fields you wish to write data to have been listed.

We have already seen the config to write data to the MySQL field of first_name, which, as seen in the example above, will be whatever is entered into the form field of firstname.

Thus far, we have entered our initial values of:

1. first_name=firstname

Since we want to send data to additional MySQL fields we need to specify these fields in the variable $SQL_entries{"1"}. As explained above this is done by entering a comma after what was shown in step 1.

2. ,

Then add the additional MySQL field, which in this example will be last_name with the corresponding form field name of lastname, Then you would follow step 3.

3. last_name=lastname As previously stated, you can write data to unlimited MySQL fields. Therefore, let’s look again at the original config of the variable, $SQL_entries{"1"}, set in bizmail.cgi in this example:
$SQL_entries{"1"} = "first_name=firstname,last_name=lastname,email=email,phone=phone,fax=alt_phone,ip=CONFIG_ip";

From what we have learned thus far, we can determine that from the value entered for the above variable, $SQL_entries{"1"} in this example, the following MySQL fields will have data written to them:

1. first_name
2. last_name
3. email
4. phone
5. fax
6. ip

The values that will be written to these 6 MySQL fields will be the corresponding values entered in the form fields with the names:

1. firstname
2. lastname
3. email
4. phone
5. alt_phone
6. CONFIG_ip

Several Important Notes:

1. In the example above there should be 6 form fields in your form:

<input type="text" name="firstname">
<input type="text" name="lastname">
<input type="text" name="email">
<input type="text" name="phone">
<input type="text" name="alt_phone">
<input type="text" name="CONFIG_ip">

It is important to note that these form fields do not have to be in a particular order, and can be set in any type of form field; whether that be a select, textarea, checkbox, or input field.

2. On the other hand, the order of the MySQL fields listed in the config variable $SQL_entries{""} should be in the same order as they are in the MySQL database from first to last.

Therefore, if you had the MySQL database structure of:

CREATE TABLE `example_table_name` (
`id` bigint(20) NOT NULL auto_increment,
`crypt_id` varchar(250) NOT NULL default '',
`first_name` varchar(250) NOT NULL default '',
`last_name` varchar(250) NOT NULL default '',
`email` varchar(250) NOT NULL default '',
`phone` varchar(250) NOT NULL default '',
`fax` varchar(250) NOT NULL default '',
`ip` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Then in this example the field orders are correct.

However, if you had the MySQL database structure of:

CREATE TABLE `example_table_name` (
`id` bigint(20) NOT NULL auto_increment,
`crypt_id` varchar(250) NOT NULL default '',
`first_name` varchar(250) NOT NULL default '',
`email` varchar(250) NOT NULL default '',
`phone` varchar(250) NOT NULL default '',
`last_name` varchar(250) NOT NULL default '',
`fax` varchar(250) NOT NULL default '',
`ip` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Then in this example the field orders would be incorrect , as shown in red.

3. You can use the predefined date and IP address functions to write specified data to MySQL. For instance, in the example above we wanted to retrieve the IP address of the form poster and write the value to the MySQL field ip. Since the config specified that the form field the value would be pulled from was the field:
CONFIG_ip

Then the form field CONFIG_ip should be set as below:
<input type="text" name="CONFIG_ip" value="predefined(I)">

More information on how to use the predefined() function click here.

$SQL_host{""}

The MySQL host name is the server host address of the server where your MySQL database is located.

This can be a domain name or an IP address.

HOWEVER, if the MYSQL database is located on the same server as your HTML form, then "localhost" should work just fine, as shown in the example config below.

Simply enter all the possible MYSQL Host Names you will need to use.

Example Config:

$SQL_host{"1"} = "localhost";
$SQL_host{"2"} = "yourdomain2.com";
$SQL_host{"3"} = "yourdomain3.com";

For example, if the MYSQL database is located on the same server as your HTML form, then you would insert the “field” below with the value of 1.
<input name="my_host" type="hidden" value="1">

-OR-

If the MYSQL database is located on the server with a host address of: yourdomain2.com, then on your HTML form page you would insert the below "field" with the value of 2.
<input name="my_host" type="hidden" value="2">

Etc....

...GOING FURTHER
If you have multiple forms and multiple addresses, simply add additional variables as shown below.
If you have 5; the config should look something like the below:

$SQL_host{"1"} = "localhost";
$SQL_host{"2"} = "yourdomain2.com";
$SQL_host{"3"} = "yourdomain3.com";
$SQL_host{"4"} = "yourdomain4.com";
$SQL_host{"5"} = "yourdomain5.com";

$SQL_user{""}

Simply enter all of the possible MYSQL connect "USERNAMES" that you will need to use.

Example Config:

$SQL_user{"1"} = "USERNAME1";
$SQL_user{"2"} = "USERNAME2";
$SQL_user{"3"} = "USERNAME3";

For example, if the MYSQL username you need to use is: USERNAME1,then on your HTML form page you would insert the below "field" with the value of 1.
<input name="my_user" type="hidden" value="1">

-OR-

If the MYSQL username you need to use is: USERNAME2, then on your HTML form page you would insert the below "field" with the value of 2.
<input name="my_user" type="hidden" value="2">

Etc....

...GOING FURTHER
If you have multiple forms and you need to use multiple MYSQL usernames, simply add additional variables as shown below.
For example, if you have 5, the config should look something like the below:

$SQL_user{"1"} = "USERNAME1";
$SQL_user{"2"} = "USERNAME2";
$SQL_user{"3"} = "USERNAME3";
$SQL_user{"4"} = "USERNAME4";
$SQL_user{"5"} = "USERNAME5";

$SQL_pass{""}

Simply enter all of the possible MYSQL connect "PASSWORDS" that you will need to use.

Example Config:

$SQL_pass{"1"} = "PASSWORD1";
$SQL_pass{"2"} = "PASSWORD2";
$SQL_pass{"3"} = "PASSWORD3";

For example, if the MYSQL password you need to use is: PASSWORD1, then on your HTML form page you would insert the below "field" with the value of 1.
<input name="my_pass" type="hidden" value="1">

-OR-

If the MYSQL password you need to use is: PASSWORD2, then on your HTML form page you would insert the below "field" with the value of 2.
<input name="my_pass" type="hidden" value="2">

Etc....

...GOING FURTHER
If you have multiple forms and you need to use multiple MYSQL passwords, simply add additional variables as shown below.
For example, if you have 5, the config should look something like the below:

$SQL_pass{"1"} = "PASSWORD1";
$SQL_pass{"2"} = "PASSWORD2";
$SQL_pass{"3"} = "PASSWORD3";
$SQL_pass{"4"} = "PASSWORD4";
$SQL_pass{"5"} = "PASSWORD5";

$SQL_db{""}

Simply enter all of the possible MYSQL "DATABASES" that you will need to use.

Example Config:

$SQL_db{"1"} = "DATABASE1";
$SQL_db{"2"} = "DATABASE2";
$SQL_db{"3"} = "DATABASE3";

For example, if the MYSQL database you need to use is: DATABASE1, then on your HTML form page you would insert the below "field" with the value of 1.
<input name="my_db" type="hidden" value="1">

-OR-

If the MYSQL database you need to use is: DATABASE2, then on your HTML form page you would insert the below "field" with the value of 2.
<input name="my_db" type="hidden" value="2">

Etc....

...GOING FURTHER
If you have multiple forms and you need to use multiple MYSQL databases, simply add additional variables as shown below.
For example, if you have 5, the config should look something like the below:

$SQL_db{"1"} = "DATABASE1";
$SQL_db{"2"} = "DATABASE2";
$SQL_db{"3"} = "DATABASE3";
$SQL_db{"4"} = "DATABASE4";
$SQL_db{"5"} = "DATABASE5";

$SQL_table{""}

Simply enter all of the possible MYSQL "TABLES" that you will need to use.

Example Config:

$SQL_table{"1"} = "TABLE1";
$SQL_table{"2"} = "TABLE2";
$SQL_table{"3"} = "TABLE3";

For example, if the MYSQL table you need to use is: TABLE1, then on your HTML form page you would insert the below "field" with the value of 1.
<input name="my_table" type="hidden" value="1">

-OR-

If the MYSQL table you need to use is: TABLE2, then on your HTML form page you would insert the below "field" with the value of 2.
<input name="my_table" type="hidden" value="2">

Etc....

...GOING FURTHER
If you have multiple forms and you need to use multiple MYSQL tables, simply add additional variables as shown below.
For example, if you have 5, the config should look something like the below:

$SQL_table{"1"} = "TABLE1";
$SQL_table{"2"} = "TABLE2";
$SQL_table{"3"} = "TABLE3";
$SQL_table{"4"} = "TABLE4";
$SQL_table{"5"} = "TABLE5";

$SQL_port

Is the port to access to connect to MYSQL.

$SQL_replace_single

$SQL_replace_single allows you to specify how you would like to convert your single quotes contained in the data written to MySQL. If this field is blank, single quotes will be replaced with a back slash.

$SQL_replace_single = "";

$SQL_replace_double

$SQL_replace_double allows you to specify what double quotes contained in data written to MySQL should be converted to. if this field is left blank double quotes will simply be escaped with a back slash.

$SQL_replace_double = "";

Other MYSQL Related Form Fields

<input type="hidden" name="sql_primary_key" value="id">

The hidden "form field" name="sql_primary_key" defines the Unique "primary key" of the MYSQL table to which you are writing form data. the MYSQL table you are writting the form data to. This is only required if you are using the MYSQL powered success page feature.
In the example above, the MYSQL Unique Primary key that is defined is: id

<input type="hidden" name="sql_encrypt_key" value="crypt_id">

The hidden "form field": name="sql_encrypt_key", defines the encryption key that is generated for the MYSQL powered success page feature; and therefore is only required if implementing the described success page feature.
In the example above, the MYSQL field that will store the encryption key is defined as: crypt_id