How to make a form to export some table to CSV file

Put the code below in a new form!
notes:
replace jos_tablename with your real table name!

<?php
global $mainframe;
	$database =& JFactory::getDBO();

	include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
	//echo $_POST['formid'];
	/*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
	$database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
	$formname = $database->loadResult();*/
	
	$tablename = 'jos_tablename';
	$tables = array( $tablename );
 	$result = $database->getTableFields( $tables );
	$table_fields = array_keys($result[$tablename]);
	
	$database->setQuery( "SELECT * FROM ".$tablename."" );
	$datarows = $database->loadObjectList();
	
	$titcol = 0;
	foreach($table_fields as $table_field){
		if($titcol){$csvline .=",";}
		$csvline .= $table_field;
		$titcol++;
	}
	$csvline .="\n";
			
	$datacol = 0;
	$rowcount = 1;
	foreach($datarows as $datarow){
		foreach($table_fields as $table_field){
			if($datacol){$csvline .=",";}
			$csvline .= '"'.addslashes($datarow->$table_field).'"';
			$datacol++;
		}
		$csvline .="\n";
		$datacol = 0;
		$rowcount++;
	}
	
	if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "Opera";
	}
	elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "IE";
	} else {
		$UserBrowser = '';
	}
	$mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
	@ob_end_clean();
	ob_start();

	header('Content-Type: ' . $mime_type);
	header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

	if ($UserBrowser == 'IE') {
		header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
	}
	else {
		header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Pragma: no-cache');
	}
	print $csvline;
	exit();
?>

Hi Max,

Works perfectly. It exports all the records from the table.
But how about if I need to export selected records only.

I have two form in my chronoconectivity:-

1, CMCycle, which displays all the "entry dates" of form submission
2, CMCycle_fulldetails, upon clicking on any entry date from CMCycle form it displays full data of that specific selected date.

A button or link to get the results on my second page (CMCycle_fulldetails) would be great🙂

Can you please help me with this?

Regards
CS

Hi,

You can make a link to the backup form! remember the code needs to go into its OWN form!

edit this line of code to select whatever records:

$database->setQuery( "SELECT * FROM ".$tablename."" );

Put the code below in a new form!

What this means? Where in the form i put this code?
Hu Ho 🙂
I think I am a dummy.

Greetings.

Hi richyeiv,

click "new" in the "forms manager" and go to "form code" tab and in the "HTML code box" add the code, save!

Regards
Max

Hi Max,

I know that I can edit the query in the export to CSV script to select specific records from my database, but is there a way for my users (in front end) to download records based on criteria that THEY specify. For example, if there are categories a, b, c, d, e, f, g, etc and someone wants only a and b.

I thought maybe if there is a way for them to download the records that they are viewing in Chrono Connectivity based on the filter or search results...if that is do able, that would be the best solution for me. Let me know if you need any additional info.

Edit: Forgot to ask...is there a way to not include the UID, user ID and those other 2 fields that are created automatically in the DB table by Chrono forms. I don't want to delete them from the table because they seem to be useful for certain things. Still, I don't want my users to get those fields in the CSV file they download.

Thanks,

Daniel

Hi Daniel,

No automatic way to do this but it's not too hard to build a form with a little search interface that will deliver a CSV or Excel file when you click submit.

Bob

Hi Bob,

When you say "search interface", would that be the same search that is used in the header in Chrono Connectivity? If so, are there any changes to the code (either the search code or the export script) that would have to be made (considering I have little to no knowledge about any code 😛 )?

Also, would you happen to know how to exclude certain fields from being exported? Or is my only option deleting them from the database and just not using those fields?

Thank you very much for your help! 😀

Hi Daniel,

a search interface will be some form fields to take input and change the result CSV as you need, you can make this changes through PHP code, if you dont have any experience with any programming language then this may be a bit hard, but you may start reading few tutorial about PHP and test it, PHP is one of the easiest, at least the part you will deal with!

Regards
Max

Thanks for the quick reply.

I think I'll leave this for another time since I don't really have time to study PHP right now (although I really would like to someday).

Ps: Max, I think I saw you say in a post that you were going to include RSS features in the next Chrono Connectivity release...is that right? If so, will each new record in the CC table be sent via the RSS feed to the user or will it be something else...?

Thanks again! 🙂

No problems, regarding the RSS, no idea about this now, will think better when I'm doing it, if you have any suggestions then please don't hesitate to post them in the suggestions forums there because this is what I'm going to check later!🙂

Regards
Max

Hi There🙂
I used this script to export some data to a csvfile, but i have special characters in this form, so the script exports a Ü like this: Lieferantenrückgaben

I have to use special Characters, because we want to collect data for an external database and there all data is written with special characters.
Somebody have an Idea? [EDIT]: Excel interpretes it like that the csv ist correct, i will just change to ue...

But this Question is still active:
Furthermore... is there a way to export direct in a xls Format... ?

THX Jokes

Hi Jokes,

the code here is copied from the admin file of Chronoforms with few changes, you may copy the excel backup code from the amdin file too and do few changes to get it working!

Regards
Max

Awesome bit of code!

I was wondering if we could adapt it to take the data from two different tables, say 'os_comprofiler_fields' and 'jos_content'

The join fields are 'jos_content_created_by' and 'jos_comprofiler_fields_fieldid'

the query would go something like the below but not sure of the syntax to use!

Something like..... $database->setQuery( "SELECT * FROM ".jos_content." WHERE "jos_content_created_by"="jos_comprofiler_fields_fieldid"" );

Hi futureit,

I don't knwo what the actual values are to use here but the quoting is all wrong. MySQL uses backticks `` for column and table names and single quotes '' for strings. If possible do your match on the user_id fields in the two tables.

Bob

$query = "SELECT * 
  FROM `#__content` AS c
  LEFT JOIN `#__comprofiler_fields` AS f ON c.`created_by` = f.`fieldid` ;
";

Thanks but I have tried a billion configs but I still can't get the right data😟 (I come from a MS access background... and that was quite a while ago so finding this a bit tricky) Here is the full picture...

I want to get the below into one line per user in the .csv

A. Certain fields from jos_comprofiler e.g. user_id,firstname,lastname,avatar,etc,etc
B. and also 3 fields (for each user from jos_comprofiler) from jos_content e.g. created_by, fulltext,created
The id fields to join created_by = user_id

    <?php
    global $mainframe;
       $database =& JFactory::getDBO();

       include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
       $formname = $database->loadResult();*/
       
       $tablename = 'jos_comprofiler';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);

$query = "SELECT *
  FROM `#__content` AS c
  LEFT JOIN `#__comprofiler` AS f ON c.`created_by` = f.`fieldid` ;
";
       
       $database->setQuery( "SELECT * FROM ".$tablename."");
       $datarows = $database->loadObjectList();
       
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";
             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }
       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

or can I pay you to code it for me?

Hi Bob, Max or anyone else with good advice for the coding challenged

I have used the mult-page plugin to make one large database from 3 forms. When the last page of the multi-page form is submitted, the manager gets an email with the name and email address of the person who submitted.

For each new record:
The manager has to print two forms that are different from each other and formatted differently than the input forms. Some of the information on the two forms will be the same and some will be different.
The manager has to download data into two different Excel files. Some of the information downloaded into the different Excel files will be the same and some will be different.

So far
Using the code from this forum, I have made a form that lets the manager download data for all the records.

I have the two printable profile forms, one for each of the forms the manager has to fill out. The profile forms use the Target field name cf_id and 'Request' parameter name last.

To fill out a profile page for a record he wants, the manager downloads the entire database, and finds the cf_id for the record he needs and types it at the end of the profile page url.

I would like to find a better way to do this.

For each form that the manager needs to print, I would like give the manager a front-end form that only he can access that asks him for something unique like the email address of the record he needs.
When he clicks on submit he gets sent to the profile page he needs and the csv for that record is downloaded

So I am thinking that I need to
A make a request form where the manager inputs the email of the record he is looking for.
On Submit - before email I put code that downloads the csv for the record he needs.
On Submit - after email I either put the printable form with the fields filled with the data from the record he has requested or I send him to the right profile page.

Is this possible or is this a job for ChronoConnectivity?

I am stuck on two issues:
How to download the csv of the requested record only
How and where to put in the query to get the data from the requested record into the profile page or the On Submit After Email form.

I have searched the forums and have found similar problems but not a solution i can understand.
I admit I am fairly new to all forms of coding but I am learning as I go so be kind.

Here is what I've tried in my attempt to get the csv of one record only.

Form HTML - manager puts in the email address of the record he is looking for in the "this_email" field and submits:

On Submit Before Email - change field name "this_email" to variable $email and put it in the SELECT command of the script presented at the beginning of this forum.

I made the following changes to the script
added this- $email = JRequest::getString('this_email', '', 'post');
changed this - $database->setQuery( "SELECT recordtime, email, Contact FROM ".$tablename."WHERE email=".$email."" );

It didn't work.

Any advice would be appreciated.
Thanks a billion once again.

Hi QueenTut,

Part of the proble at least is that your MySQL query isn't correctly quoted - string values must be quoted in single quotes:

