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:

<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

Try this

<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="<? echo $_SERVER['REQUEST_URI']; ?>"> 
<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"><input type="hidden" name="id[]" value="<? echo $rows['id']; ?>" /><? echo $rows['id']; ?></td> 
<td align="center"><input name="name<? echo $rows['id']; ?>" type="text" id="name" value="<? echo $rows['name']; ?>"></td> 
<td align="center"><input name="lastname<? echo $rows['id']; ?>" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td> 
<td align="center"><input name="email<? echo $rows['id']; ?>" type="text" id="email" value="<? echo $rows['email']; ?>"></td> 
<td align="center"><input name="ONOFF<? echo $rows['id']; ?>" 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)
{ 
	foreach($_POST['id'] as $id)
	{ 
		$sql1="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."', lastname='".$_POST["lastname".$id]."', email='".$_POST["email".$id]."', ONOFF='".$_POST["ONOFF".$id]."' WHERE id='".$id."'"; 
		$result1=mysql_query($sql1); 
	} 
} 

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

You were looping through with a counter and using the loopnumber as your id, this will only work if you are absolutely positive that your ids are sequential, looks like you are using register globals I think. I've never used them so I actually find your code confusing.

Thankyou very much - that worked a treat!

i was having the same problem and this solution worked great! thanks :icon_mrgreen:

I'm getting a Warning: Cannot modify header information - headers already sent by from

<form name="form1" method="post" action="<? echo $_SERVER['REQUEST_URI']; ?>">

when the script hits

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

Otherwise it works like a charm. Any idea as to how to avoid this error?

I figured it out.

I needed to use ob_start(); at the top of my page, before any of the script runs, then ob_end_flush(); directly after the redirect.

Using the code listed above, this is a subset of code I used for Radio buttons instead of checkboxes.

In addition, I was having trouble detecting the Submit button being used, so that section of the code was changed.

if ($row['YesNo']=="Yes")
  {
  echo "<td><INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"Yes\" CHECKED> Yes <INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"No\" > No</td>";
  }
	else {
	echo "<td><INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"Yes\" > Yes <INPUT type=\"radio\" name=\"YesNo".$row['ID']."\" value=\"No\" CHECKED> No</td>";
	}
  echo "</tr>";
  }
echo "</table>";
}
?>
<input type="Submit" name="Submit" value="Submit">
</form>
<?php
Interviewed='".$_POST['YesNo'.$id]."' WHERE ID=".$_POST['id'];
//echo "<p>SQL:".$sql1;

//check if Submit button is active
if (isset($_POST['Submit']))
{
	echo "<p>submit pressed";
	foreach($_POST['id'] as $id)
	{
		$sql1="UPDATE table SET YesNo='".$_POST['YesNo'.$id]."' WHERE ID=".$id;
		$result1=mysql_query($sql1);
		echo "<p>".$sql1;
	}
}

This is excellent, helped me greatly!

One question - how can I get it to INSERT a row if there is currently no row with that ID?

I want to be able to update all rows that it can update and insert rows which aren't currently there?

Thank you.

I'm trying to use code posted by R0bb0b but it doesn't work for me. After submit nothing happend - status of checked rows don't change. Any help would be appreaciated :(

i was trying the given code.and i just change the db name ,etc

<?php 
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="root"; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="coba"; // 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="<? echo $_SERVER['REQUEST_URI']; ?>"> 
<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>Address</strong></td> 
<td align="center"><strong>Comment</strong></td> 
<td align="center"><strong>On / Off</strong></td> 
</tr> 
<?php 
while($rows=mysql_fetch_array($result))
{ 
?> 
<tr> 
<td align="center"><input type="hidden" name="ID[]" value="<? echo $rows['ID']; ?>" /><? echo $rows['ID']; ?></td> 
<td align="center"><input name="Name<? echo $rows['ID']; ?>" type="text" id="Name" value="<? echo $rows['Name']; ?>"></td> 
<td align="center"><input name="Address<? echo $rows['ID']; ?>" type="text" id="Address" value="<? echo $rows['Address']; ?>"></td> 
<td align="center"><input name="Comment<? echo $rows['ID']; ?>" type="text" id="Comment" value="<? echo $rows['Comment']; ?>"></td> 
<td align="center"><input name="ONOFF<? echo $rows['id']; ?>" 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)
{ 
	foreach($_POST['ID'] as $id)
	{ 
		$sql1="UPDATE ".$tbl_name." SET name='".$_POST["Name".$id]."', Address='".$_POST["Address".$id]."', Comment='".$_POST["Comment".$id]."', ONOFF='".$_POST["ONOFF".$id]."' WHERE id='".$id."'"; 
		$result1=mysql_query($sql1); 
	} 
} 
 
if($result1){ 
header("location:update_multiple.php"); 
} 
mysql_close(); 
?>

but..when i want to update it always can not be updated..
i think the problem is in this line.IMHO

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

for example

Name<? echo $rows['ID']; ?>" type="text" id="Name" value="<? echo $rows['Name']

it will be always return to the inputed value although it has been updated..

anyone can help me?
before i`m really sorry that my english is not so good.. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.