Pdostatement::bindParam() mix data

Asked

Viewed 355 times

0

I have a class that inserts the records into a table. The data coming from the request I turn it into an array and then build an SQL statement similar to that:

INSERT INTO web_messages (pk_web_messages, fk_type_messages, name_user, email_user, title_msg, phone_user, cel_user, end_user, compl_user, city_user, state_user, cep_user, msg_user) VALUES (:pk_web_messages, :fk_type_messages, :name_user, :email_user, :title_msg, :phone_user, :cel_user, :end_user, :compl_user, :city_user, :state_user, :cep_user, :msg_user)

The parameters pass by by the bindParam function and when I do the run runs a lot of crazy data in the table: see the code as I mount:

        foreach ($this->Dados as $field => $defs): // Constrói os parâmetros do comando INSERT
            if (is_array($defs)): // Se $defs é um array então possui valor e tipo do dado
                $value = ((($defs['type'] > FDT_STR) && (isset($defs['value']))) ? $defs['value'] : null); //  pega o valor ou um tipo de arquivo a ser armazenado
                $type = (($value) ? $FIELD_PDO_TYPE[$defs['type']] : PDO::PARAM_NULL); // Se o valor for nulo retorna PDO::PARAM_NULL senão converte para o tipo correto
                $this->Create->bindParam(":{$field}", $value, $type); // Cria o parâmetro no PDO com o nome do campo, valor e tipo
            else:
                $this->Create->bindParam(":{$field}", $defs); // Cria o parâmetro PDO com o Default (PDO::PARAM_STR)
            endif;
        endforeach;

the given array contains:

[pk_web_messages] => 0
[fk_type_messages] => 3
[name_user] => Nome do usuario
[email_user] => [email protected]
[title_msg] => Mensagem nova com testes da classe
[phone_user] => (11) 3333-4444
[cel_user] => (11) 2222-3333
[end_user] => Rua do usuário, 1111
[compl_user] => apto 10
[city_user] => Cidade do usuário
[state_user] => SP
[cep_user] => 00.000-000
[msg_user] => 
    (
        [value] => Texto a ser inserido no campo Blob.
        [type] => 6
    )

see which type in the msg_user field means the mapping to insert objects into the BLOB field which becomes the PDO constant::PARAM_LOB. If I debug php variables everything works correctly, but when doing Pdostatement::execute() it writes a lot of nonsense in the database, or gives conversion error of variables, or error that truncates the data to the field.... What can it be?

  • You really need this $FIELD_PDO_TYPE? No longer passing the right type? It could simply be $type = $value ? $defs['type'] : PDO::PARAM_NULL;

  • I think so far I could simplify $value = isset($defs['value']) ? $defs['value'] : null;

  • Yes repeat in all fields the value of the array definition of the msg_user field. ex: it tries to pass the whole array to the fields then gives the following error: Notice: Array to string Conversion in . /include/data/Create.class.php on line 135

  • $FIELD_PDO_TYPE is an array that converts types as I use them for PDO sides. I only use for better reading purposes In the type index of the msg_user field I use a FDT_TEXT constant that has the value 6;

  • Boy.... I switched bindParam for Bindvalue and it worked fine.... Problem solved... Thank you... but I’m still gonna take this test you sent.

  • You don’t even have to test the rest, I got it right. bindparam is to get the return result, not to send. I was paying attention to other things and didn’t even notice. In fact the bindvalue is to put the value inside the query, bindparam is to catch the return of the result lines. I was looking for the ants and did not see the elephant passing by.

  • ok... then binValue on it.... heheheheh... thanks for the help.

  • In fact the ideal would be the @Raphael restore his, which was right. I didn’t understand why he removed

  • @Bacco restored, but I removed why they downvote for no reason, and I do not want to help who gives downvote and does not say the reason.

  • @Raphaelcaldas would suggest not to worry about votes, if the answer is what the author needs, naturally attracts positive. The Alcindo can mark your answer as accepted, inclusive, so already closes the question with the correct solution.

  • @Bacco is why I’m new here, I created the account yesterday just to help those who need and I do a post and I get downvote for no reason, it’s kind of boring you know, but I’ll follow what you said, now just wait if I’m going to be chosen as the correct answer, thanks for the force :D

  • @Raphaelcaldas holds the ends there, and don’t worry about a vote. I edited it to make the answer clearer. You don’t need to, but if you want to add a little extra to it explaining the difference between the two things, it helps people see the solution and attracts more votes (and editing it goes to the top of the site’s entry list, drawing attention too).

  • Putz.... I haven’t even seen this @Raphaelcaldas, sorry, I don’t even know how to do this so-called vote but I’ll try to correct it now.... Your solution saved me in this case.... Thank you very much indeed.

Show 8 more comments

1 answer

2


That one bindParam is wrong, the right would be to use the bindValue:

$this->bindValue(':campo',$string,PDO::PARAM_STR);

Explaining the difference between the bindParam and the bindValue(tip from @Bacco)

bindParam:

$sexo = 'masculino';
$s = $dbh->prepare('SELECT nome FROM cadastro WHERE sexo = :sexo');
$s->bindParam(':sexo', $sexo); // usado bindParam para vincular a variável
$sexo = 'feminino';
$s->execute(); // executado como WHERE sexo = 'feminino'

bindValue:

$sexo = 'masculino';
$s = $dbh->prepare('SELECT nome FROM cadastro WHERE sexo = :sexo');
$s->bindValue(':sexo', $sexo); // usado bindValue para vincular o valor da variável
$sexo = 'feminino';
$s->execute(); // executado como WHERE sexo = 'masculino'

Explaining:

To bindParam receives the variable by reference and the bindValue uses the variable only as a value.

Examples taken from: http://devblog.drall.com.br/algumas-diferencas-praticas-entre-pdo-bindparam-e-bindvalue/

  • I’d like Downvoter to give me a reason for the downvote, if possible

  • This is the problem. Perfect solution. I changed the bindParam, for bindValue and fly... all round. As explained bindParam refers to a php variable, so it doesn’t matter if it contains a value in the command bindParam, yes after the command populates the variable with the value to be sent to the database. This is useful when running several times the same SQL with different data, an example would be repeated Inserts in a table changing only the value of php variables, no need for a new prepare and new bindParam.

  • Again I’m sorry, because I didn’t know about this voting case.... now that I understand how it works.... It’s hard in a hurry... kkkkkk But thank you so much for your clarification.

Browser other questions tagged

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