FORUMS


The Form Tools forums are no longer active, but the old posts have been archived here. Please see the Help page on how to get help / report issues.

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Manually appending new fields to a form using PHP
#1
Hi, Ben.

Really quick question:

I have migrated across from 1.5 > 2.05 and am finishing off updating a substantial chunk of my prior PayPal integration scripts. Naturally, I thought I'd make some improvements along the way but I need some clarification as on how to proceed or if I'm missing anything in particular.

IPN processor works; I've written further code to automate database entries upon verification but I need some help. The following chunk of code checks to see if any of the dedicated paypal fields I've declared are present in the corresponding form table; if not, create them:

PHP Code:
// PayPal IPN Verified
//

//some code

// boom! check if paypal fields exist in table
$query = ("SELECT mc_gross,mc_fee ".
          
"FROM ".$g_table_prefix."form_".$form_id." ".
          
"WHERE submission_id='".$submission_id."'");
$result mysql_query($query)
    or die(
"select from table ".$g_table_prefix."form_".$form_id." not successful: ".
    
mysql_error());

if(!
$result)
{
    
// create missing fields
    
$columns = array(
        
'mc_gross'            => "varchar(20)",
        
'mc_fee'            => "varchar(20)"
        
);
                    
    foreach(
$columns as $key => $value)
    {
        
// checks if any field(s) already exist in table
        
$query = ("SHOW COLUMNS ".
                  
"FROM ".$g_table_prefix."form_".$form_id." ".
                  
"LIKE '".$key."'");
        
$result mysql_query($query)
            or die(
"show columns from table ".$g_table_prefix."form_".$form_id." failed: ".
            
mysql_error());
    
        
$col mysql_fetch_array($result);
    
        if(
NULL == $col[0])
        {
            
// create new field(s) and specify datatype and position in table
            
$query = ("ALTER TABLE ".$g_table_prefix."form_".$form_id." ".
                      
"ADD COLUMN ".$key." ".$value." ".
                      
"AFTER is_finalized;");
            
$result mysql_query($query)
                or die(
"altering table ".$g_table_prefix."form_".$form_id." add column failed: ".
                
mysql_error());
            
            
// update ft_form_fields table
            //
            //
            //
            //
        
}
    }
}

//more code 

Works great!

But I need these newly created fields (or columns) and their corresponding entries to appear from within formtools. Am I correct in assuming that I need to update the ft_form_fields table to reflect this update(?). If so, is there anything else I need to consider, update or be wary of?

Any assistance you'd be willing to provide would be hugely appreciated, Ben.
Reply
#2
Think I sussed it.

For those who are following, goes as follows:

Code:
//IPN Verified

// code

//finalise submission
ft_finalize_submission($form_id, $submission_id);

// check if paypal fields exist in form table otherwise create them
$query = ("SELECT mc_gross,mc_fee,txn_id,subscr_id,txn_type,payment_date,payment_status,paypal_info,test_ipn ".
          "FROM ".$g_table_prefix."form_".$form_id." ".
          "WHERE submission_id='".$submission_id."'");
$result = mysql_query($query);

