populate mysql database

[coldal]

I have successfully used Tectite Formmail for some time now but part of a site revamp means I need to be able to extend successful form entries to an existing table in an existing mysql database.
Only after an applicant has successfully negotiated the required fields, security and the captcha thus generating the email and the good_template message do I require selected fields to populate a new line in the mysql table.
The final output will involve probably about 50 fields, but I have set up trial/test with files attached.
e9.htm is the user form /scripts/e9.php is the Tectite formmail.php file, e9_success.php is the good_template file where I was attempting to put the new php but unsuccessfully. The file list_records.php is a simple display of the mysql table contents to check if the process works or not.
I tried placing some php code in the good_template file (renaming it to .php) but you can see what happens if you drive http://bit.ly/IDcT7F which is the test/development form live on the site.
I expect it is straightforward, and I can take the solution and develop it.
The site is a hobby-based non commercial web site.

[russellr]

Hi,

The first step in achieving what you want is to use FormMail's Hook System.

This is easy to do.

First, in FormMail's configuration section look for $HOOK_DIR, and set it to ".", meaning current folder:

$HOOK_DIR = "."; 

Now, the Hook System works by including additional PHP code in various parts of FormMail's operation. The name of the file determines where it gets included.

For a database application, you really want this code included either just before FormMail sends you email, or just after it has done so. The choice is yours and depends on what you want to happen if the database code fails.

