SQLSTATE[HY093]: Invalid parameter number PHP PDO + MYSQL - form

Question

Hi I have a problem I have a record where 4 tables and each of them relates to their use of the lastInsertId to take the last inserted id generated but nevertheless I get this little problem.

THIS IS MY CODE CLASS:

public function regnuevo($ruc,$pwd,$rsocial,$ncomercial,$direfiscal,$telefono,$web,$rucs,$direfac,$nombre,$paterno,$materno,$cargo,$celular,$email){


        try{

            $insertar = $this->bd->prepare("INSERT INTO empresa (ruc,pwd,razonsocial,nombrecomercial,direccionfiscal,departamento,distrito,provincia,estado) values (:ruc,:pwd,:rsocial,:ncomercial,:direfiscal,15,1249,129,1)");

            $insertar->bindparam(":ruc",$ruc);
            $insertar->bindparam(":pwd",$pwd);
            $insertar->bindparam(":rsocial",$rsocial);
            $insertar->bindparam(":ncomercial",$ncomercial);
            $insertar->bindparam(":direfiscal",$direfiscal);
            $insertar->execute();
            $ultimoid = $this->bd->lastInsertId();

            $insertar = $this->bd->prepare("INSERT INTO empresa_detalle (telefono1,web,ruc,id_empresa) values(:telefonos,:web,:ruc,:id_emp)");
            $insertar->bindparam(":telefonos",$telefono);
            $insertar->bindparam(":web",$web);
            $insertar->bindparam(":ruc",$rucs);
            $insertar->bindparam(":id_emp",$ultimoid);
            $insertar->execute();

            $insertar = $this->bd->prepare("INSERT INTO datosfacturacion (direccion,id_empresa) values(:dire,:id_empf)");
            $insertar->bindparam(":dire",$direfac);
            $insertar->bindparam(":id_empf",$ultimoid);
            $insertar->execute();

            $insertar1 = $this->bd->prepare("INSERT INTO socios(nombre,apaterno,amaterno,cargo,grado,tipodocumento,genero,celular,correooficina,estado,id_empresa) values(:nombre,:apaterno,:amaterno,:cargo,1,1,1,:telefono,:email,'1',:id_empresa)");

            $insertar1->execute(array(":nombre" => $nombre,
                                     "apaterno"=>$paterno,
                                     ":materno"=>$materno,
                                     ":cargo"=>$cargo,
                                     ":celular"=>$celular,
                                     ":id_empresa"=>$ultimoid,
                                     ":email"=>$email));

                return true;

        }
        catch(PDOException $e)  {


            echo $e->getMessage();
            return false;
    }

THIS IS A FORM :

<div class="container">
   <form action="reg_nuevo.php" class="form-horizontal" method="post">
       <div class="form-group">
          <p class="txticon"><span class="glyphicon glyphicon-plus-sign iconos"></span> Registro de Nuevo Socio</p>
          *Solo se puede registrar 3 socios por empresa en caso de tener 4 socios comuniquese con el área de Sistemas
       </div>
       <div class="form-group">
           <label for="" class="control-label col-md-2">R.U.C</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="ruc">
           </div>
            <label for="" class="control-label col-md-2">Razón Social</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="raz">
           </div>
       </div>

       <div class="form-group">
           <label for="" class="control-label col-md-2">Nombre Comercial</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="comercial">
           </div>
              <label for="" class="control-label col-md-2">Dirección Fiscal</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="direccion">
           </div>
       </div>

       <div class="form-group">
           <label for="" class="control-label col-md-2">Telefono</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="telefono">
           </div>
           <label for="" class="control-label col-md-2">Dirección de entrega de Factura</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="facturacion">
           </div>
       </div>


       <div class="form-group">
           <label for="" class="control-label col-md-2">Web</label>
           <div class="col-md-3">
               <input type="text" class="form-control" name="web">
           </div>
       </div>

       <div class="form-group col-md-12">
         <div class="form-group child col-md-12">
           <p class="txticon"><span class="glyphicon glyphicon-user iconos"></span>Registro de Socio</p>

          <div class="form-group">
            <label for="" class="control-label col-md-1">Nombre</label>
           <div class="col-md-2">
               <input type="text" class="form-control" name="socio">
           </div>
            <label for="" class="control-label col-md-1">A.Paterno</label>
           <div class="col-md-2">
               <input type="text" class="form-control" name="paterno">
           </div>
            <label for="" class="control-label col-md-1">A.Materno</label>
           <div class="col-md-2">
               <input type="text" class="form-control" name="materno">
           </div>
       </div>

        <div class="form-group">
             <label for="" class="control-label col-md-1">Cargo</label>
           <div class="col-md-2">
               <input type="text" class="form-control" name="cargo">
           </div>
            <label for="" class="control-label col-md-1">Email</label>
           <div class="col-md-2">
               <input type="email" class="form-control" name="email">
           </div>
            <label for="" class="control-label col-md-1">Telefono</label>
           <div class="col-md-2">
               <input type="text" class="form-control" name="celular">
           </div>
       </div>
   <!--<input class="btn btn-danger deleteButton cerrar" type="button" value="Eliminar" />-->
</div>
</div>
<!--<input class="btn btn-success cerrar" type="button" id="create_button" value="Agregar Socio" />-->
<input class="btn btn-lg btn-info" type="submit" value="Registrar" name="enviar">
      </form>

@Armen Not required http://stackoverflow.com/q/9778887/ only in values are colons required. — Funk Forty Niner, May 02 '16 at 18:28

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

You're mixing parameters up: :celular should be :telefono. And :materno should be :amaterno in your $insertar1 query.

~~Looks like you need to fix "apaterno"=>$paterno, by adding a : to :apaterno~~

~~Like so: ":apaterno"=>$paterno,~~ (Re: Is the leading colon for parameter names passed to PDOStatement::bindParam() optional?)

Full code becomes:

$insertar1 = $this->bd->prepare(
    "INSERT INTO socios(
        nombre,
        apaterno,
        amaterno,
        cargo,
        grado,
        tipodocumento,
        genero,
        celular,
        correooficina,
        estado,
        id_empresa
    ) values (
        :nombre,
        :apaterno,
        :amaterno,
        :cargo,
        1,
        1,
        1,
        :telefono,
        :email,
        '1',
        :id_empresa
    )"
);

