receiving null or void field

Asked

Viewed 152 times

1

I have a class object that appears like this on print_r();

Membros Object
(
    [idMembro:Membros:private] => 
    [dataCadastro:Membros:private] => 2019-01-10
    [nome:Membros:private] => Cleonice P
    [apelido:Membros:private] => cleo
    [dataNascimento:Membros:private] => 1974-10-03
    [telefone:Membros:private] => 3237216149
    [celular:Membros:private] => 32988518043
    [bairro:Membros:private] => São Francisco
    [endereco:Membros:private] => Rua Francisco
    [email:Membros:private] => [email protected]
    [sexo:Membros:private] => Masculino
    [estadoCivil:Membros:private] => Solteiro
    [dataBatismo:Membros:private] => 2019-01-09
    [bloqueado:Membros:private] => n
    [batizadoFora:Membros:private] => n
    [usuario:Membros:private] => caca
    [senha:Membros:private] => aaaa
)

But dating,

[dataBatismo:Membros:private] => 2019-01-09

It may come empty,

[dataBatismo:Membros:private] =>

I have the function below populating the membership table 

public function cadastrar( $Membro ) {

    $dataBatismo = empty($Membro->getDataBatismo()) ? "NULL" : $Membro->getDataBatismo();

    $string = "INSERT INTO membros (     
                     dataCadastro,
                     nome,
                     apelido,
                     dataNascimento,
                     telefone,
                     celular,
                     bairro,
                     endereco,
                     email,
                     sexo,
                     estadoCivil,
                     dataBatismo,
                     bloqueado,
                     batizadoFora,
                     usuario,
                     senha) 
                    VALUES (
                       '" . $Membro->getDataCadastro() . "',
                       '" . $Membro->getNome() . "',
                       '" . $Membro->getApelido() . "',
                       '" . $Membro->getDataNascimento() . "',
                       '" . $Membro->getTelefone() . "',
                       '" . $Membro->getCelular() . "',
                       '" . $Membro->getBairro() . "',
                       '" . $Membro->getEndereco() . "',
                       '" . $Membro->getEmail() . "',
                       '" . $Membro->getsexo() . "',
                       '" . $Membro->getEstadoCivil() . "',
                       " . $dataBatismo . ",
                       '" . $Membro->getBloqueado() . "',
                       '" . $Membro->getBatizadoFora() . "',
                       '" . $Membro->getUsuario() . "',
                       '" . $Membro->getSenha() . "'
                       )";
print "<pre>";
print_r($string);   
print "</pre>";
    return $this->conexao->query( $string ) == true ? true : false;

}

The problem is here:

$dataBatismo = empty($Membro->getDataBatismo()) ? "NULL" : $Membro->getDataBatismo();

More precisely here:

$Membro->getDataBatismo();

When the date is not empty, then you end up in trouble here,

" . $dataBatismo . ",

Which leaves a nonstring in query and can only be null or string date

2019-10-01,

When the right thing would be:

"2019-10-01",

How to solve this?

php php-7

  • If it is a string, why does it not have single quotes like all other columns in your table?

  • I would make the same remark. Since birth date apparently works, baptism date should also be in the same syntax. Take a look at whether it is the lack of simple quotes in the string mount.

  • because if it is null, it cannot go with quotation marks to the query or it will NOT work However, if it is NOT null, it needs the quotation marks but I made some attempts to add the quotation marks only when it is not null and none of them worked.

  • But you’ve already played the NULL in quotes. Will end up resulting in a string anyway: [...] mo()) ? "NULL" : "$Membr [...]. Incidentally, his getter should already be ready to return NULL in empty fields.

  • When the "NULL" that is still in PHP arrives in SQL it moves in NULL without the quotes there. Po cause concatenation " . $dataBatismo . "

  • You could use a reply that would fix ALL that code posted?

  • It makes sense @Lipespry, because the ideal would be to use PDO

  • @Pauloweverton Rightly. Actually, I don’t know what it uses. But it’s object oriented. It could be PDO with the method query() or Mysqli with the method query()... My idea is to transform this query into Prepared statement.

  • That. The assembly of the consultation would be more organized and would not have this kind of problem that it seeks the solution

  • yes Lipespry , would be

  • @Carlosrocha All right. I will formulate an answer and I return now.

  • but in the whole system I do not use PDO because only work with mysql. More specifically mysqli

  • PDO accepts Mysql and several other database types. Mysqli, on the other hand, only works with Mysql (or mariadb that gives in the same)...

  • I will have to study a little bit about PDO yet. But thank you!

  • @Pauloweverton takes a look at my answer and see if you have anything else to complement it. Vlw!

Show 10 more comments

2 answers

3


If I understand correctly, just include single quotes when the value is not empty:

$dataBatismo = empty($Membro->getDataBatismo()) ? "NULL" : "'" . $Membro->getDataBatismo() . "'";

NOTE: I don’t know where the data you use in this query comes from, but concatenating the values without processing can create security problems. I recommend using Prepared statements.

  • exact. Thank you, it worked!

  • I’m recording a new member. Then, and I take these form fields and send them to a php script that will receive the form via ajax and will create an object of the Member class that will insert into the database via a class method that only does entries and updates. That means everything in OO.

  • It doesn’t matter if it’s OO, you’re mounting the query at hand with potentially unsafe data. Use Prepared statements, the question I Linkei shows as.

2

To begin with, we need to understand that:

  • null or NULL are null and ready - See documentation: PHP - NULL;

  • "null" or "NULL" or 'NULL' sane strings worthwhile null/NULL - View documentation: PHP - Strings;

Behold:

var_dump(null); // Retorna: NULL
var_dump('null'); // Retorna: string(4) "null"

