[Snippet] eForm2db 0.1BETA - Manipulate DB records via eForm and MODx DBAPI

[Guest]

Welcome, Guest. Please login or register.
Did you miss your activation email?

1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[pixelchutes]

 
   Capture, store and process validated form submission with E’s ...eForm + eForm2db


         eForm2db
         Version: 0.1 [Beta 1]
         Author: pixelchutes
         Requirements:
            eForm 1.4+
         Use:

• Takes validated eForm submittal data for use with the MODx DBAPI extender class.
  Reference: /manager/includes/extenders/dbapi.mysql.class.inc.php
  
• Easily process database records referencing your form submission data:
• INSERT new records to a table of your choice
• UPDATE existing records if you'd prefer
• DELETE records based on form submittal criteria
• Form a query and return its record set as XML using $modx->db->getXML
• Query a table's MetaData using $modx->db->getTableMetaData, followed by an update query on only the form fields that are named after table columns!
• and much more!

The eForm2db snippet:

Code:

<?php
function eForm2db( &$fields )
{
/*---------------------------------------------------------------
eForm2db
Version: 0.1 [Beta 1]
Author: pixelchutes
---------------------------------------------------------------
Requirements:
eForm 1.4+
---------------------------------------------------------------
Use:
- Takes validated eForm submittal data for use with the MODx DBAPI extender class.
- Easily process database records referencing your form submission data:   
  + INSERT new records to a table of your choice   
  + UPDATE existing records if you'd prefer   
  + DELETE records based on form submittal criteria   
  + Form a query and return its record set as XML using $modx->db->getXML   
  + Query a table's MetaData using $modx->db->getTableMetaData, followed by an update query
    on only the form fields that are named after table columns!     
  + and much more!
---------------------------------------------------------------*/

// Bring needed resources into scope
global $modx, $table_prefix;

// Init our array
$dbTable = array(); // key = DB Column; Value = Insert/Update value

// Insert field/value pairs to insert/update in our table
$dbTable[name] = $fields[first_name].' '.$fields[last_name]; // Merge two form fields together
$dbTable[datetime] = date( 'YmdHis', strtotime( $fields[postdate] ) ); // Massage the postdate timestamp to be MySQL insert friendly


// INSERT - $dbQuery = $modx->db->insert( $dbTable, $table_prefix . 'insertTableName' );
// UPDATE - $dbQuery = $modx->db->update( $dbTable, $table_prefix . 'updateTableName' );
// DELETE - $dbQuery = $modx->db->delete( $table_prefix . 'deleteFromTableName', 'some_field = 1 AND name=\''.$dbTable[name].'\'', '' );
// etc...

// Run the db insert query
$dbQuery = $modx->db->insert( $dbTable, $table_prefix . 'insertTableName' );

return true;
}
// Return empty string
return '';
?>

Implementation:

Add the snippet call before your eForm snippet call:

Code:

[[eForm2db]]

Utilize eForm's "callback functions" in your eForm snippet call:

Code:

[!eForm? &noemail=`true` &formid=`contact` &eFormOnBeforeMailSent=`eForm2db` &tpl=`contactform` &thankyou=`contactthanks` !]

NOTES:
- &noemail param is optional. Here, I have used this call solely as a db capture form. Since no email is sent, there is no need to define things like: to, from, report, subject, etc. Simply disable mailing, and use the callback function to process your form's validated fields!

- This is my first attempt at giving back to this community. MODx is definitely the next level. While I realize eForm2db is not modular in any way-shape-form, it is indeed its direction. What do you think? Would this make a better Module? Plugin? etc... What direction should we take this to allow modular, customizable processing of eForm submittal?

More on the DBAPI:
http://www.modxcms.com/dbapi.html

[kudolink]

 

As soon as I start believing this is true I'll have other words.

_{Sincerely this is a post to have this thread in my "Show new replies to your posts" page. But this is very, very very interesting, truly.}

[davidm]

Indeed !

Really this really would take eForm to the next level... Not qualified to give you input as to which direction you should take, but it's really impressive and I am eager to see it in action...

Bookmarking this thread also

[sottwell]

Thank you! I've been meaning to dig into eForm to figure out how to use those two events, now I see how easy it is!

Now all we need is another event in eForm, onBeforeFormLoad, where we can grab data from a database to populate the form fields with for profile editing!

[pixelchutes]

@kudo/david/susan-- Thank you all for your interest, as well as your contributions!

I'd also like to give a hand out to the developer(s) behind the DBAPI. Once I realized this piece was already in place, I couldn't believe how easy it really was.

As you can see, a quick array and you're inserting/deleting/updating in no time! No connection logic, etc.

Ya know, I like the onBeforeFormLoad idea...it actually would be quite easy. Thanks, Toby!

[sottwell]

Ya know, I like the onBeforeFormLoad idea...it actually would be quite easy.
However, I think TobyL's intentions may have been to keep [ eForm <-> "DB Stuff" ] separate to reduce "bloated code"? Especially since his callback functions allow for it the way I've illustrated here.

