Retrieve dynamic input and insert into the bank with Procedure

Asked

Viewed 668 times

6

I’m having trouble inserting incoming data from a dynamic input. My Procedure only inserts once, follows my code where I receive the data:

   $ddd     = $_POST['ddd'];
   $userid  = $_POST['userid'];
   $idusuario = $_POST['idusuario'];
   $telefone    = $_POST['telefone'];
   $obs         = $_POST['obs'];
   $quant_linhas = count($ddd);

   for ($i=0; $i<$quant_linhas; $i++) {

        $sql = "Call telefone_i(
        '".$userid[$i]."',
        '".$ddd[$i]."',
        '".$telefone[$i]."',
        '".$obs[$i]."',
        '".$idusuario[$i]."'

         )";

   }

In this section is the jQuery that creates dynamic inputs:

<script type="text/javascript">
$(function () {
  function removeCampo() {
    $(".removerCampo").unbind("click");
    $(".removerCampo").bind("click", function () {
       if($("tr.linhas").length > 1){
        $(this).parent().parent().remove();
       }
    });
  }

  $(".adicionarCampo").click(function () {
    novoCampo = $("tr.linhas:first").clone();
    novoCampo.find("input").val("");
    novoCampo.insertAfter("tr.linhas:last");
    removeCampo();
  });
});

</script>

Follow the excerpt for inputs:

<div class="content" style="min-height: 150px;">
   <!-- CONTEUDO INICIO -->
    <center>
        <h2  style="background-color: #DDEBFF">Cadastrar Telefone</h2>
    </center>
    <div style=" margin-left:5px; padding: 5px; width: 600px; overflow:auto; border:#036 thin; border-style:dotted;">

<form action="telefone_inserir.php?id=<?php echo $usuario_id; ?>" method="post" enctype="multipart/form-data">
    <table class="list">
    <thead>
        <tr class="linhas">

            <td width="25%" class="right">DDD</td>

            <td class="left">

            <input type="text" name="ddd[]" value="" maxlength="2" style="width: 30px" />

            <input name="userid[]" type="hidden" value="<?php echo $id; ?>" />

            <input name="idusuario[]" type="hidden" value="<?php echo $usuario_id; ?>" />

            </td>

            <td width="25%" class="right">Telefone</td>

            <td class="left"><input type="text" name="telefone[]" value="" maxlength="9" style="width: 80px" /></td>

            <td width="25%" class="right">OBS:</td>

            <td class="left"><input type="text" name="obs[]" value="" maxlength="45" style="width: 200px" /></td>
            <td class="left"><a href="#" class="removerCampo" title="Remover linha"><img src="imagens/exc_btn.png" border="0" /></a></td>

        </tr>
          <tr><td colspan="7">
        <a href="#" class="adicionarCampo" title="Adicionar item"><img src="imagens/tel_btn.png" border="0" /></a>
    </td></tr>

         <tr>

            <td colspan="7" align="center" height="50">
                <input name="botao" type="image" value="Alterar" id="btn_salvar" src="imagens/cadastrar_btn.png">

            </td>
          </tr>


          </thead>
        </table>

    </form>

    </div>
<!-- CONTEUDO FIM -->
</div>

My previous:

CREATE DEFINER=`root`@`localhost` PROCEDURE `telefone_i`(
p_usuario_id int(11) ,
p_ddd int(3) ,
p_telefone int(9) ,
p_obs varchar(45) ,
p_usuario_lancamento int(11)
)
BEGIN

insert acad.telefone
    set
        usuario_id = p_usuario_id,
        ddd = p_ddd,
        telefone = p_telefone,
        obs = p_obs,
        data_lancamento = current_timestamp,
        usuario_lancamento = p_usuario_lancamento;
END

I tried to do it this way but it didn’t work:

   for ($i=0; $i<$quant_linhas; $i++) {

        $sql = mysqli_query($con, "Call telefone_i(
        '".$userid[$i]."',
        '".$ddd[$i]."',
        '".$telefone[$i]."',
        '".$obs[$i]."',
        '".$idusuario[$i]."'

         )");



   }

But my Procedure inserts only 1 record and returns the error "Query was Empty". I tried otherwise, but the same effect happens, just inserts a line and returns error. Follows the code snippet:

