PHP Longtext in Mysql - cannot get all text

[mc1392]

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?

[gw1500se]

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.

[mc1392]

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.

[gw1500se]

Then it is not a max packet size issue. Post some code so we can see what you are talking about.

[requinix]

Also confirm that there are, in fact, 1000 characters in there.

[mc1392]

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

[Barand]

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;

 

[mc1392]

Thank! I will give this a try and come back and respond.

[mc1392]

@Barandthat was the issue!

You are the man! / you are the woman!

thank you!