How to export data from SQL to excel file

Hi!

Can the chronoconnectivity export data from SQL to excel file?
If it capable, Please anyone help how to code it.

Hi noo_bai,

ChronoConnectivity doesn't include the ExcelWriter code - that's only in ChronoForms. However, you could probably hook up a ChronoForms form to the same table using the DB Connectivity tab and export from there. Or it is pretty straightforward to custom code for yourself if you know a little PHP.

Bob

Hi Bob!

Thank you for your reply. Now I can export data as your advice. :o
One question about this. Can the chronoform export data in front end?, that is I looking for my form...

Hi noo_bai,

Not automatically but it's not difficult to code. I have created a couple of front-end forms that just have 'BackUp' and 'Delete' buttons, the 'BackUp' button exports new records to Excel, then the 'Delete' button changes a flag in the record to hide the records from future deletes.

Bob

Hi noo_bai,

this question have been answered before too, look at the forums or copy the backup code from the admin.chronocontact.php file!

Regards
Max

Hi Max, Bob.

Thank again. I got Idea for that.😀

sorry but i didnt understand, how can i use the code from admin.chronocontact.php in chrono conectivity to create a backup button?

thanks

Hi tvdotto,

You can't do it quite like that, but you can build a ChronoForms 'form' that will export the data from the same table.

Bob

can you explain a little more how can i achieve this?

Hi tvidotto,

A bit complex to explain but here's an example. The form html is:

<?php
$debug = $paramsvalues->debug;

// redirect user to home page if not Manager or higher
$user = & JFactory::getUser();
if ( ! in_array($user->gid, array('23' , '24' , '25')) ) {
    $mainframe->redirect("index.php");
}

// set database table name
$table = "#__chronoforms_8";
$database = & JFactory::getDBO();

// Read records from database
$sql = "SELECT cf_id, recordtime, team_array, team_size, team_cost
      FROM $table
      WHERE backup = 'false';";
$database->setQuery($sql);
if ( ! $database->query() ) {
    $mainframe->enqueuemessage($database->getErrorMsg(), 'error');
}
$rows = $database->loadObjectList();
$cf_id_array = array();
foreach ( $rows as $row ) {
    $team_name = substr($row->team_array, 0, strpos($row->team_array, ";"));
    echo $row->recordtime . " : " . $team_name . "<br />";
    $cf_id_array[] = "'".$row->cf_id."'";
}
?>
<input type="submit" name="submit" value="Backup" />
<!--  <input type="submit" name="submit" value="Delete"
    onclick="javascript:return confirm('Are you sure you want to delete these records ?')" /> -->
<input type="hidden" name="user_id" value="<?php echo $user->id; ?>" />
<input type="hidden" name="cf_id_list" value="<?php echo implode(',',$cf_id_array); ?>" />
<input type="hidden" name="table" value="<?php echo $table; ?>" />

This reads the new records from the table and shows a summary list with two buttons.

The OnSubmit After code is:

<?php

$user = & JFactory::getUser();
// Check the user is authorised
if ( ! in_array($user->gid, array('23' , '24' , '25')) ) {
    $mainframe->redirect("index.php");
}
// Check the user is the same user as submitted the form
if ( !$user->id == JRequest::getVar('user_id','', 'post', 'string', '' ) ) {
    $mainframe->redirect("index.php");
}
// get data from the post array
$table = JRequest::getVar('table','', 'post', 'string', '' );
$cf_id_list = JRequest::getVar('cf_id_list','', 'post', 'string', '' );
$submit = JRequest::getVar('submit','', 'post', 'string', '' );

// Check which submit button was used - Backup or Delete
if ( $submit == 'Backup' ) {
    // backup the selected records to an Excel spreadsheet
    $table_fields = array('team_name', 'find_out', 'title',
        'first_name', 'last_name', 'dob', 'address1', 'address2', 'city',
        'postcode', 'phone', 'email', 'gender', 'data_prot', 'gift_aid',
        'attracted', 'status');
    $sql = "
        SELECT *
            FROM $table
            WHERE cf_id IN ($cf_id_list);";
    $database->setQuery($sql);
    $datarows = $database->loadObjectList();
    $data_array = array();
    $i = 0;
    // this is some code to pack the data that can probably be ignored
    foreach ( $datarows as $row ) {
        $semi_1 = $semi_2 = $team_name = $find_out = "";
        $semi_1 = strpos($row->team_array, ";");
        $team_name = substr($row->team_array, 0, $semi_1);
        $semi_1++;
        $semi_2 = strpos($row->team_array, ";", $semi_1);
        $find_out  = substr($row->team_array, $semi_1, $semi_2 - $semi_1);
        $team_array = explode( '|', $row->team_array );
        foreach ( $team_array as $team_member ) {
            $member_array[$i] = array_combine($table_fields, explode('; ',$team_member));
            $member_array[$i]['team_name'] = $team_name;
            $member_array[$i]['find_out'] = $find_out;
            $member_array[$i]['team_id']    = $row->cf_id;
            $member_array[$i]['recordtime'] = $row->recordtime;
            $member_array[$i]['team_size']  = $row->team_size;
            $i++;
        }
    }
    // write the data to the spreadsheet using the function below
    writeSpreadSheet($table_fields, $member_array);
} elseif  ( $submit == 'Delete' ){
    // Delete the records from the database
    $sql = "
        DELETE
            FROM $table
            WHERE uidp IN ($uidp_list);";
    $database->setQuery($sql);
    if (!$database->query()) {
        $mainframe->enqueuemessage($database->getErrorMsg(), 'error');
    } else {
        $mainframe->enqueuemessage("Records were deleted");
    }
} else {
    // it wasn't backup or delete so redirect
    $mainframe->redirect("index.php");
}
/**
 * Write the selected records to an Excel spreadsheet
 * This is an edited copy of the function from ChronoForms admin
 *
 * @param array $table_fields the table fields to write
 * @param object list $datarows the data to write
 */
