How do I add more than one row with Zend_Db?

问题:

I have an array with information which looks more or less like this:

$data[] = array('content'=>'asd');
$data[] = array('content'=>'asdf');

And I want to add both entries into the Database.

$db->insert('table', $data);

does not add both entries. What am I doing wrong? Do I have to use Zend_ Db_Table?

$data = array('content'=>'asdf');
$db->insert('table', $data);

works of course

回答1:

I don't think Zend_Db supports insertion of multiple rows.

But if you just have two rows or a little more you can just use a loop.

foreach ($data as $row)
{
    $db->insert('table', $row)
}

---

Bill Karwin, a former Zend Framework developer, wrote this on Nabble some time ago:

Rowsets are basically a collection object, so I would add methods to that class to allow rows to be added to the set. So you should be able to do this:

// creates a rowset collection with zero rows
$rowset = $table->createRowset();

// creates one row with unset values 
$row = $table->createRow();

// adds one row to the rowset 
$rowset->addRow($row); 

// iterates over the set of rows, calling save() on each row
$rowset->save(); 

It makes no sense to pass an integer to createRowset() to create N empty rows. You would just have to iterate through them to populate them with values anyway. So you might as well write a loop to create and populate individual rows with application data, and then add them to the collection.

$rowset = $table->createRowset();
foreach ($appData as $tuple) 
{
    $row = $table->createRow($tuple);
    $rowset->addRow($row);
}
$rowset->save();

It does make sense to allow an array of arrays to be passed to createRowset(), since this would be consistent with the usage of passing a tuple to createRow().

$rowset = $table->createRowset($appData); // pass array of tuples

This would perform the same loop as the previous example above (except for the save() at the end), creating a new rowset of new rows, ready to be save()d.

There are two ways in SQL to improve the efficiency of inserting data:

1. Use a single INSERT statement with multiple rows:

   INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);

2. Prepare an INSERT statement and execute it multiple times:

   PREPARE INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?); EXECUTE 1, 2, 3 EXECUTE 4, 5, 6 EXECUTE 7, 8, 9

However, supporting either of these improvements would add complexity to the Row and Rowset classes. This is due to the internal way the current Zend_Db_Table_Row class differentiates between a row that needs to be INSERTed or UPDATEd when you call save(). This distinction is encapsulated by the Row object, so the Rowset doesn't know if the individual rows are new rows or modified copies of existing rows. Therefore for the Rowset class to offer a multi-row save() method that uses more efficient SQL, the management of dirty data would have to be totally refactored. The easier solution is for the Rowset to iterate over its rows, calling save() on each one. This is better for OO encapsulation, though it doesn't help optimize SQL for inserting a rowset.

In any case, it's really rare to bulk-load many rows of data in a typical web request, when there's the greatest need for efficient SQL. The difference in efficiency for a small number of rows is small, so it would be a noticeable improvement only if you're bulk-loading a huge number of rows. If that's the case, you shouldn't be using INSERT anyway, you should be using MySQL's LOAD DATA statement, or equivalent feature if you use another RDBMS brand. INSERT is not usually the most efficient choice for loading lots of data.

Regarding returning auto-generated keys, I wouldn't bother. Notice that if you use plain SQL (in the mysql CLI for example), and you insert multiple rows in a single INSERT statement, you can only get the last generated id value, not the id values for all rows inserted. This is SQL behavior; it's true for any language or any framework.

INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
SELECT LAST_INSERT_ID(); -- returns only the id for the third tuple

If you do need the id for each row, you should write a loop and insert the rows one at a time, retrieving the generated id after each row inserted.

回答2:

You can execute any SQL syntax you want -- including multi-row INSERT statements -- via the Zend_Db_Adapter_Abstract::query() method.

But methods of the Zend_Db_Table and Zend_Db_Table_Rowset classes don't have any support for inserting multiple rows in one go.

回答3:

to insert multiple rows you can use Zend_Db

$stmt = $table->getAdapter()->prepare('INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?)');

$stmt->execute( array($value1, $value2, $value3, $value4, $value5, $value6) );

(from Bill Karwin)

in your case we can change that to this code:

$data[] = array('content'=>'asd');
$data[] = array('content'=>'asdf');

$stmt = $table->getAdapter()->prepare('INSERT INTO table (col1) VALUES (?), (?)');
$stmt->execute( $data );

to generate these '(?), (?)' dynamically, incase the data is dynamic, you can try using this snippet:

$values = implode(',',array_fill(0,count($data),'(?)'));

hope this helps

regards, Riki Risnandar

回答4:

here is my solution:

public function saveRows($array) {
        $vAmount    = count($array);
        $values     = array();
        $columns    = array();

        foreach ($array as $colval) {
            foreach ($colval as $column=>$value) {
                array_push($values,$value);
                !in_array($column,$columns) ? array_push($columns,$column) : null;
            }
        }

        $cAmount    = count($columns);
        $values     = array_chunk($values, $cAmount);
        $iValues    = '';
        $iColumns   = implode("`, `", $columns);

        for($i=0; $i<$vAmount;$i++)
            $iValues.="('".implode("', '", $values[$i])."')".(($i+1)!=$vAmount ? ',' : null);

        $data="INSERT INTO `".$this->_name."` (`".$iColumns."`) VALUES ".$iValues;
        die($data);
        $this->query($data);
    }

回答5:

If you do use ZF2 then solution might be like this:

$insert = $this->getSql()->insert();
foreach ($values as $value) {
    $relation = array(
        'column_one' => $value,
        'column_two' => $value
    );
    $insert->values($relation, Insert::VALUES_MERGE);
}

$insertRes = $this->executeInsert($insert);

回答6:

It's work.

 $query = 'INSERT INTO ' . $db->quoteIdentifier('table') . ' (`col1`, `col2`) VALUES ';
    $queryVals = array();
    foreach ($data as $row) {
        foreach($row as &$col) {
            $col = $db->quote($col);
        }
        $queryVals[] = '(' . implode(',', $row) . ')';
    }
    $stmt = $db->query($query . implode(',', $queryVals));

how to build multiple insert query in zend framework