$query = "
  SELECT `recordtime`, `email`, `Contact` 
    FROM `$tablename` 
    WHERE `email` = '$email' 
";
$database->setQuery($query);

Bob

hy there!

i've pased the code and if i would like to save the changes, it's not possible because the (pased) script runs and asks me for open or download the file...

i use ie 8 and firefox 3.013 and windows xp
chrono contact 3.1 RC5.5

[attachment=0]screenshot.gif[/attachment]

Hi kollo,

I think this is the result of a bug/feature in the latest release where it evaluates the Form HTML on save. It's a complete pain in the neck. Max is aware but I haven't seen a fix yet.

Bob

thanks for your quick reply!!! and many thanks for this great component!!

if i see it right, this "bug" is not there in older versions, right? where can i get an "older" version without this bug?

perhaps this is unneesesary for my problem, because i can't solve it this way. what would i do: i got a form for input article data. model, ean-number, weight, size etc. (in different fields for each spec). after submitting / saving (this works wonderful!) i would export this data as csv. and here comes the problem: i need to export the article data as one "article description" and some fields like user_ide, date etc. seperatly. is it possible to save the email-template (includig the data)? i think this would solve my problem...

greetz
marcus

Not sure if this is useful advice for you but I ended up making up two forms. One to collect and save data. The other is a profile form that uses some of the data from the database to fill out a printable document. At the top of the profile form (in a hidden class) I use the php coding for 'getuser' to input the other fields that I only want to save to a csv. I created a new database table to collect the data from the 'get user' fields and then used Max's coding from this forum in the "on submit - after email" to download the csv of only those fields. (coding works fine there)
I may not be making myself clear because I'm supposed to be doing something else right now but if this technique sounds useful to you I can elaborate later.
Cheers.

Hi kollo,

QueenTut's approach works OK. I'd probably just hand write the csv code into the OnSubmit after box and export from there. The code is pretty simple.

Collate the data into an array $data_array with one entry per field.

<?php
$fp = @fopen($path.$filename, "w");
fputcsv($fp, $data_array, $delimiter, $quote);
fclose($fp);
?>

If you have multiple records to export then repeat the fputcsv line.
Bob

Hello again :-)

Thanks a lot for reply!! But i must tell you, that i use the chrono component since yesterday - and so i'm a little bit slow in understanding how all the settings play together.

i found an older version of chronoforms (3.1_RC5.1) on my pc an installed it - the coding from max works fine, but didn't solve my problem.

i thought about it the last hour and now im sure that it would be the best, if i could somehow save the email-template (with data) in the database. so it would be possible, that i "design" my article description with the editor as email template and the exported data would be "ready" to import in my lokal software. (i think my english is bad :-( - for my excuse, i'm from germany)

ok - i would sum up what i intent:

1. create a form for collecting product specs <-- solved, no problem
2. save the data in the database <-- solved, thanks chronoform!!
3. email the data in a designed template <-- solved, works fine!

4. the saved data includes fields like product_id, manufacturers_name, color, size, etc.
in the csv export file i need the data like this example:

"uid";"user_name";"user_email";"manufacturers_name";"product_id";"<table><tr><td>Manufacturer</td><td>database_entry->manufacturers_name</td></tr><tr><td>Color</td><td>database_entry-color</td></tr><tr><td>Size</td><td>database_entry-size</td></tr></table>";\n

the last entry (with the table tags) in the example should be the email template including data.

5. in the next step all exported records should "unpublished" - so that the next export includes only "new" data.

PS: my PHP and mySQL is not good. i understand the functions, but can't code things. so i think it would be the best, if i pay for the solution if there is one...

Hi kollo,

If you have Max's code working then this should be fairly simple but you are going to need some PHP to process each row of the results from the database and convert them into the format you want to export.

Bob

Has the issue with processing of Max's code preventing save of form been resolved?

Hi coffeej,

The bug is still there but we have a fix: add

<?php
if ( !$mainframe->isSite() ) return;
?>

to any code box that causes a problem.

Bob

Yep... worked beautifully.
Perfect.

GreyHead is so great, Thanks for your support, i hope that you still support more and more effectively.

It would be nice to have the table being exported in the frontend of the site this way.

Unfortunately I got the following error when I clicked the link in the frontend (which is linked to the 'form', which contains the code) given in this thread:
Parse error: syntax error, unexpected T_CLASS in ***/public_html/components/com_chronocontact/chronocontact.html.php(83) : eval()'d code on line 56

*** = edited

I changed the jos_table thing to the correct one. Is there a way to solve this error?

Is it possible to let it export to an excel file, by changing al the csv's to xls's?

Thanks in advance,

Marenka

Hi Marenka,

Which version of ChronoForms are you using? I can't relate the Error message to line 83 of the file in the current version.

Please will you post the code that you are using?

You can't change from a .csv. version to an .xls version by changing the suffixes in the code.

Bob

Hello Greyhead,

sorry for causing so much trouble. Chronoforms works just fine, only those little scripted extra's are really not coöperating with my site. Hehe.

The version I'm currently using is 2.5 J1.5 RC3.1.

The code I used is:

    <?php
    global $mainframe;
       $database =& JFactory::getDBO();

       include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
       $formname = $database->loadResult();*/
       
       $tablename = 'oywn_Reserveren';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";
             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }
       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

I checked the database prefix in the Global Configuration in the backend, and I saw it was set on oywn_. The name of the table in question is Reserveren.

Additional information: #1, Form ID = 1

It's a pity this code exports the the table to csv file only. Is it possible to export it to .xls in the first place?

Thank you for your time.

With kind regards,

Marenka

Hi Marenka,

That's quite an old version, it might help to update to the current release. ChronoForms v3.1 RC5.5.

Line 56 is

header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');

I don't see anythign wrong with that unless possible there's a problem with $tablename.

I think that you probably want to add debugging code to let you see exactly what is happening.

It's equally possible to add an export to Excel to a form in the front-end. I think it quite likley that you will find the code in the forums here (this thread is mostly about CSV export).

Bob

Very nice 😀

I just changed some values to make it export to excel, but don't know how it works with the IE vs Opera check. Beside, it gives an error when opening the file, but it works 😀

Just tried it a bit more, the error is only in Office 2007 not in 2003.

Error: The file you are trying to open, 'filename.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

You only get the error, but you can ignore it and just continue 😀

<?php
if ( !$mainframe->isSite() ) return;

       global $mainframe;
       $database =& JFactory::getDBO();

       include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
       $formname = $database->loadResult();*/
       
       $tablename = 'jos_chronoforms_yourtable';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .="\t";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";
             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .="\t";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }
       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       //Don't now if theres a difference here, so just used the same
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/x-msdownload' : 'application/x-msdownload';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.xls"');
          //also dont know if this needs to be on for XLS
          //header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.xls"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

Hi,

Yes, you are trying to generate XLS file with the excel write library, its a bit old and doesn't work well with the latest versions of MS Excel, latest versions work better with XML representation of files, but this will not be ready before the next version

Or for now you may try the normal CSV approach🙂

Regards,
Max

This was just what I was looking for. I searched Google for how to do this exact thing but was getting theory a lot of times and not exactly how to do it. I am pretty good with Html but needed this code to help me on a project I was working on to export tables to CSV files. This worked like a charm.

Thanks again for the help.

Hi Jokes,

the code here is copied from the admin file of Chronoforms with few changes, you may copy the excel backup code from the amdin file too and do few changes to get it working!

Regards
Max

The CSV expoert facility is really nice to have!

Hi, I did the following but whenever I try saving or applying, the code seems to execute and it tries to download the file instead of saving the code.

Hi richyeiv,

click "new" in the "forms manager" and go to "form code" tab and in the "HTML code box" add the code, save!

Regards
Max

[attachment=0]ScreenShot001.jpg[/attachment]

Edit: For

got to ask...is there a way to not include the UID, user ID and those other 2 fields that are created automatically in the DB table by Chrono forms. I don't want to delete them from the table because they seem to be useful for certain things. Still, I don't want my users to get those fields in the CSV file they download.

I got this right by using the following command (where each number represents the fields I want to drop):

unset($table_fields[0],$table_fields[1],$table_fields[2],$table_fields[3],$table_fields[4],$table_fields[7]);

Just after

$table_fields = array_keys($result[$tablename]);

I then also modified

$database->setQuery( "SELECT * FROM ".$tablename."" );

Replacing "*" with the fields that I wanted.

Hi rcadmin,

There is a tweak posted on the forums by Bob to fix the immediate admin code execution issue OR you may contact me through the "Contact us" page to get a patch for that.

Regards,
Max

Hi, I couldn't find Bob's patch so I've requested the details via the "Contact Us" link.

Thanks
😛

I guess the development team was too bust screwing up our toolbar with the Chrono Engine rather than working on something that people actually wanted.

Hi,

The 'patch' is simple to add one line before any PHP in the Form HTML:

<?php
if ( !$mainframe->isSite() ) { return; }
// continue PHP here
?>

Bob

Hi

the code for the output is it put in a chronoform or in a connectivity form ? if in an connect.form, in which field do i place it ?