if(!$result)
{
    // define arrays
    $column = array("mc_gross"         => array( "varchar"        => "20",
                                                  "field_name"     => "mc_gross",
                                                  "field_size"     => "small",
                                                  "data_type"     => "number",
                                                  "field_title" => "Gross",
                                                  "col_name"     => "mc_gross"
                                                 ),
                    "mc_fee"            => array( "varchar"     => "20",
                                                  "field_name"     => "mc_fee",
                                                  "field_size"    => "small",
                                                  "data_type"     => "number",
                                                  "field_title"    => "Fee",
                                                  "col_name"     => "mc_fee"
                                                 ),
                    "txn_id"             => array( "varchar"     => "20",
                                                  "field_name"     => "txn_id",
                                                  "field_size"     => "small",
                                                  "data_type"     => "string",
                                                  "field_title" => "Transaction ID",
                                                  "col_name"     => "txn_id"
                                                 ),
                    "subscr_id"         => array( "varchar"     => "20",
                                                  "field_name"     => "subscr_id",
                                                  "field_size"     => "small",
                                                  "data_type"     => "string",
                                                  "field_title" => "Subscription ID",
                                                  "col_name"     => "subscr_id"
                                                 ),
                    "txn_type"         => array( "varchar"     => "20",
                                                  "field_name"     => "txn_type",
                                                  "field_size"     => "small",
                                                  "data_type"     => "string",
                                                  "field_title" => "Transaction Type",
                                                  "col_name"     => "txn_type"
                                                 ),
                    "payment_date"     => array( "varchar"        => "255",
                                                  "field_name"     => "payment_date",
                                                  "field_size"     => "medium",
                                                  "data_type"     => "string",
                                                  "field_title" => "Payment Date",
                                                  "col_name"     => "payment_date"
                                                 ),
                    "payment_status"    => array( "varchar"     => "20",
                                                  "field_name"     => "payment_status",
                                                  "field_size"    => "small",
                                                  "data_type"     => "string",
                                                  "field_title" => "Payment Status",
                                                  "col_name"     => "payment_status"
                                                 ),
                    "paypal_info"     => array( "varchar"     => "0",
                                                  "field_name"     => "paypal_info",
                                                  "field_size"     => "very_large",
                                                  "data_type"     => "string",
                                                  "field_title" => "PayPal Info",
                                                  "col_name"     => "paypal_info"
                                                 ),
                    "test_ipn"         => array( "varchar"     => "1",
                                                  "field_name"     => "test_ipn",
                                                  "field_size"     => "small",
                                                  "data_type"     => "number",
                                                  "field_title" => "Test IPN",
                                                  "col_name"     => "test_ipn"
                                                 )
                    );

    foreach($column as $key => $value)
    {
        // checks if any field(s) already exist in form table
        $query = ("SHOW COLUMNS ".
                  "FROM ".$g_table_prefix."form_".$form_id." ".
                  "LIKE '".$key."'");
        $result = mysql_query($query);

        $col = mysql_fetch_array($result);
        
        // if not
        if(NULL == $col[0])
        {
            // create new field(s) and specify datatype and position in form table
            $query = ("ALTER TABLE ".$g_table_prefix."form_".$form_id." ".
                      "ADD COLUMN ".$key." varchar=".$value[varchar]." ".
                      "AFTER is_finalized;");
            $result = mysql_query($query)
                or die(mysql_error());
                            
            // now fetch existing list orders from formtools form fields table
            $query = "SELECT list_order ".
                     "FROM ".$g_table_prefix."form_fields ".
                     "WHERE form_id='".$form_id."'";
            $result = mysql_query($query);
    
            $list_order = array();

            while($row = mysql_fetch_array($result))
                array_push($list_order, $row[list_order]);

            // and define next list order
            $nxt_list_order = max($list_order)+1;
                        
            // finally, insert values into formtools form fields table
            mysql_query("INSERT INTO ".$g_table_prefix."form_fields (".
                        "form_id,
                        field_name,
                        field_size,
                        field_type,
                        data_type,
                        field_title,
                        col_name,
                        list_order,
                        include_on_redirect
                        ) ".
                        "VALUES('".$form_id."',
                                '".$value[field_name]."',
                                '".$value[field_size]."',
                                'textbox',
                                '".$value[data_type]."',
                                '".$value[field_title]."',
                                '".$value[col_name]."',
                                '".$nxt_list_order."',
                                'no'
                                )"
                        )
                or die(mysql_error());  
        }
    }
}

// lots more code

In short, a 2d Array did the trick. Having defined the required fields/values for both the form table and formtools form field table, it was pretty straight forward thereafter. Haven't tested it in it's entirety but the elements themselves certainly work.

Is there anything besides the formtools form fields table and form table that I should be updating when creating form fields outside of the formtools control panel?
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)