I have some results from a mysql table that I would like to export, I am currently able to click a download link and download an xls but I would like to be able to run this via a cron job and have the weekly results email to me.
I have looked at doing this from Mysql and save it out as a csv directly.
However I am struggling with the SQL, the table format is as follows
btFormQuestions (some columns ommitted)
+-------+---------------+----------+-----------+
| msqID | questionSetId | Question | InputType |
|-------+---------------+----------+-----------+
| 1 | 123456 | Name | field |
| 2 | 123456 | Telephone| field |
| 3 | 123456 | Email | email |
| 4 | 123456 | Enquiry | test |
btFormAnswers
+-----+------+-------+-----------------+
| aID | asID | msqID | answer |
+-----+------+-------+-----------------+
| 1 | 1 | 1 | Sean |
| 2 | 1 | 2 | 0800 0 |
| 3 | 1 | 3 | se@te.com |
| 4 | 1 | 4 | Asking Question |
btFormAnswersSet
+------+---------------+---------------------+
| asID | questionSetId | created |
+------+---------------+---------------------+
| 1 | 123456 | 2013-04-30 11:07:55 |
The sql queries, I am currently using to get the information into PHP and into an array is as follows:
//get answers sets
$sql='SELECT * FROM btFormAnswerSet AS aSet '.
'WHERE aSet.questionSetId='.$questionSet.' ORDER BY created DESC LIMIT 0, 100;
$answerSetsRS=$db->query($sql);
//load answers into a nicer multi-dimensional array
$answerSets=array();
$answerSetIds=array(0);
while( $answer = $answerSetsRS->fetchRow() ){
//answer set id - question id
$answerSets[$answer['asID']]=$answer;
$answerSetIds[]=$answer['asID'];
}
//get answers
$sql='SELECT * FROM btFormAnswers AS a WHERE a.asID IN ('.join(',',$answerSetIds).')';
$answersRS=$db->query($sql);
//load answers into a nicer multi-dimensional array
while( $answer = $answersRS->fetchRow() ){
//answer set id - question id
$answerSets[$answer['asID']]['answers'][$answer['msqID']]=$answer;
}
return $answerSets;
I would like to be able to do one of the following
A.) Move all of this into one query to be able to get the following sort of result
+---------------+------+-----------+-----------+-----------------+
| QuestionSetID | Name | Telephone | Email | Enquiry |
+---------------+------+-----------+-----------+-----------------+
| 123456 | Sean | 0800 0 | se@te.com | Asking Question |
(I did try this with various joins but could not get them quite right) If I could get this to work I would not mind saving as a CSV
B.) Output the returned array as excel file that can be saved to a location on the server,
The current code creates a html table from the array
The code is a little long so I am only pasting the top and bottom bits here
//fwrite($handle, $excelHead);
//fwrite($handle, $row);
//fflush($handle);
ob_start();
header("Content-Type: application/vnd.ms-excel");
echo "<table>\r\n";
//Question headers go here
foreach($answerSets as $answerSetId=>$answerSet){
$questionNumber=0;
$numQuestionsToShow=2;
echo "\t<tr>\r\n";
echo "\t\t<td>". $dateHelper->getSystemDateTime($answerSet['created'])."</td>\r\n";
foreach($questions as $questionId=>$question){
$questionNumber++;
if ($question['inputType'] == 'checkboxlist'){
$options = explode('%%', $question['options']);
$subanswers = explode(',', $answerSet['answers'][$questionId]['answer']);
for ($i = 1; $i <= count($options); $i++)
{
echo "\t\t<td align='center'>\r\n";
if (in_array(trim($options[$i-1]), $subanswers)) {
// echo "\t\t\t".$options[$i-1]."\r\n";
echo "x";
} else {
echo "\t\t\t \r\n";
}
echo "\t\t</td>\r\n";
//fwrite($handle, $node);
//fflush($handle);
}
}elseif($question['inputType']=='fileupload'){
echo "\t\t<td>\r\n";
$fID=intval($answerSet['answers'][$questionId]['answer']);
$file=File::getByID($fID);
if($fID && $file){
$fileVersion=$file->getApprovedVersion();
echo "\t\t\t".'<a href="'. $fileVersion->getDownloadURL() .'">'.$fileVersion->getFileName().'</a>'."\r\n";
}else{
echo "\t\t\t".t('File not found')."\r\n";
}
echo "\t\t</td>\r\n";
}else{
echo "\t\t<td>\r\n";
echo "\t\t\t".$answerSet['answers'][$questionId]['answer'].$answerSet['answers'][$questionId]['answerLong']."\r\n";
echo "\t\t</td>\r\n";
}
//fwrite($handle, $node);
//fflush($handle);
}
echo "\t</tr>\r\n";
//fwrite($handle, $row);
//fflush($handle);
}
echo "</table>\r\n";
//fwrite($handle, $excelFoot);
//fflush($handle);
//fclose($handle);
file_put_contents($filePath, ob_get_clean());
I can get the file to save to the directory but I am having issues setting it as an Excel file, I have also tried, playing with Fwrite (instead of the buffer) with the similar results
can anyone help, or point me in the right location.
Thank you, Sean