greetings
Stefan

hi

i guess it in a chronoform. i saw this :
There is a tweak posted on the forums by Bob to fix the immediate admin code execution issue OR you may contact me through the "Contact us" page to get a patch for that.

i'm experiencing this also, where can i find the tweak ?

Hi, the code goes into a Chronoform "Form HTML" of the "Form Code" tab.

You are creating a form that when accessed, starts downloading the csv. The way you access it is by creating a link to the link that is shown on the "Forms Manager page"

ok, thanks. Is there a way to make a form which make a selective CSV output instead of the whole dbase ?

I am by no means very good at this (but given all the help that the good people on this forum have given me, I'd like to try and put something back 😀 ) that said here is how I went about it:

There is probably a better way to do this but I used the UNSET command (see in my code below) to remove the fields that I didn't want.

My code that I used

    <?php
    global $mainframe;
       $database =& JFactory::getDBO();

       include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
       $formname = $database->loadResult();*/
       
       $tablename = 'jos_chronoforms_form_Year8To9_options';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
unset($table_fields[0],$table_fields[1],$table_fields[2],$table_fields[3],$table_fields[4],$table_fields[4],$table_fields[8]);
         
       $database->setQuery( "SELECT Surname,Firstname,Username,Art,Drama,Music,ESOLa,Numeracya,Moari,French,Japanese,ESOLb,Literacy,Numeracyb,Technology,FoodTechnology,Graphics,ESOLc,Numeracyc FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";      
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

thxns, i will look into into it. it's better to loose some fiels indeed.

hi

it works great. How can i change the output order of the fields ? And second.. how can i make a selection bases on one field (wedstrijden_id) with a dropdown field

<?php
if ( !$mainframe->isSite() ) { return; }
// continue PHP here
?>


<?php
    global $mainframe;
       $database =& JFactory::getDBO();

       include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
       $formname = $database->loadResult();*/
       
       $tablename = 'inschrijvingen';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
unset($table_fields[0],$table_fields[2],$table_fields[17]);
         
       $database->setQuery( "SELECT licentienummer, geboortejaar, naam, geslacht, categorie, onderdeel1, pronderdeel1, onderdeel2, pronderdeel2,onderdeel3,pronderdeel3,onderdeel4,pronderdeel4,startnummer,wedstrijd FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";      
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=",";}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

Hi stefandeblock,

Here's a more compact version of the code to export a CSV file. In this case I'm assuming that you have a form that resturns a value for 'westrijd' and this code then goes into one of the OnSubmit Code boxes

<?php
$westrijd = JRequest::getString('westrijd', '', 'post');

$field_array = array(
    'Licentie Nummer' => 'licentienummer',
    'Geboorte Jaar' => 'geboortejaar',
    'Naam' => 'naam',
    'Geslacht' => 'geslacht',
    'Categorie' => 'categorie',
    'Onder Deel 1' => 'onderdeel1',
    'Pronder Deel 1' => 'pronderdeel1',
    'Onder Deel 2' => 'onderdeel2',
    'Pronder Deel 2' => 'pronderdeel2',
    'Onder Deel 3' => 'onderdeel3',
    'Pronder Deel 3' => 'pronderdeel3',
    'Onder Deel 4' => 'onderdeel4',
    'Pronder Deel 4' => 'pronderdeel4',
    'Start Nummer' => 'startnummer',
    'Westrijd' => 'westrijd');

$title_array = $column_array = array();
foreach ( $field_array as $k => $v ) {
    $title_array[] = $k;
    $column_array[] = "`$v`";
}
$columns = implode(', ', $column_array);

$db =& JFactory::getDBO();
$query = "
    SELECT $columns 
        FROM `inschrijvingen` 
        WHERE `westrijd` = '$westrijd' ;
";

$db->setQuery($query);
$data = $db->loadAssocList();

$file_path = J_PATH_SITE.DS.'components'.DS.'com_chronocontact'.DS.'includes'.DS.'csv_export.csv';
$file = fopen($file_path, 'w');
fputcsv($file, $title_array);
foreach ( $data as $d ) {
    fputcsv($file, $d);
}
fclose($file);

echo "<a href='$file_path'>Klik hier</a> om uw bestand te downloaden";
?>

Note: not tested and may need debugging.

There is an array near the beginning that sets the Row titles and the column names to be extracted, you can edit this to change the order and to include or exclude columns. All of the column names should be valid or the code will fail.

The data is taken from the database, then saved to a file using the PHP fputcsv function; and finally a download link is displayed.

Bob

Hi Bob


i inserted the code, and this is what i get after selecting and submitting 'wedstrijd' in the frontpage :

Warning: fopen(J_PATH_SITE/components/com_chronocontact/includes/csv_export.csv) [function.fopen]: failed to open stream: No such file or directory in /home/deb34880/domains/rkhav.com/public_html/components/com_chronocontact/libraries/customcode.php(64) : eval()'d code on line 39

Warning: fputcsv() expects parameter 1 to be resource, boolean given in /home/deb34880/domains/rkhav.com/public_html/components/com_chronocontact/libraries/customcode.php(64) : eval()'d code on line 40

Warning: fclose(): supplied argument is not a valid stream resource in /home/deb34880/domains/rkhav.com/public_html/components/com_chronocontact/libraries/customcode.php(64) : eval()'d code on line 44
Klik hier om uw bestand te downloaden

this is the form html code (which works, because it on an other form)

<div class="form_item">
<div class="form_element cf_text"> <span class="cf_text">Actuele inschrijvingen RKHAV atleten</span> </div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_text"> <span class="cf_text"></span> </div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_dropdown">
<label class="cf_label" style="width: 150px;">Wedstrijd :</label>
<select class="cf_inputbox validate-selection" id="select_2" size="1" title="selecteer wedstrijd" name="wedstrijd">
<option value="">selecteer wedstrijd</option>

<?php

$sql= mysql_query("SELECT * FROM wedstrijden")or die(mysql_error());
$nSql = mysql_num_rows($sql);

if($nSql > 0){

while($fSql = mysql_fetch_assoc($sql)){

echo '<option value="'.$fSql['wedstrijdnaam'].'">'.$fSql['wedstrijdnaam'].'</option>';

}

}

?>

</select>
</div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_text"> <span class="cf_text"></span> </div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_button">
<input value="bekijk inschrijvingen" name="button_1" type="submit" />
</div>
<div class="cfclear"> </div>
</div>

Hi stefandeblock,

I did say that it would need debugging. forgot to say that you'd need to make sure that the folder you were saving to existed. I've set the path to an components/com_chronocontact/includes folder which you probably don't have. Adjust the path definition to point to some other location.

Bob

Hi Bob,

i've made the path, etc. and the faults are gone. Excell output opens with field names , but without data. I think it's a query. I attached the txt file with the html code and the onsubmit code. i will also take a look at it. Thanks again till now for the great help, support is super!

Hi stefandeblock ,

I checked the csv code using the jos_users table and it works fine with both the names and the values saved.

One bug was that the last link line included a path instead of a URL. The code should be

. . .
fclose($file);
$file_url = JURI::base().'components/com_chronocontact/includes/csv_export.csv';
echo "<a href='$file_url'>Klik hier</a> om uw bestand te downloaden";
?>

Bob

It's really useful .thanks

Bob

can i put in a debug somewhere ? i can't find the problem why there is no output.
excell select versie 3.txt is the version which includes you're code. That's the one with the select i've.

The other file is the old version (versie 2). This is the old code wihout selection drop down, but with a complete output.

maybe if i can putin a debugcode ithelpsfixing the problem

Hi Bob,

can you spare some time to look at the files i uploaded. I can't find the problem why the one form exports the complete data, but that'swithout a selection possiblity and why the version (3) with the selection possibilty exports a file with only the field names, but without the data which matched the wedstrijdnaam field

hope, you can help

Hi stefandeblock,

I think that the files need breaking into two parts. Looking at version 2 this part goes into the Form HTML box and creates the mini=form to select the records to be exported:

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text">Actuele inschrijvingen RKHAV atleten</span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text"></span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Wedstrijd :</label>
    <select class="cf_inputbox validate-selection" id="select_2" size="1" title="selecteer wedstrijd"  name="wedstrijd">
    <option value="">selecteer wedstrijd</option>
      
<?php
if ( !$mainframe->isSite() ) { return; }

$db =& JFactory::getDBO();
$query = "
    SELECT *
		FROM `wedstrijden`
";
$db->setQuery($query);
$data = $db->loadObjectList();

if ( count($data) ) {
	foreach ( $data as $d ) {
		echo '<option value="'.$d->wedstrijdnaam.'">'.$d->wedstrijdnaam.'</option>';
	}
}
?>
    </select>    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text"></span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="bekijk inschrijvingen" name="button_1" type="submit" />
  </div>
  <div class="cfclear"> </div>
</div>

NB re-written a little to use Joomla! DB code

and this part goes into the OnSubmit After Box to generate the file for export:

<?php
$wedstrijd = JRequest::getString('wedstrijd', '', 'post');

$field_array = array(
    'Licentienummer' => 'licentienummer',
    'Geboortejaar' => 'geboortejaar',
    'Naam' => 'naam',
    'Geslacht' => 'geslacht',
    'Categorie' => 'categorie',
    'Onderdeel1' => 'onderdeel1',
    'PRonderdeel1' => 'pronderdeel1',
    'Onderdeel2' => 'onderdeel2',
    'PRonderdeel2' => 'pronderdeel2',
    'Onderdeel3' => 'onderdeel3',
    'Pronderdeel3' => 'pronderdeel3',
    'Onderdeel4' => 'onderdeel4',
    'Pronderdeel4' => 'pronderdeel4',
    'StartNummer' => 'startnummer',
    'wedstrijd' => 'wedstrijd');

$title_array = $column_array = array();
foreach ( $field_array as $k => $v ) {
    $title_array[] = $k;
    $column_array[] = "`$v`";
}
$columns = implode(', ', $column_array);
$db =& JFactory::getDBO();
$query = "
    SELECT $columns 
        FROM `inschrijvingen` 
        WHERE `wedstrijd` = '$wedstrijd' ;
";

$db->setQuery($query);
$data = $db->loadAssocList();

$file_path = 'components'.DS.'com_chronocontact'.DS.'includes'.DS.'csv_export.csv';
$file = fopen($file_path, 'w');
fputcsv($file, $title_array);
foreach ( $data as $d ) {
    fputcsv($file, $d);
}
fclose($file);
$file_url = JURI::base().'components/com_chronocontact/includes/csv_export.csv';
echo "<a href='$file_url'>Klik hier</a> om uw bestand te downloaden";
?>

Bob

Hi Bob

i would glad to buy you a beer🙂. I inserted the code you made,but it still doesn't do the trick. All seems to go well, bu in the actual export file only the field names are vissible,not the data.
You can look at it at <!-- w --><a class="postlink" href="http://www.rkhav.com">www.rkhav.com</a><!-- w --> and then select at the top 'inschrijven' and then 'test excell - test'

Well i bought you that beer🙂, and be'll glad to buty you another one when i works🙂

Hi stefandeblock,

Well it give a nice row of headings :-)

By all means email or PM me the site URL and a SuperAdmin login and I'll take a quick look.

Bob

hi, mailed you the login..

the fileyou need is inschrijvingen_excell_2

Hi stefandeblock,

I think it's working now. The drop-down to select the westrijd was returning the 'naam' - but the query needed the id. I changed the drop down to set the id as the value.

Bob

PS I left a line of Debug code in so that you can see the query being generated. I also installed the free EasySQL so that I could see the DB tables and test queries from the admin area.

PPS I forgot to say thanks for the beer :-) Proost !!

