如何使用php中具有一对二关系的sql语句编辑两个表

Question

I have two tables having one to many relationship.

data is being properly entered in both tables at one time, but now I want to edit both tables at one time and I m confused that how to update this.

I'm using parent table primary key to edit both tables.

When I update one record, the others records on same primary key also updated in child table.

when I update by using child table primary key to update tables then I have to update related fields also.

I do not know what to do, please help me.

These are my two tables and the query is :

mysqli_query($conn, "update new_invoice inner join new_item ON new_invoice.invoice_id = new_item.invoice_id_fk
  SET 
  new_invoice.c_name='$c_name',
  new_invoice.c_address='$c_address',
  new_invoice.invoice_date = '$invoice_date',
  new_invoice.amount = '$amount',
  new_item.item_name  = '$item_name',
  new_item.detail = '$detail',
  new_item.quantity = '$quantity',
  new_item.unit_cost = '$unit_cost',
  new_item.discount = '$discount',
  new_item.price = '$price',
  new_invoice.confirmation = '$confirmation'

where new_invoice.invoice_id='$invoice_id'   

");

I'm working on invoice system.

I want to update data in both tables at one time but how to do this.

Actually I'm new to stack overflow so please tell me how to answer your question.

can I answer in comment ?

I do not know the answer of my question.

My mean is this: How can I answer you if you ask me something about my question?

Answer 1

You will need to identify the row in the new_item table that needs to be updated, at the moment it's updating all rows for this invoice...

mysqli_query($conn, "update new_invoice inner join new_item ON new_invoice.invoice_id = new_item.invoice_id_fk
  SET 
  new_invoice.c_name='$c_name',
  new_invoice.c_address='$c_address',
  new_invoice.invoice_date = '$invoice_date',
new_invoice.amount = '$amount',
new_item.item_name  = '$item_name',
new_item.detail = '$detail',
new_item.quantity = '$quantity',
new_item.unit_cost = '$unit_cost',
new_item.discount = '$discount',
new_item.price = '$price',
new_invoice.confirmation = '$confirmation'

where new_invoice.invoice_id='$invoice_id'
    and new_item.line_no = $ine_no

");

I've used new_item.line_no - but you need to put the correct column names and values in there.