mc1392 Posted November 25, 2019 Share Posted November 25, 2019 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? Quote Link to comment Share on other sites More sharing options...
gw1500se Posted November 25, 2019 Share Posted November 25, 2019 Did you check for errors? If you exceed the max packet size you should see error 1153. If you need to change it, set 'max_allowed_packet' in the ini file. Quote Link to comment Share on other sites More sharing options...
mc1392 Posted November 25, 2019 Author Share Posted November 25, 2019 19 minutes ago, gw1500se said: Did you check for errors? If you exceed the max packet size you should see error 1153. If you need to change it, set 'max_allowed_packet' in the ini file. I'm not getting any errors. I run the query and the string is getting truncated. No error messages. Quote Link to comment Share on other sites More sharing options...
gw1500se Posted November 25, 2019 Share Posted November 25, 2019 Then it is not a max packet size issue. Post some code so we can see what you are talking about. Quote Link to comment Share on other sites More sharing options...
requinix Posted November 25, 2019 Share Posted November 25, 2019 Also confirm that there are, in fact, 1000 characters in there. Quote Link to comment Share on other sites More sharing options...
mc1392 Posted November 25, 2019 Author Share Posted November 25, 2019 (edited) 11 minutes ago, gw1500se said: Then it is not a max packet size issue. Post some code so we can see what you are talking about. 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. Edited November 25, 2019 by mc1392 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 25, 2019 Share Posted November 25, 2019 If you look up GROUP_CONCAT in the MySQL manual... The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer: SET [GLOBAL | SESSION] group_concat_max_len = val; Â 1 Quote Link to comment Share on other sites More sharing options...
mc1392 Posted November 25, 2019 Author Share Posted November 25, 2019 Thank! I will give this a try and come back and respond. Quote Link to comment Share on other sites More sharing options...
mc1392 Posted November 25, 2019 Author Share Posted November 25, 2019 @Barandthat was the issue! You are the man! / you are the woman! thank you! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.