[SOLVED] MySQL Update problem ( checkbox values )

[plodos]

reviewer_research_field table

user 	id
22 	       2
22 	       4
22 	       6

 

when I try to update data which is coming from form with checkbox values

 

<?php foreach ($_POST['state'] as $newstate)
{
	$write = "update reviewer_research_field as r 
	          set r.id='{$newstate}'
	          where r.user='{$_SESSION['id']}'
	          ";
	mysql_query($write) or die(mysql_error());
} ?>

 

im selecting 3 different checkbox 5-8-9

but this query is only adding one number like

 

user id

22       9

22       9

22       9

 

but it must be

 

user id

22       5

22       8

22       9

 

What is the problem? How can I solve :s

Thanks in advance

[MadTechie]

Well the problem is simple..

lets review whats happening

 

$write = "update reviewer_research_field as r set r.id='{$newstate}' where r.user='{$_SESSION['id']}' ";

the line above repeats 3 times as follows

update reviewer_research_field as r set r.id='5' where r.user='22' 

update reviewer_research_field as r set r.id='8' where r.user='22' 

update reviewer_research_field as r set r.id='9' where r.user='22' 

Each affect 3 records, thus the last one updates all 3 to the last value (9)

 

How so solve this, well the easy way is to do this

<?php
foreach($_POST['state'] as $newstate)
{
$delete = "DELETE FROM reviewer_research_field WHERE user='{$_SESSION['id']}' ";
mysql_query($delete) or die(mysql_error());
#$write = "update reviewer_research_field as r set r.id='{$newstate}' where r.user='{$_SESSION['id']}' ";
#mysql_query($write) or die(mysql_error());
$insert = "INSERT INTO reviewer_research_field (`id` ,`user`) VALUES ('{$newstate}' , '{$_SESSION['id']}');";
mysql_query($insert) or die(mysql_error());
}
?>

 

[plodos]

yessss...thats the another solution method which I didnt think before

 

thanks for everything

[plodos]

DELETE is repeating inside of the foreach

 

DELETE FROM reviewer_research_field WHERE user='22'

 

insert into reviewer_research_field set user='22', id='1'

 

DELETE FROM reviewer_research_field WHERE user='22'

 

insert into reviewer_research_field set user='22', id='2'

[MadTechie]

LOL Opps, But i think you got the idea.. if not try this

 

<?php
$delete = "DELETE FROM reviewer_research_field WHERE user='{$_SESSION['id']}' ";
mysql_query($delete) or die(mysql_error());
foreach($_POST['state'] as $newstate)
{
  #$write = "update reviewer_research_field as r set r.id='{$newstate}' where r.user='{$_SESSION['id']}' ";
   #mysql_query($write) or die(mysql_error());
   $insert = "INSERT INTO reviewer_research_field (`id` ,`user`) VALUES ('{$newstate}' , '{$_SESSION['id']}');";
   mysql_query($insert) or die(mysql_error());
}
?>

[plodos]

thank you one  more time