PDO returns NULL on longtext column, even if it's not NULL

Question

I'm trying to manually fetch a row on the Wordpress table wp_post from outside wordpress. I use PDO as follows:

$dbh2 = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
    $dbh2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh2->prepare("SELECT WP.* FROM wp_posts WP, wp_term_relationships WTR WHERE WP.ID = WTR.object_id AND WP.ID = '821'");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $result){
    print_r($result);

}

The query works but I get a really weird result: the column post_content is returned as NULL even if it's not empty (I checked on PhpMyAdmin. The post with ID: 821 exists and is full of content).

Why is it returning NULL on that column?

Additional notes:

post_content column has longtext type
post_content has a very long text inside, 2086 chars and full of html tags.

EDIT: The answer is correct and works. What's weird is that var_dump correctly outputs the content, while the function fb() from FirePHP returns NULL. Be careful!

Why do you need a longtext field? Try one of the smaller versions maybe? — GordonM, Mar 12 '15 at 19:10
The table is a Wordpress table. I didn't decide to set the post_content type as longtext, the creator of Wordpress decided that :) I just need to get the value of post_content, but i'm getting NULL and i don't know why. What's weird is that only THAT column returns NULL while all the others return the correct value — Alberto Fontana, Mar 12 '15 at 19:13
maybe this is your problem: http://stackoverflow.com/questions/24781930/mysql-pdo-maximum-longblob-data-length-on-fetch but it is strange that you get NULL, i got a striped string in that case. — steven, Mar 12 '15 at 19:18
Only post_content is returned NULL. All the other values are returned correctly — Alberto Fontana, Mar 12 '15 at 19:31
It may be worthwhile selecting that longtext (blob) column separately, or binding using the 'PDO::PARAM_LOB' type before the fetch, May be useful: [PDO allows you to work with this large data type by using the PDO::PARAM_LOB type](http://php.net/manual/en/pdo.lobs.php). — Ryan Vincent, Mar 12 '15 at 20:00
I tried but that doesn't work. Maybe i did it the wrong way...can you try to adapt my code to properly fit with PDO::PARAM_LOB ? — Alberto Fontana, Mar 14 '15 at 16:46
ouch - your notice about client side 'discrepancies' with _var_dump_ and _fb()_ are 'concerning'. I suggest that you raise the issues that you found with the 'support team' for 'firePHP'. The more information that you can supply the 'easier' it will be for them to find the 'differences'. — Ryan Vincent, Mar 19 '15 at 23:39

Ryan Vincent · Accepted Answer · 2015-03-16T13:49:03.367

An implementation of the example to retrieve a 'longtext' (blob) column.

Note: the manual is incorrect about returning a stream as it returns a string containing the contents of the 'lob'.

$pdo = new PDO("mysql:host=localhost;dbname=testmysql", 'test', 'test');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT WP.ID,
                            WP.post_title,
                            WP.post_content,
                            length(WP.post_content) AS post_length
                       FROM
                             wp_posts WP
                       WHERE
                             WP.ID = :ID");

// which record to select
$stmt->bindValue(':ID', 821, PDO::PARAM_INT);

// store output in these variables
$stmt->bindColumn(1, $wpId, PDO::PARAM_INT);
$stmt->bindColumn(2, $wpTitle, PDO::PARAM_STR);
$stmt->bindColumn(3, $wpContent, PDO::PARAM_LOB);
$stmt->bindColumn(4, $wpLength, PDO::PARAM_INT);

$stmt->execute();
$results = $stmt->fetch(PDO::FETCH_BOUND);

// display output
var_dump($wpId, $wpTitle, $wpLength);
var_dump($wpContent);

Table Definition

Field         Type          
------------  ------------  
ID            bigint(20)    
post_title    varchar(255)  
post_content  longtext

Sample Output:

int 821
string 'first' (length=5)
int 2741

string 'First Post - Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aenean lacus urna, impe'... (length=2741)

PDO returns NULL on longtext column, even if it's not NULL

1 Answers1