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

  • 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!

    Friday, April 6, 2018 6:19 PM

Answers

  • 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.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 15, 2018 11:53 AM

All replies

  • 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.

    Saturday, April 7, 2018 12:23 AM
  • User718515125 posted

    id in both tables are Primary and cannot have duplicates.

    Saturday, April 7, 2018 5:32 PM
  • User269602965 posted

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

    select id=null

    Saturday, April 7, 2018 11:48 PM
  • 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.

    Sunday, April 8, 2018 12:23 AM
  • 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.

    Monday, April 9, 2018 7:07 PM
  • 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.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 15, 2018 11:53 AM