Export db data to CSV from CF v4

Hi ChronoEngine team-

I just installed CF v4 rc1.8 on a new Joomla 1.5.22 site. Works great and I really like the direction you are going with the interface. I had a form built in minutes and didn't have to read anything. Piece of cake.

My question: my clients need to be able to export the database entries into a CSV file from the ChronoForms dashboard. They can do that through the older version of CF, but I didn't see how in v4. Am I missing something?

thanks again for a great product!

Hi vashanka,

It's not in CFv4 yet but I expect that it is something that Max will add before too long.

Bob

Thank you! Please add my vote to having it included soon; it's super important for my clients so they can easily export contacts into their other databases.

Hi vashanka,

I've attached a new ChronoForms action to export records from a table to a CSV file.

To install the action download and unzip the attachment and then copy the contents to the administrator/components/com_chronoforms/form_actions folder on your site.

To test:
[list=a]

Create a new form with the form wizard, give the form a name and publish it then add just a Submit Button to the Preview box;

On the Events tab add a Show HTML action to the On Load event

Look in the DB Operations tab for the CSV Export [GH] and drag that to the On Submit Event; followed by a Show Thanks Message and Debugger actions from the Core Actions group

Click the centre 'spanner' icon on the the CSV Export [GH] action and select a table from the first drop down (I used jos_categories). Save and close the action configuration.

Click the centre 'spanner' icon on the the Show Thanks Message action and add the following code to the message box:

You may download your file from <a href='{csv_link}'> here </a>.

{csv_count} records were exported.
The file size is {csv_size} kb.

Save & Close the form and view it in the Front End.

Click the Submit button and you should see a message like this:

You may download your file from here. 17 records were exported. The file size is 0.3 kb.

with more detailed information from the debugger.

[/list:o]

That is the simplest configuration and will export all of the data in the table.

You can configure the plug-in to be much more selective in terms of the data exported. See the action Help Tab for more information.

Main features:
[list]

Select any table from the Joomla! database

Specify the export folder and file name including form results and new variables (table name, random number, datetime string)

Specify MySQL WHERE and ORDER BY clauses to filter and sort the data

Specify lists of Included / Excluded columns for simple selection

OR Specify a list of Column Titles and column names to build a custom set of columns; this can include MySQL Functions.

You can use Form inputs to specify the table, folder, file name and the MySQL WHERE and ORDER BY clause to make the export interactive. This feature is only available to registered users and should be used with care as the results may be used in MySQL queries.

[/list]

The action was written in Joomla! 1.6.3 and has been tested in Joomla! 1.5.23 It should be treated as beta code and used with care. Please report any bugs you find here for fixing.

Bob

The action is now available for download here

I fixed a couple of small bugs: it's now correctly packaged in a folder; there is only now an On Fail event (see comments later in this thread).

Thanks Bob!

I will give this a try in the next couple of days and let you know how it goes- appreciate your time and hard work!

Vashanka

Hi, I was looking for the same solution and I tried what you posted here and it works great, except when I specify certain columns I want exported. I receive this error on export: Warning: in_array() [function.in-array]: Wrong datatype for second argument.

My list is as such: input_text_1, input_text_2, input_text_7.

Am I not entering the information correctly or is this a table problem?

Hi xlittle,

Sounds like a bug. I'll take a look.

Bob

Hi xlittle,

Hmmm . . . I can't get that error to appear.

By all means email or PM me the site URL and a SuperAdmin login and I'll take a quick look and see if I can spot where the problem is.

Bob

Grayhead

While this export action is a great addition to ChronoForms, I've discovered what appears to be a minor bug:

There is no $this->events['success']=1 ever defined in this action, thus it never returns you to the success event.

I was able to finally successfully display the Thank You action as you described in your instructions upon changing /administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php around line 220 from:

		$form->debug['CSV Export'][] = 'File size is: '.$form->data['csv_size'].' kb';
	}

to:

		$form->debug['CSV Export'][] = 'File size is: '.$form->data['csv_size'].' kb';
		if(!$this->events['fail']) $this->events['success']=1;
	}

I've observed this in other actions, too. It might be something worth adding to the core as a class for success/fail situations as a function rather than a variable. That, or configuring the system such that in the event 'fail' is ever true, even if 'success' is true (which would be the default), it ignores 'success' and considers it a failure. Either way, then, there is never a possibility of ending in purgatory where it's neither a success nor a failure like that which happened to me.

Hi bizzynate,