$msg =   mysql_query("Call telefone_i(
'".$userid[$i]."',
'".$ddd[$i]."',
'".$telefone[$i]."',
'".$obs[$i]."',
'".$idusuario[$i]."'

 )");

From now on, I appreciate any help.

php mysql mysqli
  • 1

    Its $id variable is an array?

  • not inputs are dynamically created with jquery the $id is to count the input numbers that are sent

  • This I understood... But what kind of data comes in it? An array, a string separated by comma?

  • @Marcelobonus opa was wrong in place of $id was $ddd and comes in an array yes

  • Like someone with 27 points offers a 50% reward ?

  • @opeta, I have already put an answer to your question, if she answers your question, mark it as accepted. Thank you.

  • @Taopaipai simple I was with 77 when you offer the reward it automatically comes out of your points

Show 2 more comments

4 answers

3


The problem seems to be in the way you are executing the submission and the way you are trying to insert the data into the database. One important detail I recommend is to remove the id of querystring, both for safety reasons and to avoid breaking the loop in your Ids.

If you need to pass something that should not be shown in your POST, send an input like "Hidden".

OBS: I was a little confused by the names because you have two camps userid and idusuario but I understood that one of them is the user_release, I suggest you change it so that your code becomes clearer, even for yourself: 

$mysqli = new mysqli('localhost','root','senha','acad');
if (mysqli_connect_errno()) {
   printf("Falha na conexão: %s\n", mysqli_connect_error());
   exit();
}

if ($_POST) {
   if (count($_POST['telefone'])) {
      $i=0;
       foreach ($_POST['telefone'] as $telefone) {

           $data = array(
                         $_POST['userid'],
                         $_POST['ddd'][$i],
                         $telefone,
                         $_POST['obs'][$i],
                         $_POST['idusuario']
                   );

           $SQL = "CALL telefone_i('" . implode("','",$data) . "')";
           if ( ($result = $mysqli->query($SQL))===false ) {
                printf("Erro da Query: %s\nQuery enviada: %s\n",
                        $mysqli->error, $SQL);
                exit();
           } 
          $i++;   
       } //endforeach
   } 
}

Your trial should look like this:

DELIMITER $$

USE acad$$

DROP PROCEDURE IF EXISTS telefone_i$$

CREATE PROCEDURE telefone_i(
p_usuario_id int(11),
p_ddd int(3),
p_telefone int(9),
p_obs varchar(45),
p_usuario_lancamento int(11)
)
BEGIN

INSERT INTO telefone (
  usuario_id,
  ddd, 
  telefone,
  obs,
  data_lancamento,
  usuario_lancamento
) VALUES (p_usuario_id,
         p_ddd,
         p_telefone,
         p_obs,
         now(),
         p_usuario_lancamento
);

END$$
DELIMITER ;

Your HTML should look like this:

<div class="content" style="min-height: 150px;">
   <!-- CONTEUDO INICIO -->
    <center>
        <h2  style="background-color: #DDEBFF">Cadastrar Telefone</h2>
    </center>
    <div style=" margin-left:5px; padding: 5px; width: 600px; overflow:auto; border:#036 thin; border-style:dotted;">
    <form action="telefone_inserir.php" method="post" id="insercao_tel">
        <table class="list">
           <thead>
                 <tr class="linhas">
                   <td width="25%" class="right">DDD</td>
                   <td class="left">
                      <input type="text" name="ddd[]" maxlength="2" style="width: 30px" />
                   </td>
                   <td width="25%" class="right">Telefone</td>
                   <td class="left">
                      <input type="text" name="telefone[]" maxlength="9" style="width: 80px" />
                   </td>
                   <td width="25%" class="right">OBS:</td>
                   <td class="left">
                      <input type="text" name="obs[]" maxlength="45" style="width: 200px" />
                  </td>
                  <td class="left">
                      <a href="#" class="removerCampo" title="Remover linha"><img src="imagens/exc_btn.png" border="0" /></a>
                  </td>
               </tr>
               <tr>
                  <td colspan="7">
                      <a href="#" class="adicionarCampo" title="Adicionar item"><img src="imagens/tel_btn.png" border="0" /></a>
                  </td>
               </tr>
               <tr>
                  <td colspan="7" align="center" height="50">
                     <input name="userid" type="hidden" value="<?php echo $id; ?>" />
                     <input name="idusuario" type="hidden" value="<?php echo $usuario_id; ?>" />
                     <input name="botao" type="image" value="Alterar" id="btn_salvar" src="imagens/cadastrar_btn.png">
                 </td>
              </tr>
           </thead>
       </table>
    </form>
    </div>
    <!-- CONTEUDO FIM -->