function writeSpreadSheet($table_fields, $datarows)
{
    include_once JPATH_BASE.DS.'administrator'.DS.'components'
        .DS.'com_chronocontact'.DS.'excelwriter'.DS.'Writer.php';
    $xls = & new Spreadsheet_Excel_Writer();
    $xls->send("Hearts+Heroes_" . date('j_n_Y') . ".xls");
    $format = & $xls->addFormat();
    $format->setBold();
    $format->setColor("blue");

    $sheet = & $xls->addWorksheet('Teams at '.date("m-d-Y"));

    $titcol = 0;
    foreach ( $table_fields as $table_field ) {
        $sheet->writeString(0, $titcol, $table_field, $format);
        $titcol ++;
    }

    $datacol = 0;
    $rowcount = 1;
    foreach ( $datarows as $datarow ) {
        foreach ( $table_fields as $table_field ) {
            $sheet->writeString($rowcount, $datacol, $datarow[$table_field], 0);
            $datacol ++;
        }
        $datacol = 0;
        $rowcount ++;
    }
    $xls->close();
    exit();
}
?>

This is beta code and may not work without debugging!

Note, later versions of this code didn't actually delete the records but set a 'deleted' flag instead.

Bob

and check the code in the export to excel function in admin.chronocontact.php, you will need to edit very few things to get it working in a new form!

Max

i think it will be a little dificult to work, i will search more about writing database to excel

i tryed your code in html and in php with jumi and both returned a

Redirect Loop
Firefox has detected that the server is redirecting the request for this address in a way that will never complete.

what i had done wrong?

thanks








Firefox has detected that the server is redirecting the request for this address in a way that will never complete.

Hi, I got the code from the admin form, put it in a new form inside PHP tags:

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

	include_once JPATH_BASE.DS.'/components/com_chronocontact/excelwriter/'."Writer.php";
	//echo $_POST['formid'];
	$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
	$database->setQuery( "SELECT name FROM #__your_table" );
	$formname = $database->loadResult();
	
	$tablename = "#__your_table";
	$tables = array( $tablename );
 	$result = $database->getTableFields( $tables );
	$table_fields = array_keys($result[$tablename]);
	
	$database->setQuery( "SELECT * FROM ".$tablename."" );
	$datarows = $database->loadObjectList();
	
	$xls =& new Spreadsheet_Excel_Writer();
	$xls->setVersion(8); // this fixes the 255 limit issue!🙂
	$xls->send("ChronoForms - ".$formname." - ".date("j_n_Y").".xls");
	$format =& $xls->addFormat();
	$format->setBold();
	$format->setColor("blue");
	if (strlen($formname) > 10){$formname = substr($formname,0,10);};
	$sheet =& $xls->addWorksheet($formname.' at '.date("m-d-Y"));
	$sheet->setInputEncoding('utf-8');

	$titcol = 0;
	foreach($table_fields as $table_field){
		$sheet->writeString(0, $titcol, $table_field, $format);
		$titcol++;
	}
			
			
	$datacol = 0;
	$rowcount = 1;
	foreach($datarows as $datarow){
		foreach($table_fields as $table_field){
			$sheet->writeString($rowcount, $datacol, $datarow->$table_field, 0);
			$datacol++;
		}
		$datacol = 0;
		$rowcount++;
	}
			
	$xls->close();
	exit;
	

sorry for the last post, i didnt realised that the code where to put in a chronoform

i tryed the grayhead code and i got one xls file, i will edit the code to my database and i will give a feedback soon

thanks

Hi, I got the code from the admin form, put it in a new form inside PHP tags:

