setQuery($query);$options = $db->loadAssocList();foreach ( $options as $o ) { echo "".$o[event_date]."";}?> ย  ย And in the OnSubmit after email:isSite() ) { return; } $where_clause = JRequest::getVar('event_date'); 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_KCShowEntries'; $tables = array( $tablename ); $result = $database->getTableFields( $tables ); $table_fields = array_keys($result[$tablename]); $database->setQuery( "SELECT * FROM ".$tablename." WHERE `event_date` = " '$where_clause'); $datarows = $database->loadObjectList(); $titcol = 0; foreach($table_fields as $table_field){ if($titcol){$csvline .=",";} $csvline .= $table_field; $titcol++; } $csvline .="\n"; ********* etc etc etc as original ?>Sorry to be a pain and thanks in advance for any help.Nick"> Creating a CSV export using Max's script - Forums

Forums

Creating a CSV export using Max's script

Nicodemus 17 May, 2011
I've got finger trouble ๐Ÿ™‚ I know I'm making a mistake in this but just can't see it.
I've read through the topic on using Max's script to create a CSV download and it works a treat in it's base form. I am trying to add a WHERE clause, drawing a value from a dropdown, to the SQL SELECT which gets the table rows for output. The column I'm matching `event_date` on will contain a text string.

In the form HTML code box I've this:-
<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">
      Select Show to Download as a CSV file</label>
    <select class="cf_inputbox required"
        id="event_date" size="1" title="You must choose a show." name="event_date">
      <option value="">Choose a Show...</option>
<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
    SELECT DISTINCT `event_date` 
    FROM `jos_chronoforms_KCShowEntries`
    ;
  ";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
  echo "<option value=\"".$o[event_date]."\">".$o[event_date]."</option>";
}

?>
    </select>

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


<div class="form_item">
  <div class="form_element cf_button">
    <input class="art-button" value="Download" name="submit" id="submit"
type="submit" />
  </div>
  <div class="cfclear">ย </div>
</div>



And in the OnSubmit after email:

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

       $where_clause = JRequest::getVar('event_date');
       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_KCShowEntries';
       $tables = array( $tablename );
       $result = $database->getTableFields( $tables );
       $table_fields = array_keys($result[$tablename]);
       
       $database->setQuery( "SELECT * FROM ".$tablename." WHERE `event_date` = " '$where_clause');
       $datarows = $database->loadObjectList();
       
       $titcol = 0;
       foreach($table_fields as $table_field){
          if($titcol){$csvline .=",";}
          $csvline .= $table_field;
          $titcol++;
       }
       $csvline .="\n";
       
        *********      
       etc etc etc as original
    ?>


Sorry to be a pain and thanks in advance for any help.

Nick
Nicodemus 17 May, 2011
I've partially sorted my problem.
I realised that I hadn't concatenated the variable $where_clause properly and changed the csv source SELECT to:-
$database->setQuery( "SELECT * FROM ".$tablename." WHERE `event_date` = ".$where_clause.";");


Now it at least creates a csv file with a header of column names as it should but no rows ๐Ÿ˜‘ so it's off to phpMyAdmin to test the select syntax I go ๐Ÿ˜€

Nick

Edit: It was the periods . :mrgreen:
Nicodemus 17 May, 2011
SELECT works fine in phpMyAdmin, returns the 3 rows I expect, so it's definately just my brain causing a problem.

SELECT *
FROM `jos_chronoforms_KCShowEntries`
WHERE `event_date` = '27 08 2011 - Paws n Music - HTM show Stoneleigh';


Edit: Tried a couple of bodges in desperation but no luck. Anyone who can spot my c@ck up in the code will receive some nice words :0 'cause I ain't got no money.
Nicodemus 18 May, 2011
I've fixed it and I think it was finger trouble on my part plus I didn't properly de-limit strings but so it goes ๐Ÿ™‚

In the OnSubmit after:-
The where value to test against is set thus using getString:

$where_clause = JRequest::getString('event_date');


And the select query is formed using Joomla's database quote() to wrap single quotes around the value:

$database->setQuery( "SELECT * FROM `".$tablename."` WHERE `event_date` = ".$database->quote($where_clause).";");


See this for more info on db functions http://docs.joomla.org/How_to_use_the_database_classes_in_your_script

Nick
GreyHead 18 May, 2011
Hi Nicodemius,

Sorry, I've been away for a couple of days. Looks like you finally solved this though :-)

I assume that this is for ChronoForms v3 ?? If it's for CFv4 then I've written a custom action that would help a bit.

Bob
Nicodemus 19 May, 2011
Hi Bob,

Yup, cracked it yesterday. I'm convinced it was finger trouble on my part๐Ÿ™‚ And I should've said it's CF v3.2.0 sorry about that.

Cheers,

Nick

Oh and thanks for writing the cookbook, it's been extremely usefull and is well worth every penny. I recommend everyone else goes and gets a copy right now and I rarely recommend anything๐Ÿ™‚.
GreyHead 19 May, 2011
Hi Nick,

Thnk you, glad you fould the book valuable.

Bob
This topic is locked and no more replies can be posted.