PDO Insert multiple row in same insert using bindParam

Question

PDO Insert multiple row in same insert using bindParam

Navigation

#1 by (1 votes)

3

I have a default 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 rows in a same query INSERT INTO table (campo, campo2) VALUES (:campo, :campo2), (:campo, :campo2).. But I do not know how to proceed so that the function can do this processing and execute the inserts.

I do not need to modify the existing function, I can create a unique one for this use, but the problem is that I do not know how to mount the structure so that the group of values to be entered passes through bindParam . >

php sql pdo

asked by anonymous 06.01.2017 / 13:18

1 answer

Importing nondescript files with NodeJS Display a div when clicking on the close of the browser window [duplicate]

score 1 · Accepted Answer

Based on the comment from rray, I was able to work out 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 value of the variable, the value to be inserted in the database will also be updated by reference (I am not PHP understander but if I understand it well , this is the logic).

So, what I needed to do was create a variable reference in bindParam and then update the variable value for each insert inside a foreach , getting the function 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 execute the function, simply 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 stay more dynamic and with less code, but that's what I was able to work out with my knowledge.