PDO Insert multiple lines into a single Insert using bindParam

Asked

Viewed 820 times

3

I have a default function that I use for all of 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 one query INSERT INTO table (campo, campo2) VALUES (:campo, :campo2), (:campo, :campo2).. But I do not know how I should proceed so that the function can do this treatment and perform the inserts.

It does not need to be modified the existing function, I can create a unique one for this use, but the problem is that I do not know how to assemble the structure so that dear group of values to be inserted goes through bindParam.

php sql pdo
  • 1

    Seria that ?

  • @rray great! I’m making a Query Builder library in PHP, I’ll take this tip.

  • @rray more or less... But I managed to understand the logic and I can solve here! Vlw!

  • 2

    @Wallacemaxters body library Builder?

  • @Wallacemaxters see if the answer also helps in something ;)

  • http://answall.com/questions/174354/como-inserir-todos-valores-do-array-e-evitar-query-execute-a-cada-execu%c3%a7%c3%a3o-d/174356#174356

Show 1 more comment

1 answer

1


Based on the rray comment, I was able to elaborate a function that gives me the desired result. By 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’m not a PHP expert but if I understood correctly, this is the logic).

So what I had to do was create a reference of variables in the bindParam and then update the value of the variable for each insert within a foreach, leaving the function thus:

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 perform the function, just pass the parameters like this model:

$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 less code, but that’s what I was able to elaborate with my knowledge.

Browser other questions tagged php sql pdo

You are not signed in. Login or sign up in order to post.