hi bob

i just tested it. i can see the debug line , but still there no data in the csv file. dit it return data in you're csv file ?

bob

did just another test. when i select a wedstrijd and i come to the moment to download/open the file, i logged in with ftp and downloaded the generated csv file. This file contains indeed the data. But the file that i open through the frontside doesn't

maybe this can be of some help

bob

me again.....it works ok (also front) hen using firefox to open the file it seems, not with explorer 8

the faults explorer 8 gives :
Foutdetails webpagina

Gebruikersagent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
Tijdstempel: Sun, 31 Oct 2010 13:07:24 UTC


Bericht: 'Class' is niet gedefinieerd
Regel: 17
Teken: 1
Code: 0
URI: http://www.rkhav.com/media/system/js/modal.js


Bericht: 'SqueezeBox' is leeg of geen object
Regel: 62
Teken: 4
Code: 0
URI: http://www.rkhav.com/component/chronocontact/?chronoformname=Inschrijvingen_excell_v2


Bericht: Deze eigenschap of methode wordt niet ondersteund door dit object
Regel: 59
Teken: 102
Code: 0
URI: http://www.rkhav.com/media/system/js/mootools.js

Hi stefandeblock,

True but nothing to do with ChronoForms this time. The squeezebox is being loaded by the Multi-media plug-in on the right hand side.

Bob

ok, strange that firefox handles the page and file right and ie 8 doesn't

me again..pfff... thanks for the great help. wel found out that saving the file does the the trick., so it works🙂 thanks again. Which part of the code must be removed to remove the debug message ? and final question... is it possible that the output is sorted on the field 'geboortejaar'(=birthyear), and then with the youngest to begin

if you fix this last one, i'll buy you anothet beer Bob!

Hi stefandeblock,

Done, added ORDER BY `geboortejaar` DESC to the query.

The Debug was the $mainframe->enqueuemessage(. . . line just after the query, now commented out.

Bob

Hello all, after reading i tried this code and alash! it worked perfectly. After this i was trying something more by joining two table by changing the codes but failure. Can anyone suggest me what the code should be?

Hi finken,

A JOIN should be OK here, please post the code you are trying so we can take a look and see if there is any obvious fix.

Bob

I think I'll leave this for another time since I don't really have time to study PHP right now (although I really would like to someday).

Ps: Max, I think I saw you say in a post that you were going to include RSS features in the next Chrono Connectivity release...is that right? If so, will each new record in the CC table be sent via the RSS feed to the user or will it be something else...?

Thanks again!:)

Hi all,
first excuse me for resume this topic but I didn't want to open another similar.
Here is my problem:
I used the code written by Max and it works but, I need, instead of prompting the download of the csv, to write the csv on a folder on the web server.
Can you help me?
Thanks in advance

Livio

Hi Livio,

Please see this post from earlier in this topic.

Bob

Hi Bob thanks for the reply but my problems is that I need to write the file using some variables.
I made a php script without using joomla routines and it works

<?php
	$db = mysql_connect ('db', 'username', 'pass') or 
		die ('Unable to connect');
	mysql_select_db('dbname', $db) or die(mysql_error($db));
	//create the variables
	$tablename = jos_chronoforms_form_madre;
	$filename = '/' . $tablename . date("Y-m-d-H-i-s") . '.txt';
	echo $filename; //check the format of the file
	//Query for saving orders
	$query = "SELECT * FROM $tablename WHERE cf_user_id=65 INTO OUTFILE '$filename'";
	$result = mysql_query($query);
?> 

Please note that in the select statement I put the condition for a known userid.
After this trial I modified the script to fit joomla standard and in this case the system doesn't write the output.

<?php
    global $mainframe;
       $db=& JFactory::getDBO();
       $user=& JFactory::getUser();
	//create the variables
	$tablename = jos_chronoforms_form_madre;
	$filename = '/' . $user->id . $tablename . date("Y-m-d-H-i-s") . '.csv';
        echo $filename;
	//Query for saving orders
	$query = "SELECT * FROM jos_chronoforms_form_madre WHERE cf_user_id=('".$user->id."') INTO OUTFILE '$filename'";

$db->setQuery($query);
$result = $db->query();
?> 

Where am I wrong?

thank you in advance

Livio🙂

Hi Livio,

The solution I pointed you to allows you to use PHP variables.

In your examples I think that

$tablename = jos_chronoforms_form_madre;

needs to be

$tablename = "jos_chronoforms_form_madre";

I don't see any other obvious problems.

Bob

Thanks Bob but it didn't work.
As you can see I put a check after that line to see if the name of the file was written correct and the output it's ok in both cases (with and without quotes).

Livio

Ok found the solution🙂
I'll post it in case anyone needs it🙂

<?php
	$db = mysql_connect ('host_db', 'db_user', 'password_db') or 
		die ('Unable to connect');
	mysql_select_db('database', $db) or die(mysql_error($db));
        $user=& JFactory::getUser();
	//Creates the variables
	$tablename = jos_chronoforms_form_madre;
	$filename = '/' . $tablename . date("Y-m-d-H-i-s") . '.txt'; //Used / at beginning of the file to tell the application to write it in the root
	//Query for saving the table
	$query = "SELECT * FROM $tablename WHERE cf_user_id=('".$user->id."') INTO OUTFILE '$filename'";
	$result = mysql_query($query);
?> 

Thanks for the time spent🙂

Livio

Hi Livio,

The unquoted text string gives a PHP Warning, then treats the string as an undeclared constant. Not good code but it will work in this case.

The query appears to run and gives a OK result . . . but I can find no trace of the file created. If I remove the \ from the front then it ends up in mysql/data/[dbname] folder which isn't too helpful. Nor is it a CSV file :-(

I'm afraid that you need to debug your code to find out just what is happening.

Bob

There where an error in the code that I posted🙂
change

$query = "SELECT * FROM $tablename WHERE cf_user_id=('".$user->id."') INTO OUTFILE '$filename'";

with this

$query = "SELECT * FROM $tablename WHERE cf_user_id=$user->id INTO OUTFILE '$filename'";

and you'll find the file🙂

Thanks again for help!🙂

I am trying to export a csv file from a database table and save it in a directory named test within a subdomain called subdomainA. The test directory has 755 permissions. I'm trying to do this without using Joomla code.

My problem is that nothing happens when I run the following script. It does not generate errors but I do not see any csv files in the test directory. What am I doing wrong? I know that I am connected to the database table because I can echo it. Please advise. Thank you!

