INSERT query does not work inside a loop

Asked

Viewed 502 times

6

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?

  • PHP is configured to display errors?

  • 2

    Prepared Statements: Voce is doing it wrong

3 answers

2

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

  • Thanks for the tip, but unfortunately I didn’t succeed. I need to use foreach because it is a form with addition of fields dynamically, registered in the database...

  • 1

    I did not remove the foreach, just removed the bindparam from inside it, see the manual the bind need not repeat...

  • But wouldn’t the bind have to come after the variable? Remembering that $reply passes more than one result because of the dynamic post...

  • 1

    No, you specify that you want to listen to that variable for that parameter, so if it changes its value, the command $cadastraresposta->execute(); will take the current value of the variable,

1

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.

  • 1

    about #4, it is a form widely used in views, as it is easier to find the beginning and end than {...}... #8, $perguntaId = 0 set to zero makes no sense at all

  • As Pope Charlie said, I also did not understand the definition of the variable as 0, even though it did not work. I echo the post variables and they return the value correctly, I do not know why it is not entering... Who knows some better way than the foreach to do this?

  • Check in the query where you have ,$name, I think it should be ,'$name',

  • @Jader Well noted, I’ll edit the answer. @Papacharlie I prefer the traditional but it’s good to know the other way, when assigning zero, it could be any number or string, I just like to initialize. Moreover, bindParam needs a reference and I don’t like to rely on very automatic things. @Matheusmelo Check out Jader’s observation.

  • 1

    His phrase "I like to boot" reminded me when programming in ASP. I did not refer exclusively to bindParam, but yes to the declaration of any variables with attribute 0 or null before inserting a real value.

  • 1

    @Mateusmelo Check Jader’s remark about limiting the string $name in SQL, also check if there is error in query, I added the code to check the answer. There is some output (any) in html?

Show 1 more comment

-2

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

  • 3

    Hello user3474474, you could give a concrete example of code?

Browser other questions tagged

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