Last post May 15, 2018 11:53 AM by thephillium
Apr 06, 2018 06:19 PM|thephillium|LINK
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>";
echo "Error: " . $sql . "<br>" . $conn->error;
Apr 07, 2018 12:23 AM|Lannie|LINK
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.
Apr 07, 2018 05:32 PM|thephillium|LINK
id in both tables are Primary and cannot have duplicates.
Apr 07, 2018 11:48 PM|Lannie|LINK
If id is primary key in the target table then why are your inserting this :
Apr 08, 2018 12:23 AM|thephillium|LINK
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.
Apr 09, 2018 07:07 PM|Lannie|LINK
In the tables did you use the auto increment command on CREATE table
CREATE TABLE X
ID int NOT
May 15, 2018 11:53 AM|thephillium|LINK