Error inserting data with PDO and POO

Navigation
2

Greetings community. I am developing an application using PDO, I am starting and I have had a problem when inserting records in the database. Next I put the functions that I am using and the error that is giving me.

First my controller receives the data in the following way: 

public function agregar(){
        if(!$_POST){
            $datos = $this->seccion->listar();
            return $datos;
        }else{
            $permitidos = array("image/jpeg", "image/png", "image/gif", "image/jpg"); // valido los formatos de imagen permitidos
            $limite = 700;
            if(in_array($_FILES['imagen']['type'], $permitidos) && $_FILES['imagen']['size'] <= $limite * 1024){ //valido si el formato y el peso son aceptados
                $nombre = $_FILES['imagen']['name'] . date('is'); //creo el nombre de la imagen
                $ruta = "Views" . DS . "templates" . DS . "imagenes" . DS . $nombre; //asigno la ruta de la imagen
                move_uploaded_file($_FILES['imagen']['tmp_name'], $ruta);
                $this->estudiante->set("nombre" , $_POST['nombre']);
                $this->estudiante->set("edad" , $_POST['edad']);
                $this->estudiante->set("promedio" , $_POST['promedio']);
                $this->estudiante->set("imagen" , $nombre);
                $this->estudiante->set("id_seccion" , $_POST['id_seccion']);
                $this->estudiante->add();
                echo error_reporting();
            }
        }
    }

Then that data is received by the set () function that is in my models 

public function set($atributo, $contenido){
        $this->$atributo = $contenido;
    }

Which passes that data to my add () function in the model 

        public function add(){
        $sql = "INSERT INTO estudiantes(nombres, edad, promedio, img, id_seccion)
        VALUES(:nombre, :edad, :promedio, :imagen, :id_seccion)";

        $params = [':nombre' => $this->nombre];
        $params = [':edad' => $this->edad];
        $params = [':promedio' => $this->promedio];
        $params = [':imagen' => $this->imagen];
        $params = [':id_seccion' => $this->id_seccion];

        $this->con->consultaSimple($sql, $params);

    }

Finally, I execute the query with this function 

public function consultaSimple($sql, $binds = []){
        $datos = $this->con->prepare($sql);

        foreach ($binds as $key => $val) {
            $datos->bindParam($key, $val);
        }

        $datos->execute();
    }

Those are all the functions that I use to insert, the error that is generating me is the following:

Warning: PDOStatement :: execute (): SQLSTATE [HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C: \ xampp \ htdocs \ pdo \ Models \ Connection.php on line 25 22527

I clarify that the line where the error is thrown is in the function SimpleSpeed in the execute () The truth is that I have not been able to solve this error and I have given it many times already and I have not been successful.

    
asked by Alejo Mendoza 31.12.2017 в 02:03
source

2 answers

2

Edited response

Good point the one bounded by @xerif in his comment to this answer, so I retract the following:

I think you're drowning in a glass of water. The problem is that in consultaSimple($sql, $binds = []) you clean your array by doing $binds=[] , that is, the function interprets that the parameter is always an empty array.

Actually, as Xerif says, the array was being overwritten and that would be the main reason for the error.

Nonetheless, I maintain my answer as to what follows:

  • I do not see too much use to pass the code to a method only to prepare and execute the query, taking into account that prepare and execute are already PDO's own methods.

  • Much less I see sense to do the binding using a loop for (although you have all the freedom to do it that way if you want). I say this because in PDO (and that is one of its wonders with respect to mysqli), you can pass the array with the parameters directly in execute , without using bindValue .

  • I would focus more on improving the function add , for example, controlling what happens in it, its possible errors and causing it to return a result, be the number of rows inserted or an error message. Also the function add could be more global, giving it, and not%% of%, the possibility of receiving the SQL statement and the parameters.

    • For example: 

        $sql = "INSERT INTO estudiantes (nombres, edad, promedio, img, id_seccion) 
    VALUES (:nombre, :edad, :promedio, :imagen, :id_seccion)";
    /*Construimos el array de este modo, así evitaremos errores como el inicial*/
    $arrParams=array (  ':nombre' => $this->nombre, 
                        ':edad' => $this->edad, 
                        ':promedio' => $this->promedio, 
                        ':imagen' => $this->imagen, 
                        ':id_seccion' => $this->id_seccion);

    /*Pasamos al método add la consulta y los parámetros, recuperando en una variable su respuesta*/
    $intResultado=add($sql, $arrParams);

    /*Aquí podemos evaluar el resultado de la llamada, para fines de información*/
    if ($intResultado >0){
        //Se insertaron datos
    }else{
        //No se insertaron datos
    }



/*Una función de agregación que podemos usar con cualquier consulta insert*/
public function add($sql, $arrParams){
    /*Preparamos*/
    $stmt=$this->con->prepare($sql);

    /*
      *Ejecutamos pasando los parámetros recibidos
      *Quiero INSISTIR en este punto, porque es una de las grandes ventajas de PDO
      *en el método execute se pueden pasar los parámetros directamente
      *sin tener que abrir bucles o recurrir a procedimientos complicados
      *por ejemplo en el caso de consultas dinámicas
    */
    $insert=$stmt ->execute($arrParams);

    /*Usamos un operador ternario para saber lo que ocurrió en la consulta*/
    $intResultado = ($insert) ? $stmt->rowCount() : 0;

    /*La función devolverá 0 en caso de no inserción o de error, o el número de filas insertadas*/
    return $intResultado;
}

    In this way the consultaSimple method can be used from anywhere.

        
    answered by 31.12.2017 / 04:55
    source
    1

    Clearly the error is in the function add() note that you are writing about $ param all the time, so in the end you only send a single value to consultaSimple()

    This part 

        public function add(){
    $sql = "INSERT INTO estudiantes(nombres, edad, promedio, img, id_seccion)
    VALUES(:nombre, :edad, :promedio, :imagen, :id_seccion)";

    $params = [':nombre' => $this->nombre];
    $params = [':edad' => $this->edad];
    $params = [':promedio' => $this->promedio];
    $params = [':imagen' => $this->imagen];
    $params = [':id_seccion' => $this->id_seccion];

    $this->con->consultaSimple($sql, $params);

}

    It should look like this 

        public function add(){
    $sql = "INSERT INTO estudiantes(nombres, edad, promedio, img, id_seccion)
    VALUES(:nombre, :edad, :promedio, :imagen, :id_seccion)";

    $params[':nombre'] = [$this->nombre];
    $params[':edad'] = [$this->edad];
    $params[':promedio'] = [$this->promedio];
    $params[':imagen'] = [ $this->imagen];
    $params[':id_seccion'] = [$this->id_seccion];

    $this->con->consultaSimple($sql, $params);

}
        
    answered by 31.12.2017 в 10:59
    Image does not load with JavaScript How can I change the text content by clicking on it in HTML / CSS?