This is valid both for the PHP how much for the Mysql (see Mysql data types), which is the db in question.

See the test:

$null = null;
$mysqli->query("INSERT INTO `teste_null` VALUES ('".$null."');");
// > SELECT * FROM `teste_null`;
// +--------+
// | coluna |
// +--------+
// |        | // string vazia: ''
// +--------+

$null = "null";
$mysqli->query("INSERT INTO `teste_null` VALUES ('".$null."');");
// > SELECT * FROM `teste_null`;
// +--------+
// | coluna |
// +--------+
// | null   | // null
// +--------+

Behold:

$dataBatismo = empty($Membro->getDataBatismo()) ? "NULL" : $Membro->getDataBatismo();

In thesis, add the quotes both in query as in the coalescence, will solve the problem in question. But, hold on! It has how to improve and much its code.

As stated, Mysql can store null as string when you store this data, since it is in quotes. That way, you will not be able to validate such information using the functions empty(), is_null() and the like.

Before you know it, you’ll have a bomb on your hands. Delve into this in a way that avoids future maintenance due to the bad practice of something so simple to circumvent.

Let’s start correcting your code!

See, again, this:

$dataBatismo = empty($Membro->getDataBatismo()) ? "NULL" : $Membro->getDataBatismo();

Could easily come ready from your getter:

public function getDataNascimento()
{
    if (empty($this->dataNascimento))
        return null;
    else
        return $this->dataNascimento;
    /* Ou
    return (
        empty($this->nascimento)
        ? null
        : $this->nascimento
    ); */
}

It would be nice, too, to standardize your Setter:

public function setDataNascimento($valor)
{
    if (empty($valor))
        $this->dataNascimento = null;
    else {
        // Validações entram aqui
        $this->dataNascimento = trim($valor);
    }
    return true;
}

This is valid for all properties in your class.

Now we must correct your query. No quotation marks on it! At most, which I recommend, is to use the crase to avoid using some reserved word from Mysql. You must have heard this saying:

"- It’s better to have and not need than need and not have..."

To do so, I will make an example using the PDO. Sure, you can do with the Mysqli also. But I will make my example with the PDO. I’m more intimate with him, Uai!

Suppose that the variable $pdo is an object of the class \PDO. Vulgarity connection to db:

// Montando a query:
$sql = 'INSERT INTO `membros` (
    `dataCadastro`, `nome`, `apelido`, `dataNascimento`,
    `telefone`, `celular`, `bairro`, `endereco`, `email`,
    `sexo`, `estadoCivil`, `dataBatismo`, `bloqueado`,
    `batizadoFora`, `usuario`, `senha`
) VALUES (
    :dataCadastro, :nome, :apelido, :dataNascimento,
    :telefone, :celular, :bairro, :endereco, :email,
    :sexo, :estadoCivil, :dataBatismo, :bloqueado,
    :batizadoFora, :usuario, :senha
);';

// Preparar a query:
$pdo->prepare($sql);

// "bindar" os valores na query
$pdo->bindValue(':dataCadastro', $Membro->getDataCadastro());
$pdo->bindValue(':nome', $Membro->getNome());
$pdo->bindValue(':apelido', $Membro->getApelido());
$pdo->bindValue(':dataNascimento', $Membro->getDataNascimento());
$pdo->bindValue(':telefone', $Membro->getTelefone());
$pdo->bindValue(':celular', $Membro->getCelular());
$pdo->bindValue(':bairro', $Membro->getBairro());
$pdo->bindValue(':endereco', $Membro->getEndereco());
$pdo->bindValue(':email', $Membro->getEmail());
$pdo->bindValue(':sexo', $Membro->getsexo());
$pdo->bindValue(':estadoCivil', $Membro->getEstadoCivil());
$pdo->bindValue(':dataBatismo', $Membro->getDataNascimento());
$pdo->bindValue(':bloqueado', $Membro->getBloqueado());
$pdo->bindValue(':batizadoFora', $Membro->getBatizadoFora());
$pdo->bindValue(':usuario', $Membro->getUsuario());
$pdo->bindValue(':senha', $Membro->getSenha());

// retorna true se tudo ocorrer bem ou false se ocorrer algum erro
return $pdo->execute();

I find it elegant to use the method bindValue(). But you can use an associative matrix as argument of the method execute():

$pdo->execute(
    array(
        ':dataCadastro' => $Membro->getDataCadastro(),
        ':nome' => $Membro->getNome(),
        ':apelido' => $Membro->getApelido(),
        ':dataNascimento' => $Membro->getDataNascimento(),
        // ...
    )
);

The difference, basically, is that the method bindValue() acceptance:

$pdo->bindValue(':dataCadastro', $Membro->getDataCadastro(), PDO::PARAM_STR);
$pdo->bindValue(':nome', $Membro->getNome(), PDO::PARAM_STR);
$pdo->bindValue(':apelido', $Membro->getApelido(), PDO::PARAM_STR);
//...

See the types in the documentation: PHP - PDO: Predefined constants

Note that I have not used quotes in any of the table columns. Mysql will take care of doing everything for you. Just remember that NULL is NULL (empty, undefined, absent) and "NULL" is a string worthwhile NULL.

Recommended reading: Mysql - What types of data should be in quotes?

  • I also recommend using Prepared statements, but your assertion that your code enters null as string does not proceed.

  • Opa, I could be wrong. But I believe that already happened to me this. I will confirm and already I give a return.

  • @bfavaretto Really you were right in part. I edited the answer. Look there. In PDO I always assign NULL in empty fields: $pdo->setAttribute(\PDO::ATTR_ORACLE_NULLS,\PDO::NULL_EMPTY_STRING);

  • This answer solves in the best way

Browser other questions tagged php php-7

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