</div>

  • see if there’s a ddd post being posted. Try it again.

  • ok, I edited, check it out... just a detail. Your form should not pass the id, no action.

  • Your project expects an integer, not a null value. Note that in your post, $user_id must contain a value. in the output of the file they are not there, none of the Ids: p_usuario_id and p_usuario_lancamento ..., that is, when you created the fields with the click, failed to feed them with the values.

  • But from what I’m seeing in your code, when you duplicate the fields, you’re not duplicate them all, only part of them, that’s what’s wrong... you have to foreach the fields that will be duplicated. If the two ids are not going to be duplicated, there is no reason to put as array [].

  • I edited it, see if it solved the problem.

  • Because it has enctype="multipart/form-data" ? This is just for upload.

  • I removed this enctype="Multipart/form-data face worked I thank you so much for your help I think it was the 50 most sweaty points of your life kkkkkkk hug until the next.

  • I’m glad it worked out.

Show 3 more comments

0

Your insert Stored Procedure is not wrong ?

He’s as good as one update.

INSERT that’s how it is:

INSERT INTO TABELA (CAMPO_1, CAMPO_2) VALUES (VALOR_1, VALOR_2)

So in your SP it would be like this:

insert into acad.telefone
(
    usuario_id, ddd, telefone, obs, data_lancamento, usuario_lancamento
)
values
(
   p_usuario_id,
   p_ddd,
   p_telefone,
   p_obs,
   current_timestamp,
   p_usuario_lancamento
)

  • was actually incorrect and has already been modified

0

Not the need to create a precedent to make this Insert, and in this case you are also losing performace, as it is opening several transactions in the database.

The best way to do and making a INSERT as follows.

INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);

Then use the following code:

$sql = "insert into acad.telefone (usuario_id, ddd, telefone, obs, data_lancamento, usuario_lancamento) VALUES ";

for ($i=0; $i<$quant_linhas; $i++) {
    $sql .= "(". '".$userid[$i]."' . "," . '".$ddd[$i]."' . "," . '".$telefone[$i]."' . "," . '".$obs[$i]."' . "," . '".$idusuario[$i]."' . ") "
}

  • unfortunately I have to follow the procedures

0

Your procedure is wrong, to enter values through a precedent, you must do something like this:

CREATE PROCEDURE _nome(...)
BEGIN 
     INSERT | UPDATE | DELETE
     INTO   | SET    | FIELD
     VALUES | WHERE  | WHERE
     ...
END

Take the example:

DELIMITER //
CREATE PROCEDURE telefone_i(
                            p_usuario_id INT(11),
                            p_ddd INT(3),
                            p_telefone INT(9),
                            p_obs VARCHAR(45),
                            p_usuario_lancamento INT(11)
                            )
    BEGIN
        INSERT INTO acad.telefone (
            usuario_id,
            ddd,
            telefone,
            obs,
            data_lancamento,
            usuario_lancamento
        )   
        VALUES (
            p_usuario_id,
            p_ddd,
            p_telefone,
            p_obs,
            p_data_lancamento,
            p_usuario_lancamento
        );
    END //

In the script php you can just do this:

if(isset($_POST)){
    $i=0;
    foreach($_POST as $nome){
        $procedure = "CALL telefone_i({$_POST['userid'][$i]}, {$_POST['ddd'][$i]}, {$_POST['telefone'][$i]}, '{$_POST['obs'][$i]}', {$_POST['idusuario'][$i]})";
        if(!($query = $con->query($procedure))){
            die($con->error);
        }
        $i++;
    }
}

Within this loop, read the values sent from the form , can check, and so on.

  • it inserts only the first and gives this error "You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ' 21, 964071201, 'cellular', )' at line 1"

Browser other questions tagged php mysql mysqli

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