Data formatting

Hi Everyone, the following page is from a site which invites entries to a Golf competition that is about to enter it’s second year and it is being televised later in October. The form uses a FormMail to process it.

http://www.fournationsgolfchallenge.co.uk/howtoenter.html

The challenge is that the client is expecting a surge of entries after this and he is currently processing these manually (entering the details into a spreadsheet)

What I would like to know is, is there a script/application that anyone knows of, that could process this data for him, putting it into spreadsheet for him (maybe csv format)?

Many thanks

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Forms To Go can squirt data into a MySQL database. From there. If you have MySQL, your ISP has probably put something my PHPMySQL on the server to let you administer databases - that should give you a .csv file.


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Hi Mark,
If you fancy tinkering with FormMail you can set this up to export the data as a CSV file;

Alternatively Forms To Go (including the free ‘Lite’ version) will also do the job for you;
http://www.bebosoft.com/
Regards,
Tim.

On 30 Sep 2011, at 14:52, MarkSmith wrote:

Hi Everyone, the following page is from a site which invites entries to a Golf competition that is about to enter it’s second year and it is being televised later in October. The form uses a FormMail to process it.

divesbway.com – Just another WordPress site

The challenge is that the client is expecting a surge of entries after this and he is currently processing these manually (entering the details into a spreadsheet)


Tim Plumb
Creative Director
Softpress Systems

Follow us on Twitter: http://www.softpress.com/tny/064
Join us on Facebook: Redirecting...
Looking for a webhost? We love these guys: Softpress » Web Hosting Partners


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

The easiest way to manage this would be to do the CSV generation on the server. Your form handler would be the engine that creates the CSV file and updates it. And then your client would simply download and delete that file whenever he wanted to get the latest info. The server would just create a new one if the file was missing.

CSV is a rock-simple format, basically you just take the fields in the order presented by the form, wrap their values in quotes if they contain spaces, and separate them from one another by commas and end with a new line (return). Opening and writing a file on the server is very simple, too.

Here’s a script that does this, dragged out of my archives here.

The only things you have to configure are these:

  1. Your form submit button must have the Name (in the third tab of the Inspector) set to _submit. The leading underscore tells the handler that this is a private field, and you don’t want to add it to the CSV file.

  2. At the top of this script, set the address of your “thanks” page to something other than the canonical example page.

  3. You must have a folder on your server named csv, and this folder must be one folder above the one that holds this form handler. Ideally, you will put the form handler script in the same level of your server’s file structure as the main index.html file, which means that the csv folder is outside of the htdocs or public_html or whatever your hosting provider calls “the folder with all the Web files in it”.

The reason for this is that you have to give the csv folder relaxed permissions which allow the server itself to write to that folder. The most extreme example of this is called world-writable, or 777 permissions. Your hosting provider – particularly if you are on a shared server – will not really want you to do that. So ask them what username and group to set that folder to so that the Web server may write to it, but no one else can.

You’ll also want to set up an FTP or SFTP account that can see that folder – give those credentials to your client, and he can download the data file whenever.

  1. In Freeway, set your Form Setup / Action to point to this handler.

Walter

On Sep 30, 2011, at 9:52 AM, MarkSmith wrote:

What I would like to know is, is there a script/application that anyone knows of, that could process this data for him, putting it into spreadsheet for him (maybe csv format)?


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thanks Walt,

I appreciate your help.

Joe


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sorry for my late return to the thread guys and thank you for all your replies.

Walt - this script looks great and unless I am reading it wrong, do I just put this script in the main site folder (the same one as the html files) or does it need to go in the cgi-bin folder?

This script presumably replaces the FormMail.pl that I am currently using?

Just not sure of the exact locations of the various folders/scripts.

Tim - I will also look into tweaking the formmail on a test server I use as this will be a great option for the future as I use FormMail a lot.

Many thanks for your help as ever

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

On Oct 4, 2011, at 5:48 AM, MarkSmith wrote:

Sorry for my late return to the thread guys and thank you for all your replies.

Walt - this script looks great and unless I am reading it wrong, do I just put this script in the main site folder (the same one as the html files) or does it need to go in the cgi-bin folder?

