Creating a CSV export using Max's script

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

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:

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.

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

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

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🙂.

Hi Nick,

Thnk you, glad you fould the book valuable.

Bob