(If you're going to use FormMail's autoresponse, then there's another choice for placement.)

In any case, the code is identical, just the name of the file changes.

To run the database code before the email is sent, you put the code in a file called "fmhookpreemail.inc.php" and upload that file to the server into the same folder as FormMail.

Here's a simple test version that simply displays a message and immediately exits:

<?php
echo "This is the hook code";
exit;

If that works, now you can replace it with sensible error handling (which you'll want if your database code fails):

<?php
Error('database','Testing database failure');

Try that, and if you've got that working, the rest is straightforward. I'll post back with some sample database code.

[russellr]

Hi,

I've taken a look at the DB code you sent me, and have adjusted it below.

Just copy this to your fmhookpreemail.inc.php file (replacing the current contents), and make the appropriate changes for database connection, and you should be fine:

<?php
include("../scripts/under_neath/dbinfo.inc9.php");

$con = mysql_connect("host","username","password");
if (!$con)
{    
    Error('database','Could not connect: ' . mysql_error()); /* this also exits the script */
}
$name = mysql_real_escape_string($aCleanedValues['name'],$con);
$lastname = mysql_real_escape_string($aCleanedValues['lastname'],$con);
$email = mysql_real_escape_string($SPECIAL_VALUES['email'],$con);

mysql_select_db("applications", $con);
$sql="INSERT INTO e9 ('name', 'lastname', 'email')
VALUES
('$name','$lastname','$email')";
if (!mysql_query($sql,$con))
{
     Error('database','Insert failed: ' . mysql_error()); /* this also exits the script */
}
mysql_close($con);

Things to note:

1. Your original query was not correct, so I've fixed that.
2. Your form fields are in FormMail arrays. Most of them are in an array called $aCleanedValues, some are in $SPECIAL_VALUES as they are FormMail special fields.
3. Generally, the only two special fields you'll be interested in retrieving are "email" and "realname": $SPECIAL_VALUES['email'] and $SPECIAL_VALUES['realname'] .
4. These two arrays contain sanitized data - this means FormMail has cleaned the data from hacks and dangerous strings that might be attacks.
5. Each field you put in the MySQL query must be escaped with a called to "mysql_real_escape_string" (otherwise certain inputs can cause the query to fail).

That's it!

Good luck.

[coldal]

Get failure message after a lengthy delay at "connection" stage:
Could not connect: Lost connection to MySQL server at 'reading initial communication packet', system error: 110
any ideas?

[russellr]

Hi,

OK, time to go back a step.

Do you have a PHP script that can connect to your database and read or write rows in the table?

If so, please send it to me.

If not, that's the first thing you need to achieve.

[coldal]

The attached files do this successfully - but these are not linked to tectite FormMail which is what I wish to achieve.

[russellr]

Hi,

I think your include path is incorrect:

include("../scripts/under_neath/dbinfo.inc9.php"); 

Make sure the path is correct with respect to your installation of fmhookpreeemail.inc.php.

Perhaps it should be:

include("scripts/under_neath/dbinfo.inc9.php"); 

I'm just going on what you've sent me. The first and second files you sent were different in this respect.

[coldal]

I accept the point about the location of the login file but could this not be a red herring?
The attached fmhookpreemail.inc.php file actually works, but it is quite different from the version you suggested a few posts back and does not contain terms such as mysql_real_escape_string($aCleanedValues['lastname'],$con); and this gives me some concern.

To try and put to bed the ../scripts/ or /scripts issue, the login file dbinfo.inc9.php contains the following (with actual password removed of course:
<?
$host="localhost";
$username="ukrepeat_manage9";
$password="*****************";
$db_name="ukrepeat_applications";
$tbl_name="e9";
?>

and the file is located in public_html/scripts/under_neath/
Meanwhile the fmhookpreemail.inc.php file plus the Tectite FormMail file (e9.php) are placed in:
public_html/scripts/
The template files are in public_html/templates/

Hoping this helps.

[russellr]

Hi,

OK, if this script works, then simply replace:

$name=$_POST['name'];
$lastname=$_POST['lastname'];
$email=$_POST['email']; 

with

$name = mysql_real_escape_string($aCleanedValues['name']);
$lastname = mysql_real_escape_string($aCleanedValues['lastname']);
$email = mysql_real_escape_string($SPECIAL_VALUES['email']); 

That will ensure two things:

1. The input data is cleaned by FormMail (prevents injection attacks).
2. The input data won't break the SQL statement and also prevents another type of injection attack.

[coldal]

That's great, only outstanding issue is the generation of TWO error emails along with the input form to which they refer.
As recommended I have set the line:
define("DEF_ALERT","e9@ukrepeater.net");

To: e9@ukrepeater.net
From: FormMail@www.ukrepeater.net

The following error occurred in FormMail :
derive_fields: unknown value specification ""

email:
realname:


Referring page was http://www.ukrepeater.net/e9.htm SERVER_NAME was www.ukrepeater.net REQUEST_URI was /scripts/e9.php

User IP address was 78.143.214.98
User agent was Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0

also

To: e9@ukrepeater.net
From: FormMail@www.ukrepeater.net

The following error occurred in FormMail :
derive_fields: unknown value specification ""

email:
realname:


Referring page was http://www.ukrepeater.net/e9.htm SERVER_NAME was www.ukrepeater.net REQUEST_URI was /scripts/e9.php

User IP address was 78.143.214.98
User agent was Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0

Possibly relevant lines in the form as follows:

<form method="post" action="http://www.ukrepeater.net/scripts/e9.php" name="e9" enctype="multipart/form-data">
<input type="hidden" name="env_report" value="REMOTE_HOST,REMOTE_ADDR,HTTP_USER_AGENT,AUTH_TYPE,REMOTE_USER" />
<input type="hidden" name="recipients" value="e9GUINEAukrepeater.net" />
<input type="hidden" name="required" value="email" />
<input type="hidden" name="subject" />
<input type="hidden" name="good_template" value="http://www.ukrepeater.net/templates/e9-success.php" />
<input type="hidden" name="bad_template" value="http://www.ukrepeater.net/templates/e9-failure.htm" />
<input type="hidden" name="derive_fields" value="Callsign11=%ucase(Callsign1)%, Callsign21=%ucase(Callsign2)%, FirstName11=%ucase(name)%, Surname11=%ucase(lastname)%, Address11=%ucase(Address1)%, Town11=%ucase(Town1)%, Postcode11=%ucase(Postcode1)%, subject=%'e9 test from concerning '%+Callsign21+%, realname=FirstName11 + Surname11, DateTime = %day% + %dom%.%daysuffix% + %monthname% + %fullyear% * %'at'% +%hour240%.%':'%.%min%.%':'% * %tzname%, Date = %dom% + %monthname% + %fullyear%" />
<input type="hidden" name="mail_options" value="HTMLTemplate=e9a.htm,TemplateMissing=" />

If this can be resolved then its onwards and upwards with thanks to you for your help along the way!

Colin