$insertar1->execute(array(":nombre" => $nombre,
                          ":apaterno"=>$paterno,
                          ":amaterno"=>$materno,
                          ":cargo"=>$cargo,
                          ":telefono"=>$celular,
                          ":email"=>$email,
                          ":id_empresa"=>$ultimoid
));

edited May 23 '17 at 11:52

Community

1
1

answered May 02 '16 at 18:24

mferly

1,646
1
13
19

Not required http://stackoverflow.com/q/9778887/ only in values are colons required as per your original http://stackoverflow.com/revisions/36989101/1 – Funk Forty Niner May 02 '16 at 18:29
add : a class but the problem continues – Edinson Gutierrez Vargas May 02 '16 at 18:29
@Fred-ii- While it *may* work (I've never personally tested this so I cannot speak to it), I would advise against the practice of omitting them. The manual does not state that colons can be left out of the bindings, so it'd be best to go with that. But thank you for pointing it out. – mferly May 02 '16 at 18:45
1

No need for you to test it Marcus, it's a little known "fact" ;-) See all the links in there. – Funk Forty Niner May 02 '16 at 18:46
Haha. I must test to know for sure ;) It's just how I do. But not saying I disagree with you. Don't get me wrong. – mferly May 02 '16 at 18:48

score 1 · Accepted Answer · answered May 02 '16 at 18:27

1

The last insert are wrong, are missing two named placedholeders, :celular( maybe could be :telefono), :materno (:amaterno) does not exists

        $insertar1 = $this->bd->prepare("INSERT INTO socios(nombre,apaterno,amaterno,cargo,grado,tipodocumento,genero,celular,correooficina,estado,id_empresa)
        values(:nombre,:apaterno,:amaterno,:cargo,1,1,1,:telefono,:email,'1',:id_empresa)");

$insertar1->execute(array(":nombre" => $nombre,
                                 "apaterno"=>$paterno,
                                 ":materno"=>$materno,
                                 ":cargo"=>$cargo,
                                 ":celular"=>$celular,
                                 ":id_empresa"=>$ultimoid,
                                 ":email"=>$email));

To fix just match names of array keys with place holders:

$insertar1->execute(array(":nombre" => $nombre,
                                 "apaterno"=>$paterno,
                                 ":amaterno"=>$materno,
                                 ":cargo"=>$cargo,
                                 ":telefono"=>$celular,
                                 ":id_empresa"=>$ultimoid,
                                 ":email"=>$email));

answered May 02 '16 at 18:27

rray

2,518
1
28
38

@EdinsonGutierrezVargas you say, the error is before the insert into socios ? – rray May 02 '16 at 18:42
Yes, the error is when it reaches the Insert jquery socios But the empresa, empresa_detalle and facturacion inserts But not socios table: / – Edinson Gutierrez Vargas May 02 '16 at 18:53
@EdinsonGutierrezVargas after the modify, the error message are the same? – rray May 02 '16 at 19:59
Yes,after modifying the error message is the same (SQLSTATE [HY093]: Invalid parameter number: parameter was not defined) – Edinson Gutierrez Vargas May 02 '16 at 20:32
The problem was soluccionado thank you very much , had not seen his materno look parameter and was amaterno – Edinson Gutierrez Vargas May 03 '16 at 14:33
@EdinsonGutierrezVargas it's nice :D. Could you accept one answer with green mark? – rray May 03 '16 at 14:34
I served ready thanks :) if someone verify it is the same good step parameters are habre me lol dream state , I will open another dynamic but this time is to add and DELETE: / I hope you can help me in thanks friends – Edinson Gutierrez Vargas May 03 '16 at 16:32

SQLSTATE[HY093]: Invalid parameter number PHP PDO + MYSQL - form

2 Answers2