It’s exceedingly rare these days that a PHP script would need to be run as a CGI. If this one did, I would need to rewrite it to include the “shebang” line to help it find the PHP command-line interpreter. So this script, as written, belongs in the same folder as your index.html page, and one folder above that, you must put a folder named csv. If your html documents all lived in a folder named ‘htdocs’, your server would look something like this if you looked at it with an FTP application:

marksmith/
	cgi_bin/
	csv/
	htdocs/
		Resources/
		form_to_csv.php
		index.html
	logs/
	...

The csv folder would have to be writable by your Web server, and only your hosting provider can tell you for certain what that means. For short-term testing ONLY, you can switch it to 777 permissions with your FTP application, and that means anyone can write in it. Because this folder is outside of the Web root folder (htdocs) you’re not worried about anyone outside of your server doing this – but you are worried about someone malicious on the same server as you who has a Perl script running at all times, looking for unsecured folders like this. Quite a few of the shared servers I use run what’s called a ‘setuid’ environment for the Web server, which means that the server runs as “you” – the user you log in to the server as – and that makes this completely easy and very secure. If that’s the case on your server, you would not need to change permissions at all, and the server would be able to write into that folder because you created it.

This script presumably replaces the FormMail.pl that I am currently using?

You would make one change in Freeway – to make your form’s Action attribute read ‘form_to_csv.php’ instead of /cgi-bin/FormMail.pl or whatever your local syntax requires.

Just not sure of the exact locations of the various folders/scripts.

Hopefully this has illuminated that for you. If you do get friendly with your hosting provider’s admin staff, you could ask them to create a user (for your client to use) that was able to write to that csv folder, but who was “chroot jailed” into it (any sysadmin knows what that word means). This will vastly simplify things for your client – and you – as using that login and password, your client would see only that one folder with the csv file in it, and could not go anywhere else on the server (like the htdocs folder with all your hard work in it) and could basically stay out of trouble. If your client download the log file, and either deletes it or renames it in place on the server, the next form submission would create a new file starting from scratch with that next submission’s contents.

Walter

Tim - I will also look into tweaking the formmail on a test server I use as this will be a great option for the future as I use FormMail a lot.

Many thanks for your help as ever

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thank you Walter for your help, that has cleared up the questions I had, I’ll give it a go.

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Actually Walter, I do have one other quick question:

Would it be possible to password protect the csv folder to add security or would this mess things up or be un-necessary?

Mark

On 4 Oct 2011, 5:22 pm, MarkSmith wrote:

Thank you Walter for your help, that has cleared up the questions I had, I’ll give it a go.

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

It is secured, because it is outside of your Web root. The Apache Web server cannot see “above” the level of your htdocs folder (or whatever it’s named on your server). Any folder that lives above that level in the filesystem of your server is completely invisible to the outside world. You or your client will need to use an FTP application to access this folder and the csv file inside it.

Walter

On Oct 4, 2011, at 1:27 PM, MarkSmith wrote:

Actually Walter, I do have one other quick question:

Would it be possible to password protect the csv folder to add security or would this mess things up or be un-necessary?

Mark

On 4 Oct 2011, 5:22 pm, MarkSmith wrote:

Thank you Walter for your help, that has cleared up the questions I had, I’ll give it a go.

Mark


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

as I use FormMail a lot

Am I mistaken but is the FormMail script not notoriously insecure? And at the very least you should rename it to something spurious that will not be targeted by hackers/spammers looking to abuse it.

David


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

+10 for a great idea, but you have to put the filename for this in the form’s Action, so renaming it isn’t going to hide it that effectively. The way you make this script secure is to erase it from your server.

Walter

On Oct 4, 2011, at 5:38 PM, DeltaDave wrote:

you should rename it to something spurious that will not be targeted by hackers/spammers looking to abuse it.


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sure - but do they (spammers) not fire out requests to anynumberofsites.com/cgi-bin/FormMail.pl hoping to get hits.

D


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sure. But if they’re even a little sophisticated, they will do this:

if(preg_match('/formm?ail/im',file_get_contents('http://example.com')){
	doBadStuff('http://www.example.com');
}

Walter

On Oct 4, 2011, at 6:40 PM, DeltaDave wrote:

Sure - but do they (spammers) not fire out requests to anynumberofsites.com/cgi-bin/FormMail.pl hoping to get hits.

D


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


freewaytalk mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options