PHP MySQL SELECT text truncated

2
votes

I have a MySQL table with a column 'full_description' of type 'text'. In that column is a standard lorem ipsum (plus the word END):

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. END

Yet when doing a select on it in php, it only retrieves this much:

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i

Here is the php code that retrieves it:

function getPostingDetails($posting_id){
        $getPosting = $this->PLAST->prepare('SELECT posting_id, poster_id, title, short_description, full_description FROM postings WHERE posting_id=?');
        $getPosting->bind_param('i',$posting_id);
        $getPosting->execute();
        $getPosting->bind_result($row['posting_id'],$row['poster_id'],$row['title'],$row['short_description'],$row['full_description']);
        $getPosting->fetch();
        $getPosting->close();
        return $row;        
    }

This is the array that I get:

Array ( [posting_id] => 1 [poster_id] => 1 [title] => Test 1 [short_description] => This is a short description. [full_description] => Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i )

The rest of the fields are fine. What am I doing wrong? Or is there a setting or feature that I'm not aware of that limits the SELECT statements? In MySQL? In PHP mysqli?

Thanks

This is the table structure upon request:

CREATE TABLE `postings` (
  `posting_id` int(11) NOT NULL AUTO_INCREMENT,
  `poster_id` int(11) NOT NULL,
  `title` tinytext NOT NULL,
  `short_description` tinytext NOT NULL,
  `full_description` text NOT NULL,
  PRIMARY KEY (`posting_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1
4
phpsqlmysqltypes
Can you post the table definition? The text which is returned by SELECT has 256 chars. Don't you have any limit? Another thing: how do you insert the data to the database? – Lukasz Lysik
I just added it now. I don't see where the limit would be... Note that in phpMyAdmin, the full_description shows completely. So it is saved correctly. Just truncated when doing the SELECT. – Nathan H
What is the framework you are using? I think this is some default limitation on the datatype TEXT. – Lukasz Lysik
@Lukasz, you may want to add your comments as an answer to the question, since you're working your way through to a solution. =) – David Thomas
Not sure what you mean by framework, but I'm using XAMPP on Windows (this is my local test environment). – Nathan H

4 Answers

2
votes

I experienced similar problem while using group_concat and setting maximum length for group_concat helped me. Just executed this query before my select query.

SET GROUP_CONCAT_MAX_LEN=10000;

0
votes

What is the framework you are using? I think this is some default limitation on the datatype TEXT.

By framework I mean the PHP library which access the database:

    $getPosting->bind_param(...);
    $getPosting->execute();
    $getPosting->bind_result(...);
    $getPosting->fetch();
    $getPosting->close();

The above are the functions that comes from some library. And the most probable is that this library has default restrictions for the length of TEXT field.

EDITED:

I have found similar problem with SQL Server and PHP here. They suggest to do like this:

SELECT CAST(F AS TEXT) AS F FROM

Maybe you could do the opposite:

SELECT ..., CAST(full_description AS VARCHAR) FROM postings
0
votes

try to initialize an empty string before you fetch and store it in the php.... i had a similar problem where it would only store the first letter of a string, but when i initialized the var before storing to it, it worked

0
votes

Instead of using an array to store the results, does it make any difference if you use actual variables i.e. replace this: $getPosting->bind_result($row['posting_id'],$row['poster_id'],$row['title'],$row['short_description'],$row['full_description']); with this: $getPosting->bind_result($posting_id,$poster_id,$title,$short_description,$full_description); See if that makes any difference?