Checking for duplicates

[tessthepup]

Please will someone help me with the following code.

The code copies data from one table to another on a button click. When first clicked the script selects and inserts the data ok but I need to check for duplicates and report how many duplicates there are.

I am struugling with identifying the duplicates and reporting back.

<?php
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

//Connect to MySQL using PDO.
$pdo = new PDO("mysql:host=localhost;dbname=tbl_temp_products", "root", "", $options);

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

//The name of the new table.
$categories = 'categories';

//Import the data from the old table into the new table.
$select = $pdo->prepare('SELECT categories_id, parent_id FROM tbl_temp_categories WHERE categories_id = ?');
$select->execute([$categories]);
if ($select->rowCount() > 0) {
    echo 'Duplicates found';
} else {
$pdo->query("INSERT $categories SELECT categories_id, parent_id FROM tbl_temp_categories");
}
}else{
?>

<form method ="post">
 <button class="btn btn-primary" type="submit">Update Categories</button>
</form>

<?php } ?>

Thank you for any help you can offer me

[benanamen]

Why are you creating duplicate data?

[tessthepup]

I import data from an xml feed into a temporary table so I can work on it and then I need to update the live tables with the data.

[ErnieAlex]

Well, you should check for duplicates when you update the live tables and that way, you would not have duplicates in your live data. But, here is a tutorial on how to check for duplicates using queries. It explains how to check for one field, two fields and many fields depending on your database layout. Simple to understand. Hope this helps… Tutorial - find duplicate values

[phdr]

The OP has a thread from a year ago - Xml to mysql import script where someone asked what the OP wanted to do for a duplicate and listed methods for the different possibilities -

Xml to mysql import script

What action do you want to perform if an item number already exists? Do you want to report the duplicate item numbers or a count of the duplicates? Do you want to UPDATE the rest of the values instead of inserting a new row? Do you want to silently ignore the duplicate? I’m betting you don’t want to halt execution on the first duplicate detected, which is what the current code would do.

For each of these cases, the item column should be defined as a unique index and you should just attempt to insert the data (no SELECT query is needed at all.) To report the duplicate item numbers or get a count of the duplicates, the error handling for the insert query would test for a duplicate index error number, then either save the item number (in an array) or increment a counter, then continue to process the rest of the rows. To UPDATE the rest of the values, you would use an INSERT … ON DUPLICATE KEY UPDATE … query. To silently ignore the duplicate, you would add the IGNORE keyword to the insert query.

For the first two cases, if there is a duplicate index error, it means there was an existing row, if there was no error a new row was inserted. You can count or add items to a list as needed.

For the INSERT … ON DUPLICATE KEY UPDATE … method, in order to count or make a list of existing values, you would initially get the highest auto-increment id value. You would then get the last_insert_id() value from the query. If the last_insert_id() value is greater than the initial highest auto-increment id value, a new row was inserted, otherwise an existing row was updated.

For the IGNORE method, you would detect if a row was affected by the insert query. If yes, a new row was inserted, otherwise there was an existing row and nothing occurred when the query was executed.