Insert into .. select from .. using zend db

Question

I have the following raw query which moves items from a shopping cart to an order table:

insert into webshop_order_item (
    order_id,
    product_id,
    count
)
select
    1,
    product_id,
    count
from 
    webshop_cart

I am using the Zend DB for all my modeling. I was wondering if there is a way of achieving the goal of the above query without having to use a raw query?

altern · Accepted Answer · 2009-11-13 14:12:38Z

6

There is no way of inserting from select in zend db yet. However, if you need this feature only for one adapter, then you could use approach similar to given below:

public function insertSelect($tableName, Zend_Db_Select $select, array
$fields = array()) {
    $fieldString = '';
    if (count($fields))
    {
        foreach($fields as $fieldKey => $field)
        {
            $fields[$fieldKey] =  $this->quoteIdentifier($field);
        }

        $fieldString = ' (' . implode(',', $fields) . ')';
    }

    $query  = "INSERT INTO ".$this->quoteIdentifier($tableName) .
$fieldString . " ";
    $query .= $select;

    $this->_db->query($query);
}

answered Nov 13, 2009 at 14:12

altern

5,9895 gold badges47 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rodrigo-silveira · Accepted Answer · 2013-03-07 19:59:17Z

2

This works fine for me: I use a Zend_Db_Expr. Sanitize as needed.

$this->db->insert("sa_article_attributes", array(
    "article_id" => $sid,
    "article_attribute_id" => new Zend_Db_Expr("(
        SELECT Attribute.id FROM sa_attributes Attribute
        WHERE Attribute.title = '{$category}'
        LIMIT 1
        )")
    )
);

answered Mar 7, 2013 at 19:59

rodrigo-silveira

13.1k16 gold badges81 silver badges138 bronze badges

1 Comment

sroes Over a year ago

This is not the same. The query from my question will insert multiple rows. Yours will only insert one.

Collectives™ on Stack Overflow

Insert into .. select from .. using zend db

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related