+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: populate mysql database

  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Default populate mysql database

    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.
    Attached Files

  2. #2
    Join Date
    Dec 2003
    Posts
    3,979

    Default Re: populate mysql database

    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:
    PHP Code:
    $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 Code:
    <?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 Code:
    <?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.
    Russell Robinson - Author of Tectite FormMail and FormMailDecoder
    http://www.tectite.com/

  3. #3
    Join Date
    Dec 2003
    Posts
    3,979

    Default Re: populate mysql database

    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 Code:
    <?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.
    Russell Robinson - Author of Tectite FormMail and FormMailDecoder
    http://www.tectite.com/

  4. #4
    Join Date
    Feb 2008
    Posts
    27

    Default Re: populate mysql database

    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?

  5. #5
    Join Date
    Dec 2003
    Posts
    3,979

    Default Re: populate mysql database

    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.
    Russell Robinson - Author of Tectite FormMail and FormMailDecoder
    http://www.tectite.com/

  6. #6
    Join Date
    Feb 2008
    Posts
    27

    Default Re: populate mysql database

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

  7. #7
    Join Date
    Dec 2003
    Posts
    3,979

    Default Re: populate mysql database

    Hi,

    I think your include path is incorrect:
    PHP Code:
    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:
    PHP Code:
    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.
    Russell Robinson - Author of Tectite FormMail and FormMailDecoder
    http://www.tectite.com/

  8. #8
    Join Date
    Feb 2008
    Posts
    27

    Default Re: populate mysql database

    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.







    Attached Files

  9. #9
    Join Date
    Dec 2003
    Posts
    3,979

    Default Re: populate mysql database

    Hi,

    OK, if this script works, then simply replace:

    PHP Code:
    $name=$_POST['name'];
    $lastname=$_POST['lastname'];
    $email=$_POST['email']; 
    with

    PHP Code:
    $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.
    Russell Robinson - Author of Tectite FormMail and FormMailDecoder
    http://www.tectite.com/

  10. #10
    Join Date
    Feb 2008
    Posts
    27

    Default Re: populate mysql database

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Similar Threads

  1. populate mysql database
    By coldal in forum Community Support
    Replies: 2
    Last Post: 07-Apr-2012, 07:01 PM
  2. Hook system and mysql database
    By onno in forum Community Support
    Replies: 14
    Last Post: 20-Sep-2010, 09:29 AM
  3. MySQL Database
    By Weineck in forum Community Support
    Replies: 8
    Last Post: 22-May-2009, 07:53 AM
  4. Mysql database
    By A77 in forum FormMail Subscription Support
    Replies: 11
    Last Post: 21-Apr-2008, 11:21 PM
  5. Inserting form info into MySQL database
    By MikeG in forum Community Support
    Replies: 6
    Last Post: 25-Jan-2007, 05:36 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts