Множественная вставка с помощью pdo
Подскажите, как сделать множественный insert с помощью pdo?
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$rows = [];
$sql = "INSERT INTO `test` (type,sku,name,published,description) VALUES (?,?,?,?,?),(?,?,?,?,?)";
$stmt = $pdo->prepare($sql);
$rows = [
['type' => 'fas', 'sku'=>'afds','name'=>'afsd','published'=>1,'description'=>'adsfasf'],
['type' => 'fas', 'sku'=>'afds','name'=>'afsd','published'=>1,'description'=>'adsfasf'],
];
$stmt->execute($rows);
Выдаёт ошибку:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
EDITED
sku,store_view_code,attribute_set_code,product_type,categories,product_websites,name,description,short_description,weight,product_online,tax_class_name,visibility,price,special_price,special_price_from_date,special_price_to_date,url_key,meta_title,meta_keywords,meta_description,created_at,updated_at,new_from_date,new_to_date,display_product_options_in,map_price,msrp_price,map_enabled,gift_message_available,custom_design,custom_design_from,custom_design_to,custom_layout_update,page_layout,product_options_container,msrp_display_actual_price_type,country_of_manufacture,additional_attributes,qty,out_of_stock_qty,use_config_min_qty,is_qty_decimal,allow_backorders,use_config_backorders,min_cart_qty,use_config_min_sale_qty,max_cart_qty,use_config_max_sale_qty,is_in_stock,notify_on_stock_below,use_config_notify_stock_qty,manage_stock,use_config_manage_stock,use_config_qty_increments,qty_increments,use_config_enable_qty_inc,enable_qty_increments,is_decimal_divided,website_id,deferred_stock_update,use_config_deferred_stock_update,related_skus,crosssell_skus,upsell_skus,hide_from_product_page,custom_options,bundle_price_type,bundle_sku_type,bundle_price_view,bundle_weight_type,bundle_values,associated_skus
24-WG085,,Default,simple,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Sprite Yoga Strap 6 foot,"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and urable under strain.</p><ul><li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>",,1,1,Taxable Goods,"Catalog, Search",14,,,,sprite-yoga-strap-6-foot,Meta Title,"meta1, meta2, meta3",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,Use config,,"has_options=1,quantity_and_stock_status=In Stock,required_options=0",100,0,1,0,0,1,1,0,0,1,1,,1,0,1,1,0,1,0,0,1,0,1,"24-WG087,24-WG086","24-WG087,24-WG086","24-WG087,24-WG086",,"name=Custom Yoga Option,type=drop_down,required=0,price=10.0000,price_type=fixed,sku=,option_title=Gold|name=Custom Yoga Option,type=drop_down,required=0,price=10.0000,price_type=fixed,sku=,option_title=Silver|name=Custom Yoga Option,type=drop_down,required=0,price=10.0000,price_type=fixed,sku=yoga3sku,option_title=Platinum",,,,,,
24-WG086,,Default,simple,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Sprite Yoga Strap 8 foot,"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and durable under strain.</p><ul><li>8' long x 1.0"" wide.<li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>",,1,1,Taxable Goods,"Catalog, Search",17,,,,sprite-yoga-strap-8-foot,Meta Title,"meta1, meta2, meta4",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,Use config,,"has_options=0,quantity_and_stock_status=In Stock,required_options=0",100,0,1,0,0,1,1,0,0,1,1,,1,0,1,1,0,1,0,0,1,0,1,24-WG087,24-WG087,24-WG087,,,,,,,,
24-WG087,,Default,simple,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Sprite Yoga Strap 10 foot,"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and durable under strain.</p><ul><li>10' long x 1.0"" wide.<li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>",,1,1,Taxable Goods,"Catalog, Search",21,,,,sprite-yoga-strap-10-foot,Meta Title,"meta1, meta2, meta5",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,Use config,,"has_options=0,quantity_and_stock_status=In Stock,required_options=0",100,0,1,0,0,1,1,0,0,1,1,,1,0,1,1,0,1,0,0,1,0,1,24-WG086,24-WG086,24-WG086,,,,,,,,
24-WG085_Group,,Default,grouped,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Set of Sprite Yoga Straps,"<p>Great set of Sprite Yoga Straps for every stretch and hold you need. There are three straps in this set: 6', 8' and 10'.</p><ul><li>100% soft and durable cotton.</li><li>Plastic cinch buckle is easy to use.</li><li>Choice of three natural colors made from phthalate and heavy metal free dyes.</li></ul>",,,1,,"Catalog, Search",,,,,set-of-sprite-yoga-straps,Meta Title,"meta1, meta2, meta6",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,,,"has_options=0,quantity_and_stock_status=In Stock,required_options=0",0,0,1,0,0,1,1,0,0,1,1,,1,1,1,1,0,1,0,0,1,0,1,"24-WG087,24-WG086","24-WG087,24-WG086","24-WG087,24-WG086",,,,,,,,"24-WG085=5.0000,24-WG086=5.0000"
24-WG085-bundle-dynamic,,Default,bundle,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Sprite Yoga Strap Dynamic Bundle,"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and durable under strain.</p><ul><li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>",,1,1,Taxable Goods,"Catalog, Search",14,,,,sprite-yoga-strap2,Meta Title,"meta1, meta2, meta8",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,Use config,,"has_options=1,shipment_type=together,quantity_and_stock_status=In Stock,required_options=0",0,0,1,0,0,1,1,0,0,1,1,,1,0,1,1,0,1,0,0,1,0,1,"24-WG087,24-WG086","24-WG087,24-WG086","24-WG087,24-WG086",,,dynamic,dynamic,Price range,fixed,"name=Bundle Option One1,type=select,required=1,sku=24-WG085,price=15.0000,default=0,default_qty=1.0000,price_type=fixed|name=Bundle Option One1,type=select,required=1,sku=24-WG086,price=10.0000,default=1,default_qty=1.0000,price_type=fixed",
24-WG085-bundle-fixed,,Default,bundle,"Default Category/Gear,Default Category/Gear/Fitness Equipment",base,Sprite Yoga Strap Fixed Bundle,"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and durable under strain.</p><ul><li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>",,1,1,Taxable Goods,"Catalog, Search",14,,,,sprite-yoga-strap3,Meta Title,"meta1, meta2, meta9",meta description,"2015-10-25 03:34:20","2015-10-25 03:34:20",,,Block after Info Column,,,,,,,,,,,Use config,,"has_options=1,shipment_type=together,quantity_and_stock_status=In Stock,required_options=0",0,0,1,0,0,1,1,0,0,1,1,,1,0,1,1,0,1,0,0,1,0,1,"24-WG087,24-WG086","24-WG087,24-WG086","24-WG087,24-WG086",,,fixed,fixed,Price range,fixed,"name=Yoga Strap,type=radio,required=1,sku=24-WG086,price=0.0000,default=1,default_qty=3.0000,price_type=percent|name=Yoga Strap,type=radio,required=1,sku=24-WG085,price=0.0000,default=0,default_qty=3.0000,price_type=percent",
вот пример строки из-за которой не работает insert:
"<p>The Sprite Yoga Strap is your untiring partner in demanding stretches, holds and alignment routines. The strap's 100% organic cotton fabric is woven tightly to form a soft, textured yet non-slip surface. The plastic clasp buckle is easily adjustable, lightweight and urable under strain.</p><ul><li>100% soft and durable cotton.<li>Plastic cinch buckle is easy to use.<li>Three natural colors made from phthalate and heavy metal free dyes.</ul>"
Ответы (2 шт):
Автор решения: Sergei Kirjanov
→ Ссылка
Можно использовать обертку:
function multi_insert($dbh, $table, $fields, $data){
$i = 0;
foreach($data as $d){
$keys = [];
foreach($fields as $fn){
$key = ':i'.$i.$fn;
$keys[] = $key;
$param[$key] = $d[$fn];
}
$stm_texts[] = '('.implode(',',$keys).')';
$i++;
}
$stm_text = 'insert into '.$table.' ('.implode(',',$fields).') values '.implode(',',$stm_texts);
$stmt = $dbh->prepare($stm_text);
$stmt->execute($param);
}
Пример:
multi_insert($dbh, "messages", ["id","title","message"], [
["id"=>1,"title"=>"a","message"=>"am"],
["id"=>2,"title"=>"b","message"=>"bm"],
["id"=>3,"title"=>"c","message"=>"cm"],
]);
Есть также вариант использовать транзакции, т. к. объем одного запроса может быть ограничен. Транзакции обеспечивают атомарность и дают большую скорость.
$dbh->beginTransaction();
...
$dbh->commit();
В любом случае: измеряйте->оптимизируйте->измеряйте.
В аргументы $table и $fields передаются константы. Из них будет склеен результирующий запрос. Передача в них произвольных данных может привести к взлому системы.
Автор решения: Ипатьев
→ Ссылка
Нужно всего лишь решить для себя, какие плейсхолдеры используются в запросе - именованные или позиционные.
В запросе написаны позиционные, а в execute() передаётся ассоциативный массив как для именованных.
Как только эта сложнейшая задача будет решена, остальное будет уже делом техники. Пример можно взять отсюда
$rows = [
['type' => 'fas', 'sku'=>'afds','name'=>'afsd','published'=>1,'description'=>'adsfasf'],
['type' => 'fas', 'sku'=>'afds','name'=>'afsd','published'=>1,'description'=>'adsfasf'],
];
$sql = "INSERT INTO `test` (type,sku,name,published,description) VALUES (?,?,?,?,?)";
$stmt = $pdo->prepare($sql);
$pdo->beginTransaction();
foreach ($rows as $data)
{
$stmt->execute(array_values($data));
}
$pdo->commit();
стильно, модно, молодёжно
(и никакого камалания с бубном, $pdo->quote и htmlspecialchars)