<?php              
        include 'connection.php';
       
       //Creates the variables
       $tablename = "jos_chronoforms_staff";
       $filename = '/home/username/public_html/subdomainA/test/' . $tablename . '.csv'; 

       
       //Query for saving the table       
       $query = "SELECT * INTO OUTFILE '$filename' FROM $tablename  ";

       $result = mysql_query($query);
?>

Hi cascade27,

This may help - from the MySQL docs

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host's file system.

Bob

I have a subdomain which is a secure site (https). In the Chronoforms forms manager, I created a form with the following form code. When I click on the form link in the forms manager, it asks me whether to open or save. However, when I open the csv file, it is all blank and there is no content. What am I doing wrong?

Edit: I confirmed that when I try it on my xampp localhost, it works. However, the exact same code generates only blank forms on my subdomain. FYI, the PHP version is 5.2.15 and Chronoforms v3.1 RC5.2.

<?php
if ( !$mainframe->isSite() ) { return; }

global $mainframe;
   $database =& JFactory::getDBO();

   include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
   //echo $_POST['formid'];
   /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
   $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
   $formname = $database->loadResult();*/
   
   $tablename = 'jos_chronoforms_staff';
   $tables = array( $tablename );
   $result = $database->getTableFields( $tables );
   $table_fields = array_keys($result[$tablename]);
   
   $database->setQuery( "SELECT * FROM ".$tablename."" );
   $datarows = $database->loadObjectList();
   
   $titcol = 0;
   foreach($table_fields as $table_field){
      if($titcol){$csvline .=",";}
      $csvline .= $table_field;
      $titcol++;
   }
   $csvline .="\n";
         
   $datacol = 0;
   $rowcount = 1;
   foreach($datarows as $datarow){
      foreach($table_fields as $table_field){
         if($datacol){$csvline .=",";}
         $csvline .= '"'.addslashes($datarow->$table_field).'"';
         $datacol++;
      }
      $csvline .="\n";
      $datacol = 0;
      $rowcount++;
   }
   
   if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "Opera";
   }
   elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "IE";
   } else {
      $UserBrowser = '';
   }
   $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
   @ob_end_clean();
   ob_start();

   header('Content-Type: ' . $mime_type);
   header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

   if ($UserBrowser == 'IE') {
      header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Pragma: public');
   }
   else {
      header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Pragma: no-cache');
   }
   print $csvline;
   exit();
?>

Hi cascade,

Really hard to say, I'd test the query in PHPMyAdmin and then add debug code to track what is happening chunk by chunk.

At a wild guess you have the table prefix hard-coded as jos_ and it's something different on the live site.

Bob

Hi Bob,
Thank you for the reply.

I checked the Joomla Global Configuration and verified that the database prefix is set to jos_.

I also verified that when I create a csv file using the Backup to CSV button inside the Forms Manager, it works fine. The content appears in the csv file.

I noticed that, in the code, there is the following line.

include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";

However, when I go to the directory /components/com_chronocontact/, I don't see a directory "excelwriter" nor Writer.php.

When I create a csv file by clicking on the Backup to CSV button, the file name is in a format of "Chronoforms - tablename - day_month_year.csv". I need to remove the spaces and the date from the file name. Where can I find the file to make these changes. Thank you for your help!

Hi cascade27,

The Writer.php file is in the admin files, if you are running this code from the front-end then JPATH_BASE will point to the front-end files. Make it JPATH_SITE.DS.'administrator'.DS.' . . . instead.

The file name is set in this line (repeated twice in the code you posted).

header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');

Bob

Thank you, Bob! I found the file in the admin files. I was also able to change the file name by editing the lines you pointed out. Thank you.

Now, only if I can resolve the rest of the problems... :?

Thanks for the info!

Hello,
In my export file, I have a gap between the column titles and data. The first two columns have no title, and the data that appears in this column are not synchronized with their eventual title.
please, what can i do to solve it?

Cheers
MP

I did use this code

    <?php
    if ( !$mainframe->isSite() ) { return; }
//
           global $mainframe;
           $database =& JFactory::getDBO();

           include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
           //echo $_POST['formid'];
           /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
           $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
           $formname = $database->loadResult();*/
           
           $tablename = '_mytable';
           $tables = array( $tablename );
           $result = $database->getTableFields( $tables );
           $table_fields = array_keys($result[$tablename]);
unset($table_fields[0],$table_fields[1],$table_fields[2],$table_fields[3],$table_fields[4],$table_fields[7]);
           $database->setQuery( "SELECT name FROM ".$tablename."" );
           $datarows = $database->loadObjectList();
           
           $titcol = 0;
           foreach($table_fields as $table_field){
              if($titcol){$csvline .="\t";}
              $csvline .= $table_field;
              $titcol++;
           }
           $csvline .="\n";
                 
           $datacol = 0;
           $rowcount = 1;
           foreach($datarows as $datarow){
              foreach($table_fields as $table_field){
                 if($datacol){$csvline .="\t";}
                 $csvline .= '"'.addslashes($datarow->$table_field).'"';
                 $datacol++;
              }
              $csvline .="\n";
              $datacol = 0;
              $rowcount++;
           }
           
           if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
              $UserBrowser = "Opera";
           }
           elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
              $UserBrowser = "IE";
           } else {
              $UserBrowser = '';
           }
           //Don't now if theres a difference here, so just used the same
           $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/x-msdownload' : 'application/x-msdownload';
           @ob_end_clean();
           ob_start();

           header('Content-Type: ' . $mime_type);
           header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

           if ($UserBrowser == 'IE') {
              header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.xls"');
              //also dont know if this needs to be on for XLS
              //header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
              header('Pragma: public');
           }
           else {
              header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.xls"');
              header('Pragma: no-cache');
           }
           print $csvline;
           exit();
        ?>

It gives this
[attachment=0]cap_07.jpg[/attachment]

H mister,

The code is a little hard to make sense of (i.e. it's not how I would write it). My best guess is that the unset line isn't unsetting enough values maybe [5] and [6] need unsetting too?

I also don't see how the query to get the actual data gets anything except the name column - but you seem to have several columns in the spreadsheet.

Bob

Thanks for the quick answer,
I was looking for a simple code to export data (XLS). I tried to summarize all the codes I found on this page and it was confusing because of many different talks.
Please do you have a simple one to propose to me if this one isn’t good?
How would you write that code?

I did try to set [5] and [6], still the same problem.
And when I also add all columns, still the same problem.

Thanks

Hi mister,

What are the columns in the table and which need to go into the Excel file?

Bob

Thansks again for the quick answer,
I just tried a new configuration and put the same id, that why its look the same. But still the same problem. Maybe my code is really wrong.
The columns in the table are radio0, radio1,firstname,lastname,username,address…
And I need in the Excel, radio0,radio1,firstname,lastname,username,address…

Thanks

Hi mister,

Is that all the columns in the database table - it doesn't fit with all the unset() values in your code?

Bob

Hi again,
yes for the moment.
other unset() can be deleted
Thanks

Thank!
Hi Bob !
Works perfectly. It exports all the records from the table.
But I have problem view Vietnamese language Unicode utf-8

<?php
header("Content-Type: text/html; charset=utf-8");
global $mainframe;
   $database =& JFactory::getDBO();

   include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
   //echo $_POST['formid'];
   /*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
   $database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
   $formname = $database->loadResult();*/
   
   $tablename = 'ddt_chronoforms_congviec';
   $tables = array( $tablename );
   $result = $database->getTableFields( $tables );
   $table_fields = array_keys($result[$tablename]);
   
   $database->setQuery( "SELECT * FROM ".$tablename."" );
   $datarows = $database->loadObjectList();
   
   $titcol = 0;
   foreach($table_fields as $table_field){
      if($titcol){$csvline .=",";}
      $csvline .= $table_field;
      $titcol++;
   }
   $csvline .="\n";
         
   $datacol = 0;
   $rowcount = 1;
   foreach($datarows as $datarow){
      foreach($table_fields as $table_field){
         if($datacol){$csvline .=",";}
         $csvline .= '"'.addslashes($datarow->$table_field).'"';
         $datacol++;
      }
      $csvline .="\n";
      $datacol = 0;
      $rowcount++;
   }
   
   if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "Opera";
   }
   elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "IE";
   } else {
      $UserBrowser = '';
   }
   $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
   @ob_end_clean();
   ob_start();

   header('Content-Type: ' . $mime_type);
   header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

   if ($UserBrowser == 'IE') {
      header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Pragma: public');
   }
   else {
      header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Pragma: no-cache');
   }
   print $csvline;
   exit();
?>

Hi lyphuong,

Is this a problem with the exported text file, or the Excel Character set? I think that there was an Excel setting mentioned earlier in this thread.

Bob

PS @mister, will reply to your post later.

Thanks Bob! quick answer
Font Excel: Arial
I have not found the cause to process the font for displaying content

This is file

Thank Bob!
The problem was solved, Setting Excel Character set: UTF-8, view good

Is this a problem with the exported text file, or the Excel Character set?

Hi lyphuong,

Excellent, thanks for letting us know.

Bob

Hi mister,

