PHP PDO SQL query not finding IS NOT NULL rows-CodePudding

table
-----
id column1
1  some random text
2  more random text
3  
4  blah blah
5
6
7
8
9

$sqlData7 = $this->con->prepare("SELECT id, column1 FROM table WHERE column1 IS NOT NULL");
$rowTotal7 = $sqlData7->rowCount();
$attIdArr7 = $sqlData7->fetchAll(PDO::FETCH_COLUMN, 0);

print_r($attIdArr7);

This should return:

Array ( [0] => 1 [1] => 2 [2] => 4 )

but is returning

Array ( )

What am I doing wrong that it is not picking up the not null columns?

I don't think I need an additional boolian column "is_column1_Null" that I can query but after half a day trying to get this to work I am about to add it.

many thanks

CodePudding user response：

You can do write the following query to get the expected result

SELECT id, column1 FROM table column1 IS NOT NULL AND TRIM(column1) <> '';

CodePudding user response：

You need to call $sqlData7->execute()

<?php
$sqlData7 = $pdo->prepare("SELECT id, column1 FROM `table` WHERE column1 IS NOT NULL");
$sqlData7->execute();
$rowTotal7 = $sqlData7->rowCount();
$attIdArr7 = $sqlData7->fetchAll(PDO::FETCH_COLUMN, 1);

var_dump($attIdArr7);

PHP PDO Online test