Re: [fw-db] insert method refactoring

Yes it is, and there was a chat regarding this, but not all DBMS-es support bulk insert, so idea for Zend_Db is to implement abstract db and have all adapters...etc

Regards,
Saša Stamenković

2009/7/27 Piotr Kabaciński <piotr@kabot.net>

On 07/27/2009 05:53 AM, R! wrote:

Hi,
I have small idea for insert method refactoring. I have script witch insert
nearly 50 000 rows to DB. So now I do something like this:

// taken from insert method
$cols = array();
$vals = array();
foreach ($bind as $col => $val) {
    $cols[] = $Db->quoteIdentifier($col, true);
    if ($val instanceof Zend_Db_Expr) {
        $vals[] = $val->__toString();
        unset($bind[$col]);
    } else {
        $vals[] = '?';
    }
}

// build the statement
$sql = "INSERT INTO "
   . $Db->quoteIdentifier($table, true)
   . ' (' . implode(', ', $cols) . ') '
   . 'VALUES (' . implode(', ', $vals) . ')';


$stmt = $Db->prepare($sql);

foreach($data as $value ){
           $stmt->execute($value);
}

I think that that you are familiar with first part as I actualy take it from
insert method. As it's  ugly to have same code on two places my idea is
write public method
prepareInsert($table,$columns) which will encapsulate it. This method can by
quite handy in some situation and I think that is quite easy to implement it
without breaking BC.

Maybe it's not the topic of this mail but if you really need to insert
50k rows to db try to insert as many rows as possible with one insert.
Something like:
INSERT INTO ... (...)  VALUES (), (), ()

It's much faster than many single executes.

greetings
--
Piotr Kabacinski
a2Fib3Q