Sorry I forgot your post :-(

<?php
if ( !$mainframe->isSite() ) { return; }
global $mainframe;
$db =& JFactory::getDBO();

// this array has  'Excel column title' => 'database_column_name' 
$column_array = array (
  'Radio 0' => 'radio0', 
  'Radio 1' => 'radio1', 
  'First Name' => 'firstname', 
  'Last name' => 'lastname', 
  'Username' => 'username', 
  'Address' => 'address'
);

$select = array();
$titles = array();
$csv = array();
foreach ( $column_array as $k => $v ) {
  $select[] = $db->nameQuote($v);
  $titles[] = $k;
}
$select = implode (', ', $select);

// replace _mytable with your table name
$query = "
  SELECT $select
    FROM `#__mytable` ;
 ";
$db->setQuery($query);
$data = $db->loadObjectList();
         
$csvline = "";
// build title row
$csv[] = implode("\t", $titles);

foreach( $data as $datarow ) {
  $temp = array();
  foreach( $datarow as $d ) {
    $temp[] = addslashes($d);
  }
  $csv[] = implode("\t", $temp);
}
$csvline = implode("\n", $csv);

$filename = "ChronoForms export - ".date("j_n_Y").".xls";

jimport('joomla.environment.browser');
$browser =& JBrowser::getInstance();
$UserBrowser = $browser->getBrowser();

$mime_type = 'application/x-msdownload';
@ob_end_clean();
ob_start();
header('Content-Type: ' . $mime_type);
header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
if ( $UserBrowser == 'ie' ) {
  header('Content-Disposition: inline; filename="$filename"');
  header('Pragma: public');
} else {
  header('Content-Disposition: attachment; filename="$filename"');
  header('Pragma: no-cache');
}
print $csvline;
exit();
?>

Not fully tested and may need debugging.

Bob

The code works fine. There is a little bug near $filename.

This is the correct code.

    <?php
    if ( !$mainframe->isSite() ) { return; }
    global $mainframe;
    $db =& JFactory::getDBO();

    // this array has  'Excel column title' => 'database_column_name'
    $column_array = array (
      'Radio 0' => 'radio0',
      'Radio 1' => 'radio1',
      'First Name' => 'firstname',
      'Last name' => 'lastname',
      'Username' => 'username',
      'Address' => 'address'
    );

    $select = array();
    $titles = array();
    $csv = array();
    foreach ( $column_array as $k => $v ) {
      $select[] = $db->nameQuote($v);
      $titles[] = $k;
    }
    $select = implode (', ', $select);

    // replace _mytable with your table name
    $query = "
      SELECT $select
        FROM `#__mytable` ;
    ";
    $db->setQuery($query);
    $data = $db->loadObjectList();
             
    $csvline = "";
    // build title row
    $csv[] = implode("\t", $titles);

    foreach( $data as $datarow ) {
      $temp = array();
      foreach( $datarow as $d ) {
        $temp[] = addslashes($d);
      }
      $csv[] = implode("\t", $temp);
    }
    $csvline = implode("\n", $csv);

    $filename = "ChronoForms export - ".date("j_n_Y").".xls";

    jimport('joomla.environment.browser');
    $browser =& JBrowser::getInstance();
    $UserBrowser = $browser->getBrowser();

    $mime_type = 'application/x-msdownload';
    @ob_end_clean();
    ob_start();
    header('Content-Type: ' . $mime_type);
    header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
    if ( $UserBrowser == 'ie' ) {
      header('Content-Disposition: inline; filename="'.$filename.'"');
      header('Pragma: public');
    } else {
      header('Content-Disposition: attachment; filename="'.$filename.'"');
      header('Pragma: no-cache');
    }
    print $csvline;
    exit();
    ?>

Thank you for this thread - I was easily able to do what I wanted. However, I have a problem...

Some of my text fields contain commas. I'm not exactly sure how to handle that! Any suggestions or tips?

TIA

This is the code for multitable export.

Set fiels Email the results? to Yes

The access is restricted to the administrator.

Field Form html

<?php
if ( !$mainframe->isSite() ) { return; }
global $mainframe;

// control admin login
$user =& JFactory::getUser();
if ($user->usertype<>"Super Administrator") { ?>
<div class="form_item">
  <div class="form_element cf_heading">
    <h1>Admin area it is not allowed access to unauthorized parties.</h1>
  </div>
  <div class="cfclear"> </div>
</div>

<?php } else { ?>

<div class="form_item">
  <div class="form_element cf_heading">
    <h1 class="cf_text">Exports tables to spreadsheet</h1>
  </div>
  <div class="cfclear"> </div>
</div>


<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Tabelle</label>
    <select class="cf_inputbox required" id="select_0" size="1" title=""  name="tabella">
    <option value="">Choose the table</option>
 	
<?php
 //read for dropdown

    $db =& JFactory::getDBO();         
    //$query = "SHOW TABLES LIKE '%chronoforms%'";
    $query = "SHOW TABLES ";
    $db->setQuery($query);
    $nomes = $db->loadResultArray();
print_r($nomes);
    $nome_options = "";
      $i=0;
      foreach ( $nomes as $v ) {
      $nome_options .= "<option value='".$nomes[$i]."' >".$nomes[$i]."</option>";
      $i++;
      }

     echo $nome_options;
?>      
    </select>
    <a class="tooltiplink" onclick="return false;"><img height="16" border="0" width="16" class="tooltipimg" alt="" src="components/com_chronocontact/css/images/tooltip.png"/></a>
				<div class="tooltipdiv">Table :: Choose the table</div>
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Separator fields</label>
    <select class="cf_inputbox required" id="select_1" size="1" title=""  name="separatore">
    <option value="">Choose a separator</option>
    <option value=",">Comma</option>
    <option value=";">Semicolon</option>
    <option value="|">Vertical bar</option>
    <option value="tab">Tab</option>
     
    </select>
    <a class="tooltiplink" onclick="return false;"><img height="16" border="0" width="16" class="tooltipimg" alt="" src="components/com_chronocontact/css/images/tooltip.png"/></a>
				<div class="tooltipdiv">Separator :: Choose the separator.</div>
  </div>
  <div class="cfclear"> </div>
</div>



<div class="form_item">
  <div class="form_element cf_button">
    <input value="Submit" name="button_2" type="submit" /><input type="reset" name="reset" value="Reset"/>
  </div>
  <div class="cfclear"> </div>
</div>
<?php } ?>

Field On Submit code - Before sending email

<?php
if ( !$mainframe->isSite() ) { return; }
// continue PHP here
    global $mainframe;
       $database =& JFactory::getDBO();
       include_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_chronocontact'.DS.'excelwriter'.DS."Writer.php";

       $separatore= JRequest::getVar('separatore','','post');
if( $separatore=="tab") {$separatore="\t";}
       $tablename = JRequest::getVar('tabella','','post');
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       //unset($table_fields[0],$table_fields[2],$table_fields[17]);
         
       $database->setQuery( "SELECT * FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=$separatore;}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";      
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             if($datacol){$csvline .=$separatore;}
             $csvline .= '"'.addslashes($datarow->$table_field).'"';
             $datacol++;
          }
          $csvline .="\n";
          $datacol = 0;
          $rowcount++;
       }       
       if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "Opera";
       }
       elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
          $UserBrowser = "IE";
       } else {
          $UserBrowser = '';
       }
       $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
       @ob_end_clean();
       ob_start();

       header('Content-Type: ' . $mime_type);
       header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

       if ($UserBrowser == 'IE') {
          header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("d_m_Y").'.csv"');
          header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
          header('Pragma: public');
       }
       else {
          header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("d_m_Y").'.csv"');
          header('Pragma: no-cache');
       }
       print $csvline;
       exit();
    ?>

By changing these lines, you can restrict access to tables of chronoforms

    //$query = "SHOW TABLES LIKE '%chronoforms%'";
    $query = "SHOW TABLES ";

Hi Vales,

Very nice, thank you.

I've modified your code a little and put it into a form backup file. The biggest visible change is that my version saves the exported file to the components/com_chronocontact/exports folder and then displays a link to download the file. Behind the scenes it is also using the PHP fputcsv() command which simplifies the code a little.

Bob

To use the form, unzip the attachment and use the Restore Forms icon in the ChronoForms Forms Manager toolbar to load the form.

Notes:
1) This form will only work with ChronoForms v3
2) You need to be logged into the front-end as an Admin or SuperAdmin to see the form.

Later - there was a debug line left in the first version for a few minutes, now fixed.

Hello,

I'm a chronoform newbie...I tried to use your form to export a simple database fill up by chronoform with no success.
I'm using V 4.0 RC1.9, joomla 1.5.23 and Chrome as my browser.

I just create a new form fill up the custom code field change the data base table name, but nothing happens in the frontend. Not even an error message. In fact if I mess up the PHP code, nothing happen...

Do I have to set up something on the form ?
Thanks for your help.
Denis.

Hi Denis,

Please read the Notes "This form will only work with ChronoForms v3". You are using ChronoForms v4.

There is a custom action for ChronoForms v4 that you can get here

Bob

Thanks bob, but I used the code found on the first msg of this thread, and it have no mention of compatibility problems with v4.

