INSERT query does not work inside a loop

Question

So guys, I’m not getting to solve this query problem within a foreach loop not run. At first I thought it was something wrong with Pdo->beginTransaction so I commented on that part and left only pure code.

    <?php
    if (isset($_POST["nome"])):
        try{
            // Começo da validação
            //$pdo->beginTransaction();
            // Validação do campo nome
            if ($_POST["nome"] != null):
                $procurarnome=$pdo->prepare("SELECT id FROM users WHERE username=$name");
                $procurarnome->execute();
            else:
                die("Houve um erro no sistema, contate um administrador!");
            endif;
            // Validação dos campos dinâmicos
            $cadastraresposta=$pdo->prepare("INSERT INTO form_respostas(perguntaid,username,reposta)VALUES(:perguntaid,$name,:resposta)");
            foreach ($listarpergunta as $pergunta) {
                if ($_POST["pergunta$pergunta->id"] != null):
                    $resposta=addslashes($_POST["pergunta$pergunta->id"]);
                    $cadastraresposta->bindParam(":perguntaid",$pergunta->id,PDO::PARAM_INT);
                    $cadastraresposta->bindParam(":resposta",$resposta);
                    $cadastraresposta->execute();
                    var_dump($cadastraresposta->execute());
                else:
                    $pdo->rollBack();
                    die("Preencha todos os campos corretamente!<br />");
                endif;
            }
            // Todos os arquivos foram preenchidos corretamente
            //$pdo->commit();
            echo "Obrigado!";
        }
        catch(PDOException $pe){
             //$pdo->rollback();
             die($pe->getMessage());
        }
    else:
?>

INSERT querys inside the foreach do nothing in the database and this var_dump returns me a value "false Boolean". What should I do?

Answer 1

I think you have to trigger the bind off the foreach, try it like this:

$cadastraresposta=$pdo->prepare("INSERT INTO form_respostas(perguntaid,username,reposta)VALUES(:perguntaid,$name,:resposta)");
$cadastraresposta->bindParam(":perguntaid",$perguntaid,PDO::PARAM_INT);
$cadastraresposta->bindParam(":resposta",$resposta);
foreach ($listarpergunta as $pergunta) {
    if ($_POST["pergunta$pergunta->id"] != null):
        $perguntaid=$pergunta->id;
        $resposta=addslashes($_POST["pergunta$pergunta->id"]);
        $cadastraresposta->execute();
    else:
        $pdo->rollBack();
        die("Preencha todos os campos corretamente!<br />");
    endif;
}

Manual PHP:

http://php.net/manual/en/pdo.prepared-statements.php

http://php.net/manual/en/pdostatement.bindparam.php

Answer 2

I see some problems in the code.

1. Jader is right, the bindParam should be necessary only once, outside the foreach, otherwise, nor would it make sense to have an extra variable and this function would not be like this;

2. I don’t think it’s safe to write "pergunta$pergunta->id", may be that PHP uses $pergunta as variable and continue with the text "->id".

3. If the array index does not exist in the array $_POST a call returns null?

Suggestions:

1. To accept Jader’s suggestion;

2. Use another variable for the bind of the question id. It may not be necessary, but it seems simpler to me;

3. Rewrite the indexes of $_POST as "pergunta${perguntaId}" or something similar, which can be more reliable;

4. Wow, I didn’t even know if could be written like this. Traditional is no longer readable and with greater support from code editors?

5. I think when using bind, strings do not need to be treated with addslashes, but you should test this part because I’m not sure;

6. I’ll leave the test for null, but it may be necessary to change, do not remember how it is in fact.

7. Check the variable $name;

8. I particularly like to initialize variables before use;

9. Jader noted the lack of string delimiters in the query, substituting for 'name'.

The relevant part of the code would look like this:

// Validação dos campos dinâmicos
$cadastraresposta=$pdo->prepare("INSERT INTO form_respostas (perguntaid, username, reposta) VALUES (:perguntaid, '$name', :resposta)");
$perguntaId = 0;
$resposta = 0;
$cadastraresposta->bindParam(":perguntaid", $perguntaId, PDO::PARAM_INT);
$cadastraresposta->bindParam(":resposta", $resposta);
foreach ($listarpergunta as $pergunta) {
  $perguntaId = $pergunta->id;
  $resposta = $_POST["pergunta${perguntaId}"];
  if ($resposta != null) {
    $cadastraresposta->execute();
    if ($cadastraresposta->errorCode() != 0) {
      print_r($cadastraresposta->errorInfo());
      die("Erro ao executar Query!");
    }
  } else {
    $pdo->rollBack();
    die("Preencha todos os campos corretamente!<br />");
  }
}
// Todos os arquivos foram preenchidos corretamente
//$pdo->commit();
echo "Obrigado!";

If it doesn’t work, try to see what’s wrong with the PDO::errorCode or PDO::errorInfo.

Answer 3

The post is old but should help in the future; the solution is to use bindValue instead of bindParam.