Hi there,

I have successfully setup a page using a tutorial (shown http://www.phpeasystep.com/mysql/10.html)

It's all working fine as it stands.

I have now added another column to the database that can either be 1 or 0 and I would like to be able to change it on the update page using a checkbox for each record.

The code I tried was as follows:

Code:

<strong>Update multiple rows in mysql</strong><br> 

<?php 
$host="localhost"; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="test_mysql"; // Table name 

// Connect to server and select databse. 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB"); 

$sql="SELECT * FROM $tbl_name"; 
$result=mysql_query($sql); 

// Count table rows 
$count=mysql_num_rows($result); 
?> 
<table width="500" border="0" cellspacing="1" cellpadding="0"> 
<form name="form1" method="post" action=""> 
<tr> 
<td> 
<table width="500" border="0" cellspacing="1" cellpadding="0"> 


<tr> 
<td align="center"><strong>Id</strong></td> 
<td align="center"><strong>Name</strong></td> 
<td align="center"><strong>Lastname</strong></td> 
<td align="center"><strong>Email</strong></td> 
<td align="center"><strong>On / Off</strong></td> 
</tr> 
<?php 
while($rows=mysql_fetch_array($result)){ 
?> 
<tr> 
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td> 
<td align="center"><input name="name[]" type="text" id="name" value="<? echo $rows['name']; ?>"></td> 
<td align="center"><input name="lastname[]" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td> 
<td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td> 
<td align="center"><input name="ONOFF[]" type="checkbox" id="ONOFF" value="1" 
<?php if ($rows['ONOFF'] ==1) { echo "checked";} else {} ?> 
</td> 
</tr> 
<?php 
} 
?> 
<tr> 
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> 
</tr> 
</table> 
</td> 
</tr> 
</form> 
</table> 
<?php 
// Check if button name "Submit" is active, do this 
if($Submit){ 
for($i=0;$i<$count;$i++){ 
$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]', ONOFF='$ONOFF[$i]' WHERE id='$id[$i]'"; 
$result1=mysql_query($sql1); 
} 
} 

if($result1){ 
header("location:update_multiple.php"); 
} 
mysql_close(); 
?>

What happens is weird - no matter which checkboxes are ticked, the database appears to update the wrong records. For example, if I had checked every other record (4 in total) and clicked submit, the database would have simply changed the top four records, not necessarily the ones I had originally selected.

Any ideas?!

Thanks,

Ben

    I think the reason why its only updating the first four record is because there is no ID reflecting which checkbox relates to what record.

    You could resolve this by populating the ONOFF checkbox input with a value that matches the ID of the record in which it relates i.e.

    <td align="center"><input name="ONOFF[]" type="checkbox" id="ONOFF" value="<?=$row['id']?>" 
<?php if ($rows['ONOFF'] ==1) { echo "checked";} else {} ?> 
</td>

    Now what you can do in submitting is initially set them all to not checked and hen iterate through the ONOFF array and only update those records that have an ID supplied in the $_POST['ONOFF'] array.

    This is a bit of a fudge but would work.

      2 months later

      This is a realy useful post, I'm going to see if it works for me - as I have an identical need.

      Cheers,
      Chris

        Simpler would be to specify the ID as part of the name:

        <input name="ONOFF[<?php echo $row['id']?>]">

        And do the same for all the other fields as well; Then the checkbox value for thingy 42 will be in $POST['ONOFF'][42] (if it's checked), the lastname value will be in $POST['lastname'][42] and so on. Before you start processing you can pass the $_POST array to a function like

        function build_records($form)
{
    $records = array();
    foreach($form as $fieldname=>$values)
    {
   		foreach($values as $id=>$value)
		{
			$records[$id][$fieldname] = $value;
		}
    }
	return $records;
}

        The same function can filter out extraneous/invalid stuff at the same time.

        Then you'd have an array of $records with elements like $records[42] that contains things like $records[42]['ONOFF']=1, $records[42]['firstname']='Eric' and so on.

        Incidentally, it is invalid to have multiple elements in a page with identical id attributes.

          Write a Reply...