I am trying to insert multiple records in MySQL with PDO via the MVC pattern, but when I open the view I get the response "Data has not been sent" and when I try to send the records, they are not stored in the database. What am I doing wrong or what am I missing?
This is what I have as an example:
MySQL table:
CREATE TABLE `valor`.`prueba`
( `documento` INT(15) NOT NULL , `nombre` VARCHAR(50) NOT NULL )
ENGINE = InnoDB;
View:
<?php
require_once "controlador.php";
require_once "modelo.php";
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<form role="form" method="post">
<table>
<thead>
<tr>
<th>Documento</th>
<th>Nombre</th>
</tr>
</thead>
<tbody>
<?php for ($i=0; $i < 2; $i++) :?>
<tr>
<td>
<input type="text" name="documento[]">
</td>
<td>
<input type="text" name="nombre[]">
</td>
</tr>
<?php endfor; ?>
</tbody>
<input type="submit" name="guardar" value="Guardar">
<?php
$ingresarDatos = new ControladorDatos();
$ingresarDatos -> ctrIngresarDato();
?>
</form>
</body>
</html>
Controller:
<?php
class ControladorDatos {
static public function ctrIngresarDato(){
if (isset($_POST["documento"])) {
$tabla = "prueba";
$datos = array("documento" => $_POST["documento"], "nombre" => $_POST["nombre"]);
$respuesta = ModeloDatos::mdlIngresarDatos($tabla, $datos);
if ($respuesta == "ok") {
echo "Se han insertado los datos";
} else {
echo "No se han insertado los datos";
}
}
}
}
Model:
<?php
class Conexion{
static public function conectar(){
$link = new PDO("mysql:host=localhost;dbname=valor","root","");
$link -> exec("set names utf8");
return $link;
}
}
class ModeloDatos{
static public function mdlIngresarDatos($tabla, $datos){
$stmt = Conexion::conectar()->prepare("INSERT INTO $tabla(documento, nombre) VALUES (:documento, :nombre)");
foreach ($datos as $value) {
$stmt->bindParam(":documento", $value["documento"], PDO::PARAM_STR);
$stmt->bindParam(":nombre", $value["nombre"], PDO::PARAM_STR);
}
if ($stmt->execute()) {
return "ok";
} else {
return "error";
}
$stmt = null;
}
}
Currently when you send the form to the method
ModeloDatos::mdlIngresarDatos()
an array arrives like this:So when you run them
bindParam()
you are passing an array instead of a string and it tries to do the conversion.To solve this we can do it in several ways, build a query with the same amount of data to insert, that is to say something like this, it is more laborious to perform even more efficient:
Execute the query as many times as we need with the different values, but preparing the query only once, easy to do and quite efficient
For example
editing
ModeloDatos::mdlIngresarDatos
Note: In this example, the insertion of the records is not being controlled properly, so if any of the inserts fails, those that have been correctly executed previously will be inserted. To avoid this behavior transactions should be used.
Also, if it
ModeloDatos::mdlIngresarDatos()
is reused multiple times and you are not interested in executing the foreach inside, another option could be to cycle byctrIngresarDato()
preparing and executing the query for each record. The least efficient.Which one to choose, it is a matter of your needs, it is not the same if they are always going to be blocks of 2 rows or on the contrary they can be blocks of tens of rows.