Jump to content
mc1392

PHP Longtext in Mysql - cannot get all text

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 by mc1392

Share this post


Link to post
Share on other sites

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;

 

  • Thanks 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.