Buy Now
Sign in

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

admin , December 30 2008, 12:44
admin 28
December 30 2008, 12:44 #20244
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();
?>
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
C
codeslayer 89
December 30 2008, 12:59 #20247
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
admin 28
December 30 2008, 13:32 #20253
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."" );
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
R
richyeiv 1
January 06 2009, 23:04 #20717
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.
admin 28
January 07 2009, 12:12 #20742
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
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
T
tidusx18 92
April 08 2009, 03:25 #29189
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
GreyHead 64
April 08 2009, 08:21 #29198
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
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
T
tidusx18 92
April 08 2009, 22:46 #29266
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!
admin 28
April 09 2009, 05:33 #29296
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
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
T
tidusx18 92
April 09 2009, 05:40 #29300
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!
admin 28
April 09 2009, 06:18 #29317
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
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
J
Jokes 8
August 03 2009, 09:19 #38317
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
admin 28
August 03 2009, 11:41 #38337
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
Max
If your main question got answered then please mark the answer using the button!
Did you try the new ChronoForms7 ? if yes then please send us your comments!!
F
futureit 6
August 26 2009, 05:42 #39380
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"" );
GreyHead 64
August 26 2009, 06:41 #39383
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` ;
";
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
F
futureit 6
August 26 2009, 10:51 #39392
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();
?>
F
futureit 6
August 27 2009, 03:40 #39438
or can I pay you to code it for me?
Q
QueenTut 9
September 04 2009, 15:09 #39858
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.
GreyHead 64
September 05 2009, 14:29 #39883
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
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
K
kollo 7
September 16 2009, 12:40 #40403
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

screenshot.gif
Attachments
screenshot.gif
screenshot.gif
(33.62 KiB)
6253 Downloads/Views