Jump to content


New Members
  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About mc1392

  • Rank

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. I've used COALESCE before, but I don't see how it applies to my situation? Could provide a sample? Should I COALESCE then use a CASE?
  2. I have 2 tables: My goal: I am having trouble getting to this result. Any ideas?
  3. @Barandthat was the issue! You are the man! / you are the woman! thank you!
  4. Thank! I will give this a try and come back and respond.
  5. 1. Here is the main calling page code: <?php require_once $_SERVER['ATS_CORE_PHP_LIB_PATH'].'/dao/AtsIFEReviewerDAO.php'; $theYear = intval($_GET['r']); if($theYear > 0){ require_once $_SERVER['ATS_CORE_PHP_LIB_PATH'].'/core/util/ExcelHTML.php'; $dao = new AtsIFEReviewerDAO(); $yearData = $dao->getReviewAnalytics($theYear); $xls = new ExcelHTML($yearData, null); $xls->turnOnDisableAllColumnsFromAutoExcelEscape(); $xls->printOutput($theYear.'_ife_result.xls'); } ?> 2. here is the underlying query for the getReviewAnalytics($theYear) query from the code above: select recordId, full_name,institution,abstract_title,abstract_authors, count(*) as totalReviewSubmission, sum(rate_innovation) as totalInnovation, avg(rate_innovation) as averageInnovation, sum(rate_sustainability_and_implementation) as totalSustainabilityAndImplementation, avg(rate_sustainability_and_implementation) as averageSustainabilityAndImplementation, sum(rate_transferability) as totalTransferability, avg(rate_transferability) as averageTransferability, sum(rate_outcomes) as totalOutcomes, avg(rate_outcomes) as averageOutcomes,avg(rate_innovation)+avg(rate_sustainability_and_implementation)+avg(rate_transferability)+avg(rate_outcomes) as SUM_OF_AVERAGES, group_concat(formulaRateNotes separator '') as allNotes,group_concat(reviewers SEPARATOR '|') AS 'Reviewers' from ( select f.id as recordId, f.full_name,f.institution,f.abstract_title,f.abstract_authors, r.rate_innovation,r.rate_sustainability_and_implementation,r.rate_transferability, r.rate_outcomes,concat('<strong>', r.reviewer_name, '</strong>: ', r.rate_notes, '<br/>') as formulaRateNotes, r.reviewer_name as reviewers from database_name1.innovations_fellowship_education_form f join database_name2.innovations_fellowship_education_form_review r on r.innovations_fellowship_education_form_id = f.id where f.theyear = '".$theYear."' ) t1 group by recordId 3. here, I produce a downloadable Excel file, which is produced by the code in step 1: <?php //simple version of excel create base on html table class ExcelHTML { private $excelArrayHeaderKey; private $excelArrayData; private $debugToScreen; private $excelStyleHeader; private $disableAllColumnsFromAutoExcelEscape; private $ignoreAutoExcelEscape; //disable these columns from auto convert to text field in excel (default all columns escapes) public function __construct($arrayData, $arrayKey = null){ $this->ignoreAutoExcelEscape = null; $this->disableAllColumnsFromAutoExcelEscape = false; $this->debugToScreen = false; $this->excelArrayData = $arrayData; if(!$arrayKey){ //generate key from data if(sizeof($arrayData) > 0){ $firstRowData = $arrayData[0]; $arrayKey = array(); foreach($firstRowData as $k => $v){ $arrayKey[$k] = $k; } } } $this->excelArrayHeaderKey = $arrayKey; $this->excelStyleHeader = $this->getExcelHeaderStyle('DEFAULT'); } public function getExcelHeaderStyle($style){ if($style == 'EXPANDABLE_ROW') return '<style>.t{mso-number-format:"@";border-top:.5pt solid black;}.h{background-color:#f5f5dc;}br{mso-data-placement:same-cell;}</style>'; //default style return '<style>.t{mso-number-format:"@";white-space:nowrap;}.h{background-color:#dcdcdc;}br{mso-data-placement:same-cell;}</style>'; } public function changeHeaderStyle($styleName){ $this->excelStyleHeader = $this->getExcelHeaderStyle($styleName); } public function setIgnoreTheseColumnsFromEscape($ignoreTheseKeys){ $this->ignoreAutoExcelEscape = $ignoreTheseKeys; } public function turnOnDebugToScreen(){ $this->debugToScreen = true; } public function turnOnDisableAllColumnsFromAutoExcelEscape(){ $this->disableAllColumnsFromAutoExcelEscape = true; } public function generateExcelHTML() { $finalHTML = $this->excelStyleHeader; if(sizeof($this->excelArrayHeaderKey) > 0){ //output header $finalHTML .= '<table><tr>'; foreach($this->excelArrayHeaderKey as $eachKeyName){ $finalHTML .= '<th class=h>'.$eachKeyName.'</th>'; } $finalHTML .= '</tr>'; //output record data foreach($this->excelArrayData as $eachRecord){ $finalHTML .= '<tr valign="top">'; foreach($this->excelArrayHeaderKey as $eachKey => $eachKeyName){ if($this->disableAllColumnsFromAutoExcelEscape == true){ //disable auto escape by not adding css class $finalHTML .= '<td>'.$eachRecord[$eachKey]; }else{ $autoEscape = true; if($this->ignoreAutoExcelEscape != null){ if(in_array($eachKey, $this->ignoreAutoExcelEscape)) $autoEscape = false; } if($autoEscape){ $finalHTML .= '<td class=t>'.$eachRecord[$eachKey]; $firstChar = $eachRecord[$eachKey][0]; if($firstChar == '+'){ $finalHTML .= ' '; } }else{ //disable auto escape by not adding css class $finalHTML .= '<td>'.$eachRecord[$eachKey]; } } $finalHTML .= '</td>'; } $finalHTML .= '</tr>'; } $finalHTML .= '</table>'; } return $finalHTML; } public function printOutput($fileName){ if($this->debugToScreen == false){ if(!$fileName) $fileName = 'result_output.xls'; $cleanFileName = preg_replace('/[^a-zA-Z0-9_\.-]/s', '_', $fileName); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$cleanFileName.'"'); header('Pragma: no-cache'); header('Expires: 0'); } echo $this->generateExcelHTML(); } } ?> The column in question is the concatenated field 'allNotes'. Sorry for dumping the entire excel code, this class is new to me.
  6. I'm not getting any errors. I run the query and the string is getting truncated. No error messages.
  7. I have a MySQL Longtext field. When I query the data using PHP, I cannot get all of the text. If there are 1000 characters, I only get about half. Does this have anything to do with packet-size? Is this a configuration thing?
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.