[

max i tryed your code and got a blank page, what i made was :

created a new form

named testBackupMax

in form code/ form html i put your code with my db information and the php tags

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

       include_once JPATH_BASE.DS.'/components/com_chronocontact/excelwriter/'."Writer.php";
       //echo $_POST['formid'];
       $formid = JRequest::getVar( 'formid', array(), 'post', 'array');
       $database->setQuery( "SELECT name FROM jos_chronoforms_Cadastro_Resultado" );
       $formname = $database->loadResult();
       
       $tablename = "jos_chronoforms_Cadastro_Resultado";
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename."" );
       $datarows = $database->loadObjectList();
       
       $xls =& new Spreadsheet_Excel_Writer();
       $xls->setVersion(8); // this fixes the 255 limit issue!🙂
       $xls->send("ChronoForms - ".$formname." - ".date("j_n_Y").".xls");
       $format =& $xls->addFormat();
       $format->setBold();
       $format->setColor("blue");
       if (strlen($formname) > 10){$formname = substr($formname,0,10);};
       $sheet =& $xls->addWorksheet($formname.' at '.date("m-d-Y"));
       $sheet->setInputEncoding('utf-8');

       $titcol = 0;
       foreach($table_fields as $table_field){
          $sheet->writeString(0, $titcol, $table_field, $format);
          $titcol++;
       }
             
             
       $datacol = 0;
       $rowcount = 1;
       foreach($datarows as $datarow){
          foreach($table_fields as $table_field){
             $sheet->writeString($rowcount, $datacol, $datarow->$table_field, 0);
             $datacol++;
          }
          $datacol = 0;
          $rowcount++;
       }
             
       $xls->close();
       exit;
?>

enabled it


i made anything wrong?

thanks

Hi tvidotto,

more changes are need in the code, look below the table name you changed and some more lines need to be changed, let me know if you can't do it, I'm in rush at the moment!

regards
Max

sorry max, i read several times the code and i dont know where more i need to change, i dont know much about database code,

take the time you need, you already helped me a lot

Hi,

the code will depend alittle at your table name and fields, I made some changes but I advise that you walk through the code line by line and see if there is anything undefined or strange and think about it, its easy and straight forward!

Regards
Max

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

           include_once JPATH_BASE.DS.'/components/com_chronocontact/excelwriter/'."Writer.php";
           //echo $_POST['formid'];
           //$formid = JRequest::getVar( 'formid', array(), 'post', 'array');
           $database->setQuery( "SELECT name FROM jos_chronoforms_Cadastro_Resultado" );
           $formname = "anyform";//$database->loadResult();
           
           $tablename = "jos_chronoforms_Cadastro_Resultado";
           $tables = array( $tablename );
           $result = $database->getTableFields( $tables );
           $table_fields = array_keys($result[$tablename]);
           
           $database->setQuery( "SELECT * FROM ".$tablename."" );
           $datarows = $database->loadObjectList();
           
           $xls =& new Spreadsheet_Excel_Writer();
           $xls->setVersion(8); // this fixes the 255 limit issue!🙂
           $xls->send("ChronoForms - ".$formname." - ".date("j_n_Y").".xls");
           $format =& $xls->addFormat();
           $format->setBold();
           $format->setColor("blue");
           if (strlen($formname) > 10){$formname = substr($formname,0,10);};
           $sheet =& $xls->addWorksheet($formname.' at '.date("m-d-Y"));
           $sheet->setInputEncoding('utf-8');

           $titcol = 0;
           foreach($table_fields as $table_field){
              $sheet->writeString(0, $titcol, $table_field, $format);
              $titcol++;
           }
                 
                 
           $datacol = 0;
           $rowcount = 1;
           foreach($datarows as $datarow){
              foreach($table_fields as $table_field){
                 $sheet->writeString($rowcount, $datacol, $datarow->$table_field, 0);
                 $datacol++;
              }
              $datacol = 0;
              $rowcount++;
           }
                 
           $xls->close();
           exit;
    ?>

the code will depend alittle at your table name and fields, I made some changes but I advise that you walk through the code line by line and see if there is anything undefined or strange and think about it, its easy and straight forward!

it was really straight forward, i changed a lot of things on the code, even a lot of stranges changes just to see if i could get any result and nothing

i followed grayheads and yours tips to copy the code front admin.chronocontact, and got nothing again, but when a i tried with the csv code, it worked in the first atempt, i dont know what wasnt right, but the same things i tried with the BackupExcel worked in the BackupCSV, and it solves my problem the same way =]

thanks for your great support, you two helped me a lot, thanks for the patience too, im not very good coding and i learned a lot here

im already using chronoforms in 2 sites and the next one will pay for the chronoforms license =]

Glad you found a solution, I think you may need to change this line to get excel working:

from

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

to

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

Regards
Max

Hey

I was just wondering what to do with this coding, I have no idea where or what to do with this for it to work. My knowledge on joomla is limited but I understand the coding (to a point). My question is when I have this code what do I do with it to actually have it show the backup button on the front page.

I don't understand half of what you said mainly because I haven't worked with joomla and websites much. Can you explain this to me or refer me to a website where I can learn what to do with this coding and how to use it.

Thanks Poepol!

Thank you for this useful information! It really helped.

Or use SQL to Excel component (free and Pro version).
The combination of this component and ChronoForms is a winner!
Link to JED: http://extensions.joomla.org/extensions/core-enhancements/data-reports/7413