prob with updating the table

[puvi]

hii ppl..

i have a edit form, which is populated from the database, things work fine till here. now i want to update the table with editted details. if i echo the details from $_POST variables it prints fine. but if i update the table it doesnt..

this form is generated dynamically, hence there are multiple rows to be edited. when update is clicked all the rows are updated with the details of one particular row and not with their respective details.

pls have a look at the code..n save my day...

foreach($_POST['quantity'] as $temp)//this data comes from text field
{
	$quantity[]=$temp;
}

foreach($_POST['rate'] as $temp)//this data comes from text field
{
	$rate[]=$temp;
}
foreach($_POST['total'] as $temp)//this data comes from text field
{
	$total[]=$temp;
}

foreach($_POST['component'] as $item)//this data comes from dropdown
{
	$sqlI="select id from component_item where name='".$item."'";
	$resI=mysql_query($sqlI);
	$rowI=mysql_fetch_array($resI);
	$itemId[]=$rowI['id'];
}
/*for($i=0;$i<count($quantity);$i++)//lets say $quantity=3 i.e there are 3 rows of input field populated from db, it prints fine here
{
	echo $quantity[$i]."<br>";
	echo $rate[$i]."<br>";
	echo $total[$i]."<br>";
	echo $itemId[$i]."<br>";

}*/

for($i=0;$i<count($quantity);$i++)
{	
				
	$query="update purchase_order_detail set component_item_id='$itemId[$i]', quantity='$quantity[$i]', rate='$rate[$i]', total='$total[$i]' where purchase_order_id='$id'";
	$result=mysql_query($query) or die(mysql_error());

}

please let me know if u find something wrong in my code..cos i get no error for the query..
but all 3 rows are updated with data of one particular row as if, $i is not incrementing..

thanks ppl..

[puvi]

also, apologies ppl, cos there are threads relating to this issue, but they dont answer my issue. in one of the thread i found this linkhttp://www.phpeasystep.com/mysql/10.html. in the code, in that link, the author has not used $_POST at all, he has directly updated into the database, does this code work?? infact i tried it, but it dint work for me. so is this(omiting $_POST) a mistake or did it not work for me??

this doubt may sound silly to experts..but im[ cant say im new to php :D, cos its been quite some times(few months) im using php] not an expert in php..

hope someone will clear my doubt...thanks once again..

[paulrajj]

Hi,
Where is that $id comes from in update statement ? just put print_r($quantity) after the first for loop to check how many values available in $quantity array..
You are only doing the $quantity array for updating the records, how about if it contains empty records and if other $_POST fields contains the values ?

[puvi]

hey paulrajj, $id is somewhere in the begining of the code(i have not posted the entire code, i posted only the part which i thot was source of issue..)its the id of the user who is editing the form. i ll check if that is coming fine..

n all the values in the quantity, rate, total, itemId gets printed properly..

thanks..

[paulrajj]

This is because you are updating the array values to same record (I assume $id is the single value).
So Whenever you are updating the records through the loop to the same record ($id) it always gets overwritten.
Only the last index values of array($quantity) will be updated to the $id record.

[puvi]

yes, seems like u said.

now i am getting a error, ie,
Cannot add or update a child row: a foreign key constraint fails (`****`.`purchase_order_detail`, CONSTRAINT `purchase_order_detail_component_item_id_component_item_id` FOREIGN KEY (`component_item_id`) REFERENCES `component_item` (`id`))

in one of the threads i came across similar issue, where a member said, "While specifying the foreign key constraint you have specified "ON UPDATE NO ACTION" while this seems to mean that no action would be taken on update, it specifically means that you will not be allowed to update too, either remove the clause or if it is binding for you to have it, modify it suitably."

can anyone pls help me modify this?? i need to change the specification so that the table is updated.

[paulrajj]

Alter the table by dropping the foreign key and updaing the foreignkey by adding "ON UPDATE NO ACTION".

ALTER TABLE `purchase_order_detail` DROP FOREIGN KEY `(the_foreign_key_name)`

ALTER TABLE `purchase_order_detail` ADD FOREIGN KEY ( `component_item_id` ) REFERENCES `component_item` (
`id` 
) ON UPDATE NO ACTION ;

[puvi]

hey paulrajj, thanks for the reply.

could you please explain, what would be the difference with this "ON UPDATE NO ACTION"??

i am taking it as, if i include this statement and execute, i wont be able to update the rows in table cos it says "on update NO ACTION". im really sorry if i sound silly n please excuse my lack of knowledge on this..

[paulrajj]

Just remove "ON UPDATE NO ACTION" from my previous post query to update the record.
It means no action will be taken to the foreign constraint table when you update the primary constraint table. so the final alter query would be,

ALTER TABLE `purchase_order_detail` ADD FOREIGN KEY ( `component_item_id` ) REFERENCES `component_item` (
`id` 
)

[puvi]

thanks, will try n keep u posted.