PHP PDO MySQL Duplicate Key Update Multiple Rows and Values

Question

I have the following PDO Query, but somehow can't get the ON DUPLICATE part to work.

Slug is the Unique Key.

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$statement = $db->prepare("INSERT INTO plugins (name, slug, version, author, author_profile, rating, num_ratings, active_installs, downloaded, last_updated, added, download_link, banners_high, banners_low, icons_default, icons_2x, icons_1x)
                             VALUES (:name, :slug, :version, :author, :author_profile, :rating, :num_ratings, :active_installs, :downloaded, :last_updated, :added, :download_link, :banners_high, :banners_low, :icons_default, :icons_2x, :icons_1x)
                             ON DUPLICATE KEY UPDATE version = :version, rating = :rating, num_ratings = :num_ratings, active_installs = :active_installs, downloaded = :downloaded, last_updated = :last_updated, download_link = :download_link");

                        foreach ($call_api->plugins as $keys) {
                                $statement->execute([
                                        'name' => $keys->name,
                                        'slug' => $keys->slug,
                                        'version' => $keys->version,
                                        'author' => sanitize_text_field($keys->author),
                                        'author_profile' => $keys->author_profile,
                                        'rating' => $keys->rating,
                                        'num_ratings' => $keys->num_ratings,
                                        'active_installs' => $keys->active_installs,
                                        'downloaded' => $keys->downloaded,
                                        'last_updated' => $keys->last_updated,
                                        'added' => $keys->added,
                                        'download_link' => $keys->download_link,
                                        'banners_high' => $keys->banners['high'],
                                        'banners_low' => $keys->banners['low'],
                                        'icons_default' => $keys->icons['default'],
                                        'icons_2x' => $keys->icons['2x'],
                                        'icons_1x' => $keys->icons['1x']
                                ]);
                        }

I'm receiving the error:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

Answer 1

try this

$statement = $db->prepare("INSERT INTO plugins (name, slug, version, author, author_profile, rating, num_ratings, active_installs, downloaded, last_updated, added, download_link, banners_high, banners_low, icons_default, icons_2x, icons_1x)
                         VALUES (:name, :slug, :version, :author, :author_profile, :rating, :num_ratings, :active_installs, :downloaded, :last_updated, :added, :download_link, :banners_high, :banners_low, :icons_default, :icons_2x, :icons_1x)
                         ON DUPLICATE KEY UPDATE version = :versionOne, rating = :ratingOne, num_ratings = :num_ratingsOne, active_installs = :active_installsOne, downloaded = :downloadedOne, last_updated = :last_updatedOne, download_link = :download_linkOne");

                    foreach ($call_api->plugins as $keys) {
                            $statement->execute([
                                    'name' => $keys->name,
                                    'slug' => $keys->slug,
                                    'version' => $keys->version,
                                    'author' => sanitize_text_field($keys->author),
                                    'author_profile' => $keys->author_profile,
                                    'rating' => $keys->rating,
                                    'num_ratings' => $keys->num_ratings,
                                    'active_installs' => $keys->active_installs,
                                    'downloaded' => $keys->downloaded,
                                    'last_updated' => $keys->last_updated,
                                    'added' => $keys->added,
                                    'download_link' => $keys->download_link,
                                    'banners_high' => $keys->banners['high'],
                                    'banners_low' => $keys->banners['low'],
                                    'icons_default' => $keys->icons['default'],
                                    'icons_2x' => $keys->icons['2x'],
                                    'icons_1x' => $keys->icons['1x'],

                                    'versionOne' => $keys->version,
                                    'ratingOne' => $keys->rating,
                                    'num_ratingsOne' => $keys->num_ratings,
                                    'active_installsOne' => $keys->active_installs,
                                    'downloadedOne' => $keys->downloaded,
                                    'last_updatedOne' => $keys->last_updated,
                                    'download_linkOne' => $keys->download_link

                            ]);
                    }