Thanks for that. I haven't really go my head around the Success values yet so tend only to use the Failure ones. I also find it a little odd that there isn't a default to 'Success'.

I'll keep this tab open and update my CSV action - just have to reinstall my test sites on a new server first :-(

Bob

Hi,

You can place any success actions AFTER the main action instead of inside its success event, and an "event loop" or "show stopper" in the on fail, this ensures that your actions will be executed when there is no fail, regardless of "success" reported or not, makes the events tab less complicated as well.

Regards,
Max

Hi bizzynate,

I got a better idea of how the On Success and On Fail events work and have modified the CSV Export [GH] action so that it only has an On Fail event. I couldn't immediately think of any circumstances when I'd want any special action On Success other than to continue the normal flow. (There's a new download link in my previous post.)

Action sub-events are not required and are not limited to On Success and On Fail. In practice they are a series of event triggers that can be specified in the action as values in an array. For example

$events = array('success' => true; 'fail' => false);

or equally

$events = array('apples' => 0; 'oranges' => 0, 'bananas' => 0);

Each of these events can be linked to an Event Box that displays on the action. The name, color (set in the CSS), and sequence are entirely arbitrary.

The event values are boolean and can be changed in the action code when it executes.

After the action has executed ChronoForms checks the events array and if there are any events set to 'true' it will run any actions that have been put into that events box. The sequence of events is decided by the order of the keys in the $events array.

Once any events have been executed control returns to the parent Event and the next action there will be executed.

Bob

Hi,

I followed your excellent instructions on how to do this, it worked fine for a test form I had set up already with an associated table. The message appeared click here for 30 records size etc. All well and good.

What I then did was set up a private page (for the site user) on that page I created a form as per your instructions, all looked ok, but when I navigate to the page with the form with the one SUBMIT button on it, I see "You may download your file from here {csv_count} records were exported. The file size is {csv_size} kb."


I do not see the record count figure or the file size in kb, just the code placeholders, and when I click the HERE link I get a 404 not found.

Am I missing something?
Thanks. McP

Hi McP,

Please take a Form Backup using the icon in the Forms Manager and post it here (as a zipped file) or PM or email it to me and I'll take a closer look.

Bob

To install the action download and unzip the attachment and then copy the contents to the administrator/components/com_chronoforms/form_actions folder on your site.

There is no /form_actions folder on my site, do I create it?

Also when downloading the zip file I get an error when unzipping it on a Mac. I still got a result though, is there just one file to load in called csv_export_gh.ctp ?

Please advise, thanks.

Hi almchad,

If you have ChronoForms v4 installed and working then there is an administrator/components/com_chronoforms/form_actions folder Note the 'administrator' bit of the path.

Mac's seem to have problemn with zip files :-(

There should be three files inside csv_export_gh.ctp, csv_export_gh.php and index.html. They all need to go into a form_actions/csv_export_gh folder (The zip contains the folder but unfortunately it doesn't appear when you unzip on a Mac.)

Bob

Hi, I was looking for the same solution and I tried what you posted here and it works great, except when I specify certain columns I want exported. I receive this error on export: Warning: in_array() [function.in-array]: Wrong datatype for second argument.

To fix this, declare $exclude to be an empty array before inarray(). I added this around line 112:

if (!$form->data['exclude']){
    $exclude = array();
}

Bob probably wasn't seeing the error due to different error reporting setting in php.ini.

Chris

Hi Chris,

Thanks for that I've added a fix to the file in the download.

			if ( $exclude ) {
				$exclude = explode(',', $exclude);
				foreach ( $exclude as $k => $v ) {
					$exclude[$k] = trim($v);
				}
                        // add the next two lines
			} else {
				$exclude = array();
			}

Bob

Warning: fopen(/home/d***y/domains/d***y-outdoors.com/public_html/components/com_chronoforms/exports/export/csv_export_pop_chronoforms_data_registration_201108251600.csv) [function.fopen]: failed to open stream: Permission denied in /home/d***y/domains/d***y-outdoors.com/public_html/administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php on line 197

Sorry the *** is the section above are just to stop it coming up in google

Hi MrToast,

Warning: fopen(/home/dragonfly/domains/dragonfly-outdoors.com/public_html/components/com_chronoforms/exports/export/csv_export_pop_chronoforms_data_registration_201108251632.csv) [function.fopen]: failed to open stream: Permission denied in

it looks as though the Joomla! User doesn't have write permission in this folder.

Please check the chmod settings.

Is this path correct components/com_chronoforms/exports/export/ ???

Bob

yep I fixed it sorry my bad. now the only issue i have is whenever I set the location to save the file in the backend it doesn't hold the value. for example i apply then save the form, reload the page (in the backend) and then go to edit the values and nothing is there.

strange.

Hi Mr Toast,

Remember that in Cfv4 you have to Save the modal window by clicking on the blue 'flag' AND then save the form by clicking on 'Save' or 'Save and Close' in the toolbar.

Bob

Hi Mr Toast,

If following the steps posted by Bob doesn't solve it then you have some bug which is fixed in the coming version V4 RC2.0, you may contact me through the "Contact Us" page to get it.

Regards,
Max

Hi,
I have installed the components "csv_export_gh", i have added "CSV Export [GH]" action after show html, now i must set "Specify MySQL WHERE and ORDER BY", i have no idea how can do it because i'm a graphic designer😶
Can you help me?
Thanks
loste

Hi loste,

If you want all the records exported and don't care about the order then you can leave those boxes empty.

Bob

ok, but how can i export my record?
if you look the form "http://www.promozioniaudionova.it/partecipa" there is the code in the page

loste

Hi loste,

The action belongs in the On Submit event, not in the On Load event.

Why would you want to export a single record from a database table to a CSV file?

Bob

there Was a mistake, i'd like export allega record, is it possibile?

Hi loste,

Please explain some more.

Bob

Hi bob, i will try to explan better……
when the users submit the form, i have a record for each user in chronoform back-end.
But if i need export all data user in a file (please,look the attaced file), is it possible? how can i do?

1. Create a new form (mine is named "export").
2. Add a submit button (in the Wizard, 'Preview' tab).
3. In the 'OnSubmit' event, add the CSV export action.
4. In the CSV action tab 'settings', add your table name.
5. In the CSV action tab 'columns', enter a comma-separated list of fields (name,surname,etc).
6. After the CSV action, add a 'Thanks Message' action to 'OnSubmit' event.
7. Add this code to the thanks message:

<p>You may download your file from <a href='{csv_link}'> here </a>.</p>

<p>{csv_count} records were exported.<br/>
The file size is {csv_size} kb.
</p>

.

8. Visit the new form, press submit. All user data from the columns in step #5 will be exported to CSV.

I added an authenticator to the 'OnLoad' event as well, to restrict access to super-users. You may also find adding a debugger action to be useful while setting it up.

HTH,
Chris

Hi Chris,

Perfect, thank you.

Bob

Thanks Chris, I will try
loste

Hi, i have followed the Chris list, but the point n°8 i don't understand. I have press "submit" in my new form, but where i can find the file CSV?

loste

Hi loste,

If you did step 7 then you will see a page with a link after you submit the form.

Bob

Ok, now it works, but I have 2 problems still:
1- I have setted n° 3 columns (step 5) but i can see only 3° column
2- I have n°8 regords, but i see only 5 users
Why?

if you want check the back end you can log with user and password that I have sent you some days ago
Thanks
loste

Are you sure the column names in step #5 match the column names in your database *exactly*?

I did not put spaces in the column field in step #5 either, so mine looks like:
cf_id,name,email,birthday

Are you sure all 8 records have an email address? Check your database table (in phpmyadmin for example)- I would guess 3 of them have an empty value in the 'email' column. Or look at the CSV directly (in a text editor) and count the rows and commas.

HTH,
Chris

The columns name was wronged, now are the same of database, but when i want see the recor i have 404 erreor. Can you help me?
loste

Hi, i Have fixed all my form problem

Only a question about step 7,
With this code "<p>You may download your file from <a href='{csv_link}'> here </a>.</p>" i see the CSV data in the browser window. Is it Possible download the CSV file in my computer instead of displaying it in the browser windows?

Thanks all
Bye,loste

Hi loste,

Right click and select 'Save File'

Bob

The option is great, though there are some complications:
1. In eastern Europe decinal separator is "," so MS Excel writes and reads correctly csv with field separator ";". To make it running I needed to put following code in csv_export_gh.php at line 203:206

 		fputcsv($file, $titles, ';');
		// add data rows to the file
		foreach ( $data as $d ) {
			fputcsv($file, $d, ';');

2. Would it be useful to add option for the user to select between "," and ";"?

3. Is there a simple way to export not a table but query result?
Thanks,
Eldar

Hi Eldar,

I'll try adding the options to change the delimiter and enclosure characters in the next version. (I kept it simple because my experience is that you can rapidly run into problems when these are changed.)

You can already run a query on a single table using the WHERE and ORDER boxes and the rows and columns filters. What else do you need?

Bob

i have the same problem about "," and ";" . i couldnt find csv_export_gh.php to solve that.. what can can i do to change "," with ";" ?
thanks for reply..?

Hi,

I've built a beta version that allows you to define the delimiter character (that goes between each value) and the enclosure value (that is used to wrap values with spaces in them).

Please test by downloading the attached file, unzip and copy the two files to the administrator/components/com_chronoforms/form_actions/csv_export_gh folder. (I suggest that you keep the existing files as backup in case you need to revert.)

Please take care with the values you use here as you may get unexpected results!!

Please post any bugs you find here for me to look at.

Bob

I have the same problem. I tried it just as you did and selected jos_categories like you did and it downloaded great. But when I select my custom tables I get a 404 error as well. Any ideas? I have the latest version of ChronoForms loaded which is 4.0 RC2.0. It's probably also important to note I'm using Joomla 1.6.
Is there a different process using Joomla 1.6? With it working correctly using "jos_categories", it makes me believe it's something else.


UPDATE: I figured it out. If you don't have data stored, you get an error.
Would sure be better if it said something to the effect that there is no data available vs a 404 error.

How can be the csv files deleted after the download?
Can the generated file name and path be read from another action like "Custom Code"?

Hi Mat,

You can delete them if you have a submit button displayed with the download link. Then the form OnSubmit event could have a custom housekeeping action. It's probably simplest just to empty the folder where the downloads are stored.

Bob

Does the “Exclude columns” function work?
If I use the “Include columns” function adding what I need, works, but if I leave empty all the Include Columns and Columns details fields but I add “cf_id,cf_uid,cf_modified,cf_ipaddress,cf_user_id” in the Exclude columns field, I get all the colums as output.

Hi Mat,

No it doesn't :-( You found a bug

Fixed version attached and the upload on my site is updated.

Bob

Thanks for the fix. It works now and it easier to maintain if I add fields in the form.

Is there any way I can join 2 tables before exporting??

I want to get address and other info from CB and primary email from Joomla users. I can think how I could join the 2 tables in a query but do that then export to csv?

Maybe a step to far, but if it's possible :-) Please

Also how could I set it up to run a different export process depending on a drop down selection?

In other words
if I "selected address" only I would run an export that only includes a few column, but if I chose "full details" it would export a fuller set (though not all the avatar & blocked stuff).

I can do these in seperate forms, but if I could do it in one that would be lovely.

Hi olaeblue ,

Sorry, neither the JOIN nor the optional selection are currently possible. Noted for a s future version though.

Bob

I noticed that another user wrote this:

With this code "<p>You may download your file from <a href='{csv_link}'> here </a>.</p>" i see the CSV data in the browser window. Is it Possible download the CSV file in my computer instead of displaying it in the browser windows?

And you responded to right click and press "save File", the only problem is when I do that it saves a webpage not the CSV file. I know this may seem silly to you but I would appreciate your help!
Thanks
Christie

Hi Cristie,

I have a new version of the action that includes an 'Immediate Download' option but it's not quite ready for release. Meanwhile the right-click option should work OK provided that the link is correct. Can you post the link HTML here - or a link to the download page.

Also see the quote below from http://www.htmlhelp.com/faq/html/links.html#force-download

Bob

How do I force the browser to download a file? How do I force the browser to show/play a file itself? How do I force a file to be opened by a particular program?

You can't do any of these things reliably, because the Web doesn't work that way.

When the browser requests a document (hypertext, image, audio, multimedia, etc.), the server tells the browser what type of file it is. The server should be configured to identify a document's media type properly.

The browser then decides what to do with it. Different browsers are able to and are configured to display different types of documents themselves. Browsers are usually configured to handle other file types by using appropriate helper applications or by offering to save the document to the filesystem.

RFC 2183 describes the Content-disposition header, which can be used to suggest how the browser should handle documents. The value "inline" suggests that the document should be displayed automatically, and the value "attachment" suggests that the user should be prompted before the document is displayed (or saved). Note that this mechanism has significant security implications, which are described in the RFC.

Most browsers allow users to download to disk if they want to. If the file must be saved to disk, if there is absolutely no other way to handle it, then the MIME type could be "application/octet-stream". However, this essentially says, "I can't or won't tell you what this is." Furthermore, the MIME type "application/octet-stream" provokes incorrect behavior in MSIE's HTTP implementation. A better alternative would be a custom MIME type like "application/x-some-explanation", which avoids these problems.

Bob,

I have it worked out now... I was clicking the link and then trying to download it once it loads in the browser and that is why I was saving a webpage. Once I turned my brain back on, I tried right clicking on the link to the file and it let me save the CSV.

It works great now!🙂 Though I am looking forward to the new action that has the automatic download function, this works wonderful in the meantime. Thanks!
Christie

Hi Christie,

There's an updated version available now that has extra options to force an immediate download.
http://greyhead.net/how-to-docs/chronoforms-v4-actions

Bob

Bob,
This works flawlessly! Thank you so much for the update!
Christie

Hi Christie,

As ever I found a bug as soon as it is published. There's a newer version that fixes a problem with saving custom paths. No need to upgrade unless you hit the problem.

Bob

Works beautifully but want to use form data in where clause & the following doesn't work either with ` or with ' round {meet_select}

`meet_name` = `{meet_select}` 

meet_select is a select box which lists all meet names in the table

<?php
    $db =& JFactory::getDBO();
    //
    $query = "
      SELECT DISTINCT `meet_name`
        FROM `#__chronoforms_data_meets`;
    ";
    $db->setQuery($query);
    $options = $db->loadAssocList();
    foreach ( $options as $o ) {
       echo "<option value='".$o['meet_name']."'>".$o['meet_name']."</option>";
    }
 ?>

Debug shows that it isn't parsing correctly

$query: SELECT * FROM `fv1lz_chronoforms_data_meets` WHERE `meet_name` = '{meet_select}' ; 

Probably my php error but any help as always gratefully received.

Hi olaeblue,

You need straight single quotes round the value ''

Please add a debugger action to the form On Submit event and post the results here.

Bob

If I type the value in it works
WHERE statement is `meet_name` = 'LHG Jan 2012' AND `share` IS NULL

Data Array:

Array
(
    [meet_select] => LHG Jan 2012
    [input_submit_0] => Download CSV File
    [8f9b6161b167c545921c98f3fbd4144a] => 1
    [option] => com_chronoforms
    [chronoform] => meets_download
    [event] => submit
    [Itemid] => 
    [csv_link] => http://www.yrc.org.uk/yrcweb/components/com_chronoforms/exports/meets_download/csv_export_fv1lz_chronoforms_data_meets_201201041309.csv
    [csv_count] => 16
    [csv_size] => 3
)

Debug Data

    CSV Export
        $download_export: 0
        $download_nosave: 0
        Save folder is:
        /homepages/11/d369608199/htdocs/yrcweb/components/com_chronoforms/exports/meets_download/
        $download_export: xxx
        $download_export: 0
        File name is:
        csv_export_fv1lz_chronoforms_data_meets_201201041309.csv
        $query: SELECT `meet_name`, `for`, `name`, `email`, `with`, `lift`, `from`, `Arrival`, `Fri_Eve`, `Sat_Break`, `Sat_Dinner`, `Sun_Break`, `notes` FROM `fv1lz_chronoforms_data_meets` WHERE `meet_name` = 'LHG Jan 2012' AND `share` IS NULL ORDER BY `name` ASC;
        16 records were found to export.
        Save link is:
        http://www.yrc.org.uk/yrcweb/components/com_chronoforms/exports/meets_download/csv_export_fv1lz_chronoforms_data_meets_201201041309.csv
        File size is: 3 kb

But if I try to bring info in from form data {meet_select} it fails

WHERE statement is `meet_name` = '{meet_select}' AND `share` IS NULL

Data Array:

Array
(
    [meet_select] => LHG Jan 2012
    [input_submit_0] => Download CSV File
    [8f9b6161b167c545921c98f3fbd4144a] => 1
    [option] => com_chronoforms
    [chronoform] => meets_download
    [event] => submit
    [Itemid] => 
)

Debug Data

    CSV Export
        $download_export: 0
        $download_nosave: 1
        Download 'No Save' is set so no folder is created.
        File name is:
        csv_export_fv1lz_chronoforms_data_meets_201201041305.csv
        $query: SELECT `meet_name`, `for`, `name`, `email`, `with`, `lift`, `from`, `Arrival`, `Fri_Eve`, `Sat_Break`, `Sat_Dinner`, `Sun_Break`, `notes` FROM `fv1lz_chronoforms_data_meets` WHERE `meet_name` = '{meet_select}' AND `share` IS NULL ORDER BY `name` ASC;

Hi olaeblue,

Slow start to the New Year here . . . the action never allowed form data in the WHERE box. Like you, I expected that it would (but then I wrote it).

You can try a hack if you like: open administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php and add the extra line shown here around line 174

		if ( $where ) {
			$where = $form->curly_replacer($where, $form->data); // <-- add this line
			// strip off anything after a ;
			$sc_found = strpos($where, ';');
			if ( $sc_found ) {
				$where = substr($where, 0, $sc_found);
			}
			// clean up WHERE
			$where = str_ireplace('where ', '', $where);
			$where = 'WHERE '.$where;
		}

Bob

Works like a charm!!

Worth a beer :-)

Hi olaeblue,

Thank you, much appreciated.

I'll update the version on my site when I have a moment to edit the help tab.

Bob

Hii All ,


Please help me , the link of the Export action was broken there is an error when iam trying to download it , i need it As soon As Possible:(

Hi r2awm,

It is built in to ChronoForms v4 RC3.0 so please upgrade if you haven't already done so.

Bob

Hi GreyHead!

I realy like this function! I have 1 question. I set the delimiter to ";". So excel puts al the info in columns. It works fine except fore the titels (the upper row), there the delimiter is ",".

Do you have any idea how i can fix that?

Thanks !

Emil

Hi Emil,

It was a bug - I forgot to add the code to the titles. I've just put an updated version on my site.

Bob

Hi GreyHead,

I make a mistake, but I do not understand. My csv file is unreadable. I am enclosing a Exemplare what I get.

Hi Irate,

There's no attachment here.

Bob

Oups. The extension csv is not allowed... Ok with Zip

Irate

Hi,

Another problem:
I unchecked "immediate download", "apply", "save" but the download will start immediately.

Hi Irate,

I've no idea what that file is. It certainly isn't a CSV file and doesn't look like anything produced by the CSV exporter.

I just confirmed that unchecking Immediate Download is working here.

Bob

It works! I put a folder name on the site to recover and now ... it works.
The darkness of the computer 🤣

Hi olaeblue,

Slow start to the New Year here . . . the action never allowed form data in the WHERE box. Like you, I expected that it would (but then I wrote it).

You can try a hack if you like: open administrator/components/com_chronoforms/form_actions/csv_export_gh/csv_export_gh.php and add the extra line shown here around line 174

		if ( $where ) {
			$where = $form->curly_replacer($where, $form->data); // <-- add this line
			// strip off anything after a ;
			$sc_found = strpos($where, ';');
			if ( $sc_found ) {
				$where = substr($where, 0, $sc_found);
			}
			// clean up WHERE
			$where = str_ireplace('where ', '', $where);
			$where = 'WHERE '.$where;
		}

Bob

Hi Bob

I'm also having problems with using form data in a Sql WHERE on this action. Debugger output is below.

I added the hack you recommended to olaeblue in January, but still no joy (though this is now at line 345 of csv_export_gh.php - have you changed other stuff in the last few months ?)

Any suggestions ?

Thanks

Steve


=====
csv export failed Debug Data
CSV Export
$download_export: 0
$download_nosave: 0
Save folder is:
{#path#}/export/
$download_export: xxx
$download_export: 0
File name is:
Rupert Murdoch.csv
$query: SELECT `input_childname`, `input_parentname`, `input_dobd`, `input_dobm`, `input_doby`, `input_prefsess`, `input_altsess`, `input_addr1`, `input_addr2`, `input_addr3`, `input_postcode`, `input_homephone`, `input_mobilephone`, `input_email`, `input_medical`, `input_addinfo` FROM `n0x49_chronoforms_data_enrolment` WHERE 'input_childname' = {input_childname} ;

Aardvark
I updated to RC 3.3 this morning and needed to put the hack back in as the csv export stopped working. It was still at line 174 that the change was needed though!?

Bob, you need to get this hack included as standard so I don't have to keep doing this :-)

Hi olaeblue,

Unfortunately I have no control over what Max puts in the installer files :-(

I'll update my version of the action to include this if I haven't already done so.

Bob

Hi olaeblue,

I created a sister version of the action called CSV Export v2 [GH] and have uploaded it to my website. It's still got the same download page but will install alongside the version that Max publishes. Check the [GH] Custom Actions group in the from Wizard to find it (and all the other [GH] Custom actions).

Bob

Hi Bob,

I tried the CSV action in v4 and it works great. However, I wanted to filter some data to export. For example in my form below I selected the District:
[attachment=2]Screen Shot 2012-04-15 at 9.10.23 PM.png[/attachment]

Once the submit button is click, the data table will show below depending on the selection above
[attachment=1]Screen Shot 2012-04-15 at 9.12.27 PM.png[/attachment]

Once I click the "Submit" button, the export will start and able to download in CSV but I wanted to export only data from the table depending on "District" selection above but right now it is exporting all the data in the table (district highlighted) like below. What I wanted to accomplish is to export only the Honolulu (East) district.
[attachment=0]Screen Shot 2012-04-15 at 9.19.51 PM.png[/attachment]

Is there a way I can filter the data to export depending on my selection either "District" or "School" or "Project" in the form above? Any help or suggestions pis very much appreciated? Thanks.

P.S. I really love chronoform because I can do everything in my form. Just need to get the right code. Thanks for creating this.

Hi jmarian1,

You can't have a dynamic WHERE result* with the current version of the CSV Export [GH] action.

BUT I do have a v2 of the action here that will let you use the {input_name} syntax in the WHERE box. So you could use a Custom code action to build your where clause and add it to the form data array as say $form->data['where'] then use {where} in the action WHERE box.

<?php
switch( $form->data['search'] ) {
  case 'district':
    $form->data['where'] = "`district` = '{$form->data['district_name']}'";
    break;
  case 'school':
    $form->data['where'] = "`school` = '{$form->data['school_name']}'";
    break;
}
?>

Bob

I'm sorry Bob but where can I put the custom code? Is it in my show data table (include it in my code) where it shows all data selected or in my export event? Sorry I didn't understand that good. Thanks.

Hi jmarian1,

In a Custom Code action just before the CSV Export v2 [GH] action.

Bob

Hello Bob, I'm coming here after the checkbox group storage is fixed now 😶

I downloaded your action which is again excactly what I'm trying to do !! 😀

but... I'm working in french, and the accents are not ok in the csv (like "rue pré des basses" instead of rue pré des basses) is there a way to change the encoding ? my database is in utf8_general_ci

Thanks a lot for all your work and help!!

wydo

Hi wydo,

Do you see the problems in the CSV file or only after you import it into Excel?

Bob

yesss you're right ! only in excel.... but the weird thing is that I don't come to an import popup when I open it in excel. So I have to go to Data -> convert (not sure of the translation) and there I cannot choose the encoding of the file... 😑

Hi wydo,

Please see this post for a couple of ways to import and get to set the Character set.

Bob

Hi Bob,

I tried your suggestion but somehow there is no data show or 0 records found. I put the code you suggested in the post in the same event where the CSV export located and before it but still no record.
[attachment=2]Screen Shot 2012-04-17 at 6.38.35 PM.png[/attachment]
In the custom code I have the code below:

<?php
switch( $form->data['search'] ) {
  case 'district':
    $form->data['where'] = "`district` = '{$form->data['cc_district']}'";
    break;
  case 'school':
    $form->data['where'] = "`school` = '{$form->data['cc_schoolname']}'";
    break;
}
?>

and include the {where} in the CSV2 action:
[attachment=1]Screen Shot 2012-04-17 at 6.41.24 PM.png[/attachment]

but there is no record found and below is the debug error:
[attachment=0]Screen Shot 2012-04-17 at 6.42.40 PM.png[/attachment]

Where I did wrong? I also try to modify the code a little like below but it didn't work. Please help. Thanks.

<?php
$search = JRequest::getString('submit_3', '', 'post');
$where = JRequest::getString('where', '', 'post');
switch( $form->data['search'] ) {
  case 'District':
    $form->data['where'] = "`District` = '{$form->data['cc_district']}'";
    break;
  case 'School':
    $form->data['where'] = "`School` = '{$form->data['cc_schoolname']}'";
    break;

}
?>

Hi jmarian1,

What I wrote was

use {where} in the action WHERE box.

What you put in the WHERE box is

`where` = '{$where}'

which isn't even good MySQL. Please remove that and try putting {where} in the box instead.

Bob

Hi Bob,

I tried to change the SQL to {where} it but there is still no result. Please see debug message below:
[attachment=0]Screen Shot 2012-04-24 at 8.36.32 PM.png[/attachment]

Help please!!!

Hi Jmarian1,

There is no $form->data['where'] variable defined so the query fails.

Bob

Hi Bob,

I dont understand what you mean by "no $form->data['where'] variable defined so the query fails" knowing that I have the code below in Custom code before the CSV2[GH]

<?php
switch( $form->data['search'] ) {
  case 'district':
    $form->data['where'] = "`district` = '{$form->data['cc_district']}'";
    break;
  case 'school':
    $form->data['where'] = "`school` = '{$form->data['cc_schoolname']}'";
    break;
}
?>

Can I pm you with un/pw to check please? I really need this to work soon. Please advice. Thanks.

Hi jmarian1,

I no longer have any idea what code you have where. All I can do is look at the image you posted and see that the data array in the first part has no value set for $form->data['where'] so there is probably no value set in the query that follows.

You can contact me by PM or Skype chat if you like - remember that we are in different time-zones.

Bob

Hi Bob. thanks. I pm you. Thanks.

Hi jmarian1,

This is working now. The Where clause was OK apart from some stray extra quotes, but the data wasn't being passed from the previous form page. I added 'Data to Session' & 'Session to Data' actions to pass it and it seems to work OK now.

Bob

Hi Bob, thank you for checking. But I tried the form and it doesn't export the right data. No matter what condition I selected, the total data in the database is still the total data exported and not based on selection. I guess the {where} is passing only. Please advice. Thanks in advance.

Hi Bob. Never mind. It is working now. I removed the

`project_name` = {where}

in the SQL Where clause and change it to

{where}

only and it works. Thanks a lot! You're the best!!!! God bless!

Bob, another question. How can I include in the {where} code to be able to download all the data in the table?
This is my form with "Project" selected
[attachment=1]Screen Shot 2012-05-07 at 9.37.58 PM.png[/attachment]
and this is the code for above screen shot

<div class="ccms_form_element cfdiv_radio" id="cc_radio_select_container_div"><input type="hidden" name="cc_radio_select" value="" alt="ghost" />
    <input type="radio" name="cc_radio_select" id="cc_radio_select_district" title="" value="District" class="validate['required']" onClick="CCshowHide(this.name);" checked="checked" />
      <label for="cc_radio_select_district">By District</label>
      <input type="radio" name="cc_radio_select" id="cc_radio_select_school" title="" value="School" class="validate['required']" onClick="CCshowHide(this.name);" />
      <label for="cc_radio_select_school">By School</label>
      <input type="radio" name="cc_radio_select" id="cc_radio_select_project" title="" value="Project" class="validate['required']" onClick="CCshowHide(this.name);" />
      <label for="cc_radio_select_project">By Project</label>
  <div id="error-message-cc_radio_select"></div></div>

Then, Once you click the submit button will show all data from the database table like this
[attachment=0]Screen Shot 2012-05-07 at 9.39.37 PM.png[/attachment]
Clicking the "Submit" button with the selection above supposed to export all the data from the database table but somehow with the {where} condition code below, the project is not included. How can I include the Project radio button to export all data once the Submit button is submitted together with the code below?

Please advice. Thanks.

This is the code to export CSV with condition for District and School but what about the Project radio button?

<?php
//$where = JRequest::getString('where', '', 'post');
/* echo'<div>$form->data[cc_radio_select]: '.print_r($form->data['cc_radio_select'], true).'</div>'; */
switch( $form->data['cc_radio_select'] ) {
  case 'District':
    $form->data['where'] = "`district` = '{$form->data['cc_district']}'";
    break;
  case 'School':
    $form->data['where'] = "`SchoolName` = '{$form->data['cc_schoolname']}'";
    break;
  case 'Project':
    $form->data['where'] = "`Project` = ''";
    break;

}
?>

Hi jmarian1 ,

You need to create a WHERE clause that is always true, for example WHERE 1 = 1

Bob

Thanks a million times Bob. I got it working. Whew! I am officially finish in my forms. Thanks for your help!

Hi jmarian1,

Well done :-)

Bob

Hi all!
I have installed the action and it works great.
is it possible on the mail action to attach the generated CSV file?
if yes, i'm very pleased if you give me an example

kindly regards
Robi

Hi Robi,

Hmmm, the action is included in the current release so you shouldn't have needed to install it :-( (Though I do have a slightly updated v2).

You can access the file path after the CSV export action and you need then to pass this to the Email action. If I remember correctly it's fiddly to do this with the standard Email action which is only really intended to handle uploaded files. If you add the file path to the $form->data array as, say $form->data['export_file_path'] then my Custom Email [GH] action makes it easy to attach the file by putting {export_file_path} in the Files box.

Bob