Is there a downside of inserting this array to table

[jemz]

Hi can I ask although my code works fine, but I need to know if there is a downside of inserting this array of data to table by using loop ? . Is there other way to do this ?

  if(!empty($arraydata)){

            foreach($arraydata as $data) {
                $stmt = $this->connection->prepare("INSERT INTO project (type_id, employee_id) VALUES (?,?)");

                $stmt->bindParam(1, $data,PDO::PARAM_INT);
                $stmt->bindParam(2, $userid,PDO::PARAM_INT);
                $stmt->execute();
                $stmt = null;
            }


        }

Thank you in advance.

[Drummin]

Move the prepare line to above the loop. Other than that looks fine (to me).

[jemz]

You mean like this

 if(!empty($arraydata)){
          $stmt = $this->connection->prepare("INSERT INTO project (type_id, employee_id) VALUES (?,?)");
            foreach($arraydata as $data) {
               

            $stmt->bindParam(1, $data,PDO::PARAM_INT);
            $stmt->bindParam(2, $userid,PDO::PARAM_INT);
            $stmt->execute();
            $stmt = null;
        }


    }

[Mittineague]

$stmt = null;
looks a bit scary. Maybe move that below the loop?

[jemz]

Thank you

[myesain84]

You only need to perform the parameter binding once. After a parameter is bound, you can modify the content of the bound variable however you need to. Then when the statement is executed, the current value stored in that variable is submitted to the DB to be processed. This means that you can also move the bindParam lines outside the loop as well:

if (!empty($arraydata)) {
    $data = "";// initialize the bound variable
    
    $stmt = $this->connection->prepare("INSERT INTO project (type_id, employee_id) VALUES (?,?)");
    $stmt->bindParam(1, $data, PDO::PARAM_INT);
    $stmt->bindParam(2, $userid, PDO::PARAM_INT);
    
    foreach ($arraydata as $data) {
        $stmt->execute();
    }
    $stmt = null;
}

This will be provide a slight performance bump as well because the parameters only have to be bound once rather than for each item in the array.