php – PDO Insert multiple lines in the same insert using bindParam

Question:

I have a standard function that I use for all my inserts (when they only have 1 insert at a time), which would be:

insereRegistro($sql, $param=NULL) {
    $query = $conn->prepare($sql);

    //Converte os parâmetros para bindParam
    if ( isset($param) ) {
        foreach ($param as $key => $value) {
            $$key = $value;
            $query->bindParam($key, $$key);
        }
    }

    $query->execute();
    $response = $conn->lastInsertId();
}

But I would like to insert several lines in the same query INSERT INTO table (campo, campo2) VALUES (:campo, :campo2), (:campo, :campo2).. But I don't know how I should proceed so that the function can do this treatment and perform the insertions.

The existing function doesn't need to be modified, I can create a unique one for this use, but the problem is that I don't know how I should assemble the structure so that each group of values ​​to be inserted passes through bindParam .

Answer:

Based on the rray comment, I managed to create a function that gives me the desired result. When using bindParam it creates the reference based on the value of a variable, so when updating the variable's value, the value to be inserted into the database will also be updated by reference (I'm not a PHP literate but if I understand correctly, this one is the logic).

So what I needed to do was create a variable reference in bindParam and then update the variable value for each insertion inside a foreach , so the function bindParam like this:

function insereMultiplosRegistros($sql, $param, $valor) {
    $query = $conn->prepare($sql);

    foreach ($param as $key => $value) {
        $query->bindParam($key, $$value);
    }

    foreach ($valor as $row) {
        foreach($row as $key => $value) {
            $$key = $value;
        }
        $query->execute();
    }

    return $conn->lastInsertId();
}

And to run the function, just pass the parameters like this template:

$sql = "INSERT INTO table (campo, campo2) VALUES (:campo, :campo2)";
$param = [
    ':campo' => 'campo',
    ':campo2' => 'campo2'
];

$valor = array();
foreach ($array as $key => $value) { //Deve ser modificado conforme a extensão da array e dos campos
    $object = (object) [
        'campo' => $key,
        'campo2' => $value
    ];
    array_push($valor, $object);
}

insereMultiplosRegistros($sql, $param, $valor);

Maybe this can still be optimized to be more dynamic and with less code, but that's what I managed to work out with my knowledge.

Scroll to Top
AllEscort