foreach INSERT into Mysql DB is really slow, specially when inserting over 100K records and over

Question

How could I make this code faster. It's fine when I insert 100 records into my database but it takes really long time when I insert let's say 500K records. I've tried to use implode in my code but it's not working. Code seems to have two foreach loops, one inside the other, but I can't find a way to make it work, does anyone has an idea? My framework is codeigniter.

Here's what the code looks like:

<?php  
 function Add_multiple_users($values)
{
    $err = '';
    foreach($values as $rows)
    {
        $clientQuery = 'INSERT INTO
                            client
                            (
                                admin_id,
                                create_time
                            )
                            VALUES
                            (
                                "'.$this -> session -> userdata('user_id').'",
                                "'.date('Y-m-d H:i:s').'"
                            )';
        $clientResult = @$this -> db -> query($clientQuery);
        if($clientResult)
        {
            $client_id = $this -> db -> insert_id();
            foreach($rows as $row)
            {
                $attrQuery = 'INSERT INTO
                                    client_attribute_value
                                    (
                                        attribute_id,
                                        client_id,
                                        value
                                    )
                                    VALUES
                                    (
                                        "'.$row['attribute_id'].'",
                                        "'.$client_id.'",
                                        "'.addslashes(trim($row['value'])).'"
                                    )';
                $attrResult = @$this -> db -> query($attrQuery);
                if(!$attrResult)
                {
                    $err .= '<p class="box error">Could not add attribute for<br>
                            Attribute ID: '.$row['attribute_id'].'<br>
                            Client ID: '.$client_id.'<br>
                            Attribute Value: '.trim($row['value']).'</p>';
                }
            }
        }
    }
    return $err;
}
?>

Here's what I've tried:

                $attrQuery = "INSERT INTO client_attribute_value (attribute_id, client_id, value) VALUES ";

                $vls = array();
                foreach($rows as $row) {
                    $myattribute_id = $row['attribute_id'];
                    $myclient_id = $row[$client_id];
                    $myvalue = addslashes(trim($row['value']));
                    $vls[] = " ( '$myattribute_id ', '$myclient_id ', '$myvalue ')";


                  $attrQuery .= implode(', ', $vls);

                  $attrResult = @$this -> db -> query($attrQuery);

Client Table sample:

client_attribute_value Table sample:

Answer 1

Answering my own Question, hope this helps someone else in the future.

In the model .php file of the codeigniter/framework add this:

$this->db->trans_start();

   MY CODE

$this->db->trans_complete();

Problem Solved. :) It speed up my INSERTS records to database approx. 15K records in 30secs.

Answer 2

My guess is that you are making multiple connections with the database, try to open one and preserve it's resource opened for all changes, then you close it only at the end of the process.