Indeed, and I agree with the idea of keeping eForm itself clean. That's why I was thinking another event, so the database (or whatever... this could be used to load the $fields array with SESSION values, even) results could get loaded into the $fields array, but the developer would make his own snippet to do that, just as you have done.

[pixelchutes]

Indeed, and I agree with the idea of keeping eForm itself clean. That's why I was thinking another event, so the database (or whatever... this could be used to load the $fields array with SESSION values, even) results could get loaded into the $fields array, but the developer would make his own snippet to do that, just as you have done.

I believe the form is not fully parsed until after it's first submission...wait, that can't be true since the parser DOES remove all eform="" tags...

In that case, we might need to create db2eForm

[sottwell]

Exactly! But we need to be able to get that data before the form is parsed, so the data will get inserted as the form is being parsed. That means another event, so we can (with our db2eForm snippet) go get the data and tell eForm to insert it.

I did something similar, getting data from the SESSION and loading it into the $fields array, but I hacked eForm to do it. Much better to have an event to work with.

[pixelchutes]

As soon as I start believing this is true I'll have other words.

_{Sincerely this is a post to have this thread in my "Show new replies to your posts" page. But this is very, very very interesting, truly.}

BTW, believe it! I was up and running in 10 minutes and was inserting and deleting db records
Your imagination is really the limit with this one. Just a nudge in the right direction...

[xwisdom]

Very nice pixelchutes! Man you're light years ahead

Some DB features are on the to-do list for version 3.0 but it's nice to see that you have gotten a good head start with the built-in events

Here's one way I'm planning on adding support for data columns (or content fields) in 2.0 that you might find interesting:

New efield="column:datatype:default" attribute

<input name="name" type="text" eform="Your name:string-50:1" efield="fname:string:" emessage="You must enter a name" />

Note here that the -50 represents the length of the field. More on this in time to come.

[pixelchutes]

New efield="column:datatype:default" attribute

<input name="name" type="text" eform="Your name:string-50:1" efield="fname:string:" emessage="You must enter a name" />

Note here that the -50 represents the length of the field. More on this in time to come.

Thank you! This sounds like some very cool stuff. I am anticipating seeing/using the new methods. Can't you just feel the buzz?

[pixelchutes]

Query a table's MetaData using $modx->db->getTableMetaData, followed by an update query on only the form fields that are named after table columns!

In this quick example, I grab the columns from our `leads` table, and for any form field submitted that matches a column name in the table, a definition is defined for that column, using the validated, submitted form value.
(NOTE: I am not passing {PREFIX} because I don't use one here)

Code:

// Grab our table's columns
$tblColumns = array_keys( $modx->db->getTableMetaData( 'leads' ) );
// Loop through each column, and insert it if present in the form
foreach( $tblColumns as $tblCol )
if( isset( $fields[ $tblCol ] ) ) $dbTable[ $tblCol ] = $fields[ $tblCol ]

However, my contact form asks for both Best Time to Call and whether in AM or PM. I also need to convert the time they submitted the form to be MySQL-insert-friendly.

So, I handle any custom processing immediately after:

Code:

// Custom processing (e.g. date values, field joining, PHP logic, etc)
// Insert field/value pairs to insert/update in our table
$dbTable[datetime] = date( 'YmdHis', strtotime( $fields[postdate] ) );
$dbTable[call_time] = $fields[time] . ' ' . $fields[amorpm];

And then, simply invoke the insert SQL (as in my original example):

Code:

$dbQuery = $modx->db->insert( $dbTable, 'leads' );

And here is the result...eForm records right in the db!

[TobyL]

@pixelchutes

I was really hoping this would happen... This is truly great stuff.

However, I think TobyL's intentions may have been to keep [ eForm <-> "DB Stuff" ] separate to reduce "bloated code"? Especially since his callback functions allow for it the way I've illustrated here.

Yes, that was indeed my intention but an extra event wouldn't ga amiss I think. Once I've cleaned up the niggly bits you and others have pointed out in eForm I'll have a look at other possible events.

[TobyL]

Oh and I just noticed this one:

I believe the form is not fully parsed until after it's first submission...wait, that can't be true since the parser DOES remove all eform="" tags...
In that case, we might need to create db2eForm Cheesy

The form template is indeed not fully parsed until after the first form submission (would be pretty useless as it's meant for parsing data for validation at the moment).  It does merge any placeholders for which it finds a value in the $fields array. The eform pseudo-attributes are simply removed with a regular expression.

[pixelchutes]

@TobyL--

Thank you for clarifying this. Definitely points out that an event such as onBeforeFormLoad Would be pretty useful.

What if you built the parser to capture from <form id="eFormID" ... to </form> and maybe pass that as an argument to be referenced in the user function. They could setPlaceholders from db, etc, whatever they'd like really, then return the modified string-HTML. Is this the idea?