Thread

***INCLUDING ATTACHMENTS IN CSV***

Hello, I’m using the CSVee plugin to manually export data from my Freeform form submissions.  Those form submissions live in a table called, “exp_freeform_entries” and my code looks like this:

{exp:csvee filename=“call_registration.csv” fixed_width=“200” group_id=“1|6” query=“SELECT first_name, last_name, call_email, phone1, questions, mailinglist, template FROM exp_freeform_entries WHERE template=‘callregistration’”}

I trying to figure out how to include links to attachments that were submitted with the form, but attachments live in a separate table: exp_freeform_attachments.  Is it possible to adjust my SQL query to call more than one table?

***AUTOMATING***

I’m calling the CSVee plugin manually by visiting the page/template in a browser.  Can I automate this somehow w/a CRON job?  I’m not familiar w/ CRON, but understand its used to clean directories, back-up data, etc.  Is it ever used to emulate a page visit?  That’s the only way I can think of to automate a CSVee export.

Thank for any help you can offer.

Hi,

For the first part, yes, it’s easy to join the two tables together so this will also get the server path, filename and extension (but you’ll get multiple rows if there is more than one attachment)

SELECT
exp_freeform_entries.first_name,
exp_freeform_entries.last_name,
call_email,
exp_freeform_entries.phone1,
questions,
mailinglist,
exp_freeform_entries.template,
exp_freeform_attachments.server_path,
exp_freeform_attachments.filename,
exp_freeform_attachments.extension
FROM
exp_freeform_entries
Inner Join exp_freeform_attachments ON exp_freeform_entries.entry_id = exp_freeform_attachments.entry_id
WHERE template=‘callregistration’

Yes, you can certainly schedule running the script via a cron job. Here’s an example article that should get you started:

http://articles.sitepoint.com/article/introducing-cron

Hi Gareth.  Thank you so much for helping me.  Just tried it and it works great!  I wish there were a way around having multiple rows for each attachment.  For instance, I have to use this method on one form that’s got up to 11 attachments!  Whew!

Also, is there any way to merge the server_path columns to look more like a URL vs. the full server path?  I’m using email templates and those post the URL path to the attachment.

Hi Gareth.  Thank you so much for helping me.  Just tried it and it works great!  I wish there were a way around having multiple rows for each attachment.  For instance, I have to use this method on one form that’s got up to 11 attachments!  Whew!

You could do this, but you’d have to hack the plugin substantially.

Also, is there any way to merge the server_path columns to look more like a URL vs. the full server path?  I’m using email templates and those post the URL path to the attachment.

I’m not too sure exactly what is being output as I’ve not used Freeform attachments before but if by merge you literally just want to concatenate a couple of fields I think you’ll need to update your query by adding the CONCAT() function - this example makes the server path, filename and extension into one column. If you want to use a URL rather than a server path then I think something like the second example will work (may need some extra slashes)  - replacing the url to your upload directory obviously. I’ve not tested either of these so really have no idea if they work.

SELECT
exp_freeform_entries.first_name,
exp_freeform_entries.last_name,
call_email,
exp_freeform_entries.phone1,
questions,
mailinglist,
exp_freeform_entries.template,
CONCAT(exp_freeform_attachments.server_path,exp_freeform_attachments.filename,exp_freeform_attachments.extension) AS filename
FROM
exp_freeform_entries
Inner Join exp_freeform_attachments ON exp_freeform_entries.entry_id = exp_freeform_attachments.entry_id
WHERE template=‘callregistration’

SELECT
exp_freeform_entries.first_name,
exp_freeform_entries.last_name,
call_email,
exp_freeform_entries.phone1,
questions,
mailinglist,
exp_freeform_entries.template,
CONCAT('http://www.example.com/uploads/',exp_freeform_attachments.filename,exp_freeform_attachments.extension) AS filename
FROM
exp_freeform_entries
Inner Join exp_freeform_attachments ON exp_freeform_entries.entry_id = exp_freeform_attachments.entry_id
WHERE template=‘callregistration’

Hello again, Gareth.

Thank you again for this help.  Honestly, you’ve saved me so much time.  I’ve worked out this scenario.

Form is submitted by visitor.  Email confirmation goes visitor and the admin.  The admin’s email has a link to download the .csv.  Your concatenation example worked great.  The only potential issue I see the client having is multiple rows for each attachment.  But I completely agree that a single row won’t work w/o heavy mods to CSVee.

Have a great day.