Mysql copy a row from one table to another but it duplicates

Question

User718515125 posted

I am trying to copy a row from one table, using variable $year, into an identical table called $record_year.  The code I am using copies it over no problem, but it does it twice...and I only want one record.

Once a radio has been declared as "returned" I want the record updated (works), the record copied to the "record" table (half works), and then delete the original row (works).

See code for the copy: (please excuse the number of variable)

//copy completed record to `year`_record
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "INSERT INTO `$record_year` select id=null,ON_num,BMR_num,Current_Status,Received_on,Received_by,Received_at,Recycled_on,Recycled_by,Recycled_at,Boxed_on,Boxed_by,Boxed_at,Boxed_num,Returned_on,Returned_by,Returned_to,Return_staff,Repair_needed_on,Repair_needed_by,Repair_needed_from,Repair_sent_on,Repair_sent_by,Repair_received_on,Repair_received_by,Notes,last_updated_by,last_updated_date from `$year` where id = '$id'";
$result = $conn->query($sql);
if ($conn->query($sql) === TRUE) 
{
	echo "Record moved to records successfully<br>";
} 
else 
{
	echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();

Any ideas?

Thanks!

Answer 1

User269602965 posted

check the id in source table year for uniqueness

select id, count(*) from year group by id having count(*) > 1;

Any with count of 2 or more will insert two or more records during your insert action.

Answer 2

User718515125 posted

id in both tables are Primary and cannot have duplicates.

Answer 3

User269602965 posted

If id is primary key in the target table then why are your inserting this :

select id=null

Answer 4

User718515125 posted

I want both tables, $year and $year_record, to auto increment.  

When I did not include the id=NULL, it would copy the value of id from $year into the new table (ie if the id from $year was 374, then the entry into $year_record would also be 374). When I put the id=null, then it would auto increment.

Answer 5

User269602965 posted

In the tables did you use the auto increment command on CREATE table

CREATE TABLE X

ID int NOT NULL AUTO_INCREMENT,

other columns.

Answer 6

User718515125 posted
I figured it out.

$result = $conn->query($sql);
if ($conn->query($sql) === TRUE)

These both do the same thing. I commented out $result and it stopped duplicating.