I tried to download the file @ http://greyhead.net/how-to-docs/chronoforms-v4-actions, but I've got an error in the unzipping process (see pict).

Thanks.
Denis

Hi Denis,

Sorry, the beginning of this thread was long before version 4 was released. I expected that you'd looked at the more recent posts.

The unzipping seems to be a not infrequent Mac problem. I've checked the files here and StuffitExpander and my other zip utilities all open it OK :-( See this thread (or many others) for some suggestions.

Bob

Stuffit expander tell me that the archive structure is damaged...

Have you check with the file available from the web or from your HD ?

Hi Denis,

Both. From Googling around I suspect that it may be a Mac bug linked to the file paths in some way :-(

Bob

Would you be kind to email me the files ?

dgillier "at" bayoo.tv

thanks in advance.

Sorry about that....

Hi Denis,

Done.

Bob

To use the form, unzip the attachment and use the Restore Forms icon in the ChronoForms Forms Manager toolbar to load the form.

Notes:
1) This form will only work with ChronoForms v3
2) You need to be logged into the front-end as an Admin or SuperAdmin to see the form.

Later - there was a debug line left in the first version for a few minutes, now fixed.

Questions:

Question 1 - Can a CSV file be emailed as an attachment? Or is the only way to get the csv file is to actively login to download it?

Question 2 - WE are doing it for a employment application that may have 60 responses a day - so provided it can't be emailed (if that is NOT an option) - how will each csv file be downloaded? Does the script referred to above show each CSV file?

Thanks!

Hi cb1,

Do you mean a 'CSV' file - which is a backup file for the database table?

Or do you mean a 'CV' file which is an uploaded document with the applicant's work history?

If it's the CV file then it can be attached to the email.

Bob

Hi cb1,

Do you mean a 'CSV' file - which is a backup file for the database table?

Or do you mean a 'CV' file which is an uploaded document with the applicant's work history?

If it's the CV file then it can be attached to the email.

Bob

CSV file of database results

On page 1 of this thread it says

"Put the code below in a new form!
notes: replace jos_tablename with your real table name!"

Which seems to be the first step in outputting a CSV file.

Question: WHERE/HOW do you create a new table? I guess I don't understand Step 1 on the process.

Hi cb1,

There's a Create Table icon in the Forms Manager toolbar; and yes, in order to export data from the daatbase you need to save it to the database.

If you just want to turn your form results into a single row CSV file then you can use a Custom action (in CFv4) or the On Submit code boxes (in CFv3) and add the PHP to do that.

Bob

Hey!

Here is what I have:

1 - Form that asks for the information. It is called "NEW_HR_Application"

2 - I created a table for it and connected it to the table.

3 - I have the file I downloaded called "csv_export_gh"

4 - I also have this table: "form_downLoad_Hr_application" in which I replaced the jos tablename in this line of code

$tablename = 'jos_chronoforms_NEW_HR_Application';

"

And it doesn't seem to work. So - not being familiar with the code - I don't know where or how to start debugging.

IF I follow the link next to the "form_downLoad_Hr_application" form. It does let me download a csv - but it doesn't have information in it from the form.

Suggestions as to where to start?

Hi cb1,

I have great difficulty understanding how this makes any sense but . . .

If you put a CSV Export action after the DB Save then you could get the new record ID and use that in the WHERE query of the CSV Export to create a mini-file. Save it to a file and then use the URL or path info to insert a link into the email or attach the file.

If you need to hand code it then the main bit of code is this one

			$file = fopen($save_path.$file_name, 'w');
			if ( $file === false ) {
				$form->validation_errors['CSV Export'] = 'Unable to open the file.';
				return;
			}

			//fputcsv($file, $titles);
			// add data rows to the file
			foreach ( $data as $d ) {
				fputcsv($file, $d, $delimiter, $enclosure);
			}
			fclose($file);

it will needd editing to meet your needs but basically it puts the content of the $data array into a CSV file.

Bob

I'm sorry - forget the "attach to an email" aspect altogether. Client doesn't want that.

All I want to do is this:

1 - Have someone fill out the application.
2 - When they hit "Submit" - I want the results to go to a CSV (excell spreadsheet compatible file.)
3 - I have the application ready - but when I go to fill it out - it adds a line in the csv - but doesn't add the information I ask for in the application.

So that is where I am stuck. I have the application linked to the database table - but not adding the information I have asked for.

I no longer need to attach it to an email.

Hi cb1,

Why not just export the whole list from the database when they want it??

If you have to export each row to a file then use the CSV Export action with the where clause set to the last record.

Bob

Hi cb1,

Why not just export the whole list from the database when they want it??

If you have to export each row to a file then use the CSV Export action with the where clause set to the last record.

Bob

Here's what the client wants to do - then you give me your opinion as to how to approach my side.... this is the first time I HAVE EVER messed with database information per se - so I'm a fish out of water.

1 - When someone fills out the application - I guess that information gets added to a table that lives somewhere on the server.

2 - That table needs to be able to be accessed by my client using code of their own. MEANING - they will write a hook that will come into the server grab the table information in CSV format - and write a report from it.

[I guess this is how it will all work. However - I don't know what happens to the information once they grab it - does it eventually just become one huge table on the server? That's a question that needs answering - but not the most important at the moment]

SO - knowing all this...

1 - I successfully created a table and linked it to the form.

2 - HOWEVER - when I downloaded the CSV - this is all I got:

cf_id	uid	recordtime	ipaddress	cf_user_id
1	IYzExZT###YzQ0MThk9f9763#3#719c5c7d1d962###b1b9440	2012-02-13 - 09:14:45	##.###.###.##	64

I replaced some of the numbers with hash tags just for privacy in this example.

I actually need to see, for each applicant, the following information:
lastnametext,firstnametext,presentaddresstext,presentcitytext,presentstatepulldown.

And the following radio buttons: ethnicity,race,gender,veteran,recently_separated_veteran,armed_forces_services_medal_veteran,other_protected_veteran


So - before I go any further - I need to be able to at least see that the correct information is making it to the table.

Does that help? It may be that you understand all this - an am jumping ahead. And I'm walking in baby steps!

I've gotten this far... but still no info shows up in the table past the record part...

Problem found by a friend...

The $row->bind() call to store the data from the POST into the table object…is not working properly. It is also not raising any errors. The subsequent call to $row->store() works as expected…but since there is no data stored after $row->bind(), it inserts an empty row in the database. How can I debug what is going on here?

Hi cb1,

Please update to CFv4 RC3.11 if you haven't already done so.

Bob

Put the code below in a new form!
notes:
replace jos_tablename with your real table name!

<?php
global $mainframe;
	$database =& JFactory::getDBO();

	include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";
	//echo $_POST['formid'];
	/*$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
	$database->setQuery( "SELECT name FROM #__chrono_contact WHERE id='".$formid[0]."'" );
	$formname = $database->loadResult();*/
	
	$tablename = 'jos_tablename';
	$tables = array( $tablename );
 	$result = $database->getTableFields( $tables );
	$table_fields = array_keys($result[$tablename]);
	
	$database->setQuery( "SELECT * FROM ".$tablename."" );
	$datarows = $database->loadObjectList();
	
	$titcol = 0;
	foreach($table_fields as $table_field){
		if($titcol){$csvline .=",";}
		$csvline .= $table_field;
		$titcol++;
	}
	$csvline .="\n";
			
	$datacol = 0;
	$rowcount = 1;
	foreach($datarows as $datarow){
		foreach($table_fields as $table_field){
			if($datacol){$csvline .=",";}
			$csvline .= '"'.addslashes($datarow->$table_field).'"';
			$datacol++;
		}
		$csvline .="\n";
		$datacol = 0;
		$rowcount++;
	}
	
	if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "Opera";
	}
	elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
		$UserBrowser = "IE";
	} else {
		$UserBrowser = '';
	}
	$mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
	@ob_end_clean();
	ob_start();

	header('Content-Type: ' . $mime_type);
	header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

	if ($UserBrowser == 'IE') {
		header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
	}
	else {
		header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
		header('Pragma: no-cache');
	}
	print $csvline;
	exit();
?>

Hi, I tried your code and it works perfectly!
Do you have another code to make it exported to xls format?

Thank you🙂

Hi s_meliana,

I think that must be from a very old post.

If you have CFV3 then both Excel and CSV export formats are supported from the View Data screen.

If you have CFv4 then the CSV EXport [GH] action will create CSV files that can be opened in Excel.

Bob

good morning
I use your component for a long time and I find it fantastic. (4.0 RC3.4.1)

I use a form to collect data of the athletes who sign up to my race, then export the data to a csv file and then select some of these data and insert them into another csv to make visible the online "subscriber list" using "Szaki Table".

Now the person who took care of this job is pregnant then I would need to create a fully automated system.
1) data Entry
2) Create a database with all data - (A)
3) Creating a database with some specific data - (B)
4) Direct online publication of the data contained in (B)

Do you believe is possible?

Hi kappadocio ,

Yes probably, I don't know the Szaki Table extension and you don't say what needs to happen to convert from A to B.

