Daniel Klose Daniel Klose - 1 month ago 10
MySQL Question

PHP PDO MySQL Duplicate Key Update Multiple Rows and Values

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

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

                            ]);
                    }