How can I differentiate a 'NULL' string from a null fact value?

Asked

Viewed 663 times

1

I made this example for you to understand how the query is being made in the bank in my system.

$sql = "SELECT * FROM tabela";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["campo"];
    }
}

My problem is this, I’m taking the result of the query and if it in an object, so far so good, the problem is when null values arrive, if I have a string 'NULL' or a null field the result is the same, as I can differentiate null from a string 'NULL'?

Values in the object:

   'nChegada' => 'NULL',
   'nExpedido' => 'NULL',

One with null value and the other with a string 'NULL'.

UPDATE

Value of n_arrival and n_state are null and of n_dispatched is a string

inserir a descrição da imagem aqui

Code:

public function getDadoById($id) {
    $stmt = $this->conexao->getQuery("select * from " . $this->getTabela() . " WHERE `id` = " . $id);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $cla = null;
    if (count($results) > 0) {
        $atributos = $this->getAtributos();
        $classe = 'model\dominio\\' . ucfirst($this->getTabela());
        $cla = new $classe();
        foreach ($atributos as $atributo) {
            $metodo = 'set' . ucfirst($atributo);
            if(is_null($results[0][$this->atributoToDb($atributo)])){
                $cla->$metodo(NULL);
            }else{
                $cla->$metodo($results[0][$this->atributoToDb($atributo)]);
            }
        }
    }
    return $cla;
}

I tried with the example of Roberto de Campos, but the result was the same:

model\dominio\Funcionario::__set_state(array(
   'id' => '1',
   'pis' => '12345678901',
   'numeroCarteira' => '12345',
   'serie' => '1234',
   'nome' => 'Trabalhador Padrão',
   'mae' => 'Mãe do Trabalhador Padrão',
   'pai' => 'Pai do Trabalhador Padrão',
   'dataNascimento' => '10/10/1988',
   'sexo' => '1',
   'estadoCivil' => '1',
   'naturalidade' => '2680',
   'rg' => '1234567890',
   'cpf' => '12345678901',
   'cnh' => '12345678901',
   'tituloEleitoral' => '123456789012',
   'secao' => '1234',
   'zona' => '123',
   'localEmissao' => '2680',
   'nacionalidade' => '7',
   'nChegada' => 'NULL',
   'nExpedido' => 'NULL',
   'nEstado' => 'NULL',
   'observacao' => 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry\'s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.',
   'email' => '[email protected]',
   'senha' => '123',
   'dataEmissao' => '2017-10-23 13:12:42',
))
php mysql query

  • The result is not the same, a text type field with NULL content returns NULL, now if the field is NULL (has no value) will be returned empty.

  • But if I take $Row["field"] and play on my object as mysql returns null, it understands in the same way as a string 'null'.

  • That point you’re missing, the MySQL does not return NULL it returns empty. What you can do is check if the column has any data.

  • "does not return NULL", but it returned null

  • 1

    Check my answer that you will understand what I am talking about. Who is writing NULL on the screen is the PHP and not the MySQL. The PHP writ NULL when you printa a field without any information

  • Null values in PHP

Show 1 more comment

2 answers

5


The MySQL does not return a string with the content NULL when the field is null, it returns a column with no data, then you can check whether the return has given or not with the function is_null:

$sql = "SELECT * FROM tabela";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        if (is_null($row["campo"]))
            echo "Este campo não tem dado nenhum";
        else
            echo "Este campo tem o seguinte conteúdo: ".$row["campo"];
    }
}

Another way for you to check this return NULL is using the comparative === NULL:

$sql = "SELECT * FROM tabela";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        if ($row["campo"] === null)
            echo "Este campo não tem dado nenhum";
        else
            echo "Este campo tem o seguinte conteúdo: ".$row["campo"];
    }
}
  • 1

    It is not quite "a column without any data" that it returns, it is a column with the value null of PHP.

  • 1

    @bfavaretto would comment on it. Of qq way, despite the terminology error, the solution is correct.

  • Friend, I edited the question trying the way you did, but the result was the same

  • 2

    @Wictorchaves I suggest to read carefully the answer and all the comments made. The problem lies in the wrong premise of the question. The value returned to PHP is null, not a string. Your problem lies in the way it displays on the screen. When using the PHP value, is_null works fine. If you want to display something else, use SELECT COALESCE( coluna, '---' ) AS sem_nulo or something like that. If you just want to display empty, Roberto’s solution is enough. Also check that you are not using something extra in the class used to read DB, which is interfering with the reading.

  • True, gave a var_dump and saw that are different, confused because of the ''.

1

You can filter the nulls in the mysql selection itself, thus:

SELECT ProductID, Name, Color
FROM AdventureWorks2008R2.Production.Product
WHERE Color IS NULL

And if color were a text value it would be like this:

SELECT ProductID, Name, Color
FROM AdventureWorks2008R2.Production.Product
WHERE Color = 'NULL'

These fields are handled differently by the bank.

  • 1

    This is in the filter, but I think the AP wants to differentiate in the rescue. + 1 of any sort

  • 1

    Thanks, but I need to know in return how @Jeffersonquesado spoke.

  • I got it, I’ll check it out

  • @Wictorchaves what the Oberto answered ta right ne?

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.