The CSV Export action is very customisable and will probably do the export you need.

Bob

PS And you could probably do the listing as well using ChronoForms and/or ChronoConnectivity.

ok
I try to give an example using my terrible English :?

Mario Rossi complete the form (name, surname, address, attach the payment to participate in competitive sports, etc..)

The data must be entered in a database

Part of these data (eg name and surname) should be automatically exported in CSV file format in a web folder (eg http://www.sitoprova.it/table/elenco.csv)

Every time a person registers for the race increases the database and the file elenco.csv should be overwritten automatically

I hope I have explained the procedure :wink:

Hi kappadocio,

You can do this by adding a copy of the CSV Export [GH] action in the On Submit event of your form. That way it will update the file each time it is submitted.

Bob

YOU ARE GREAT ^________________^

I think I have only few questions

FIRST

I am attaching the file with all the data in the CSV export file.
I would only export the column B and C
What should I do?
I'm going on include columns and write b,c ?

SECOND
I'm testing this form and i've this error after "send data"
Warning: Invalid argument supplied for foreach() in /web/htdocs/www.malandrinotrail.it/home/libraries/joomla/database/database/mysql.php on line 375

Hi kappadocio,

Yes, use the boxes on the Columns tab to set the columns you want to export.

I'm not sure where the warning comes from but it is just a Warning, not an Error. Set Error Reporting to System Default or None in the Site Global Configuration to hide these.

Bob

it's PERFECT 8)

Another question?
don't kill me please 😶

when I uploading a file and export CSV format i see the name of file. Right?
So If i uploading ciccio.pdf and export in CSV format I find a Column with ciccio.pdf

Can I Change this value in the CSV format with yes (if I uploading a file) or no (if I don't uploading a file)?

Hi kappadocio,

You can add a Custom Code action before the DB Save action (and after the Upload Files action) with code like this:

<?php
if ( $form->data['file_input'] ) {
  $form->data['file_input'] = 'yes';
} else {
  $form->data['file_input'] = 'no';
}
?>

replace 'file_input' with the name of your file input.

Bob

GREAT!!!!

I must offer two beers :wink:

Why isn't it working? 😈

Hi Turrican,

It is working??

Bob

Hi,
have a problem when I export to CSV: accented characters present in the CSV does not export correctly:
eg.
è, ò, à, ù, ì etc.
This NON È PUBBLICITÀ should be NON È PUBBLICITÀ
how can I fix this?
Best Regards.

Hi jack19,

What are you viewing the CSV file with? Please check that it can view in utf-8 mode and is set to that mode.

Bob

Hello

I have the same problem with utf-8. But not even know it was possible to change the way we see the data in excel (how?). But I am sure that the data is good because when I open with Notepad++ everything is ok.
I made a "routine" in chronoforms and now it is possible to export to server in csv format all data in tables. And it works well. But the problem that brings me here is the following: how to export only the data that the user is authenticated?
And even now, I need to export multiple tables. How to change the values ​​of {csv_count} and {csv_size} for each of the tables exported?. Not very important, but...
I tried also dowloaded the various tables but only works with the first. There are way to solve this?
Thanks, once again

António Graça

Hi António,

to change the way we see the data in excel (how?)

If you use the 'From Text' option on the Data tab in Excel 2010 then you will be able to specify the characters set to be used for the import.

how to export only the data that the user is authenticated?

I don't understand this question? If you want to export only a part of the table then you can use a WHERE clause on the MySQL tab of the CSV Export action to manage this.

How to change the values ​​of {csv_count} and {csv_size} for each of the tables exported?

I've never tried to do mutiple exports at the same time. I expect that they would work if you use more than one copy of the action and make sure to give the files different names? If not, then let me know and I'll test with my custom version of the action (CSV Export v2 [GH]).

I tried also dowloaded the various tables but only works with the first

Again I would expect this to work if you have more than one copy of the action.

Note that you can't use 'Immediate Download' if you want to download more than one file because that option stops ChronoForms from doing any more processing. You have to save the files and include links in a Thank You page to complete the download.

Bob











Bob

Hi Bob

Thankyou to your help.

how to export only the data that the user is authenticated?"

Yes, I want export some part of table, only the records of user is authenticated. I try use WHERE code but not work. Ill try again...

How to change the values ​​of {csv_count} and {csv_size} for each of the tables exported?

I try export several table at same time and work fine. I use several CSV Export v2 [GH] action, one per table. But, the variable of number of records not change the value. The vallue is the same on first table or last table. I understand this, but can I change this? I wating your try

I tried also dowloaded the various tables but only works with the first

The same method I use to export several tables (several CSV Export v2 [GH], etc), but dont work to downlod. Only work to the first table...


Thanks again

António Graça

Hello, again

I trying export only the authenticated user, and I put in box WHERE clause, of the CSV Export v2 [GH], the next code:

WHERE cf_user_id='user_id'

and dont work, dont do anything.

Seems a simples task, something Is missing?

António Graça

Hi António,

Well yes, the User ID is a number not 'user_id' which is a string.

Please see this FAQ

Bob

I fallow the FAQ.
I want to export data from a table but under a certain condition, only the data that the user is authenticated. I put in the OnLoadEvent the Load User Info [GH], put the event CSV Export v2 [GH] in Submit and the WHERE box put the following code:

WHERE cf_user_id={cfu_id}

and nothing happens. Is it the wrong way to pass user data to the box WHERE?
I tried the following solution:
I put in the form a Text box with the name of cod_user in Field Default Value put

{cfu_id}

and in box WHERE CSV Export v2 [GH] put the following code :

WHERE cf_user_id={cod_user}

and works.
Here's something wrong, or not ... Why does not directly putting the value of the variable

{cfu_id}

obtained with the Load event User Info [GH]?
Moreover, the values ​​of these variables obtained with this event are "losted" in the Show Thanks Message. That is, puting a code to call the values ​​of variables in OnLoad, and works. When I copy past the same code for the Show Thanks Message variable values ​​do not appear.
Did I miss something in the process?

Thanks again

António Graça

Hi António,

Data you load in the On Load event is not carried over to the On Submit event unless you deliberately pass it over (in a hidden input, or in the User Session).

It's probably simplest to put the Load User Info [GH] action into the On Submit event. (You can also have a copy in the On Load event if you need it there.)

Bob

Yhank you, Bob

All my doubts, about this topic (yet), are solved.

António Graça

Hello,

I have installed Chronoforms V4 in my joomla 2.5 and everything seems to be working.
There is on thing that I don't knwo how to setup.
I created my online form and would like to have the info submitted by visitors going to a table and be able to export that table later on with a list of all contacts submitted as an excel file. How do I do that?

HI Isantos,

Please see this FAQ

Later you can use one of the CSV Export actions to export from the table to a CSV file that can be opened in Excel.

Bob

Hi!
I put the code into a new form and it did not work.
Is there any special configuration on the form?
The result on the website is a blank page.

Hi rbleao,

What code have you put where exactly?

Bob

Went on the Forms Manager -> New Form -> Code Tab -> Form Type "custom" and in the HTML Code put this:

<?php
global $mainframe;
   $database =& JFactory::getDBO();

   include_once JPATH_BASE.'/components/com_chronocontact/excelwriter/'."Writer.php";  
   
   $tablename = 'UsersProfiles';
   $tables = array( $tablename );
   $result = $database->getTableFields( $tables );
   $table_fields = array_keys($result[$tablename]);
   
   $database->setQuery( "SELECT * FROM ".$tablename."" );
   $datarows = $database->loadObjectList();
   
   $titcol = 0;
   foreach($table_fields as $table_field){
      if($titcol){$csvline .=",";}
      $csvline .= $table_field;
      $titcol++;
   }
   $csvline .="n";
         
   $datacol = 0;
   $rowcount = 1;
   foreach($datarows as $datarow){
      foreach($table_fields as $table_field){
         if($datacol){$csvline .=",";}
         $csvline .= '"'.addslashes($datarow->$table_field).'"';
         $datacol++;
      }
      $csvline .="n";
      $datacol = 0;
      $rowcount++;
   }
   
   if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "Opera";
   }
   elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
      $UserBrowser = "IE";
   } else {
      $UserBrowser = '';
   }
   $mime_type = ($UserBrowser == 'IE' || $UserBrowser == 'Opera') ? 'application/octetstream' : 'application/octet-stream';
   @ob_end_clean();
   ob_start();

   header('Content-Type: ' . $mime_type);
   header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');

   if ($UserBrowser == 'IE') {
      header('Content-Disposition: inline; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Pragma: public');
   }
   else {
      header('Content-Disposition: attachment; filename="' . "ChronoForms - ".$tablename." - ".date("j_n_Y").'.csv"');
      header('Pragma: no-cache');
   }
   print $csvline;
   exit();
?>

Hi rbleao,

Ahh, you've gone a long way back in the thread and picked up some code that worked with ChronoForms v3. Unfortunately it won't work with CFv4 (there were some security problems with the Excel library used in CFv3 and it's no longer included).

The simplest approach is to use the CSV Export action included with ChronoForms v4 to export a CSV file that can then be opened in Excel.

Bob