Member Avatar for Benbo1980

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

  • 8 Contributors
  • 9 Replies
  • 1K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by freshmem
Member Avatar for R0bb0b

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.

Member Avatar for Benbo1980

Thankyou very much - that worked a treat!

Member Avatar for jinzing

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

Member Avatar for withremote

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?

Member Avatar for withremote

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.

Member Avatar for lemonizer

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;
	}
}
Member Avatar for Silvor

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.

Member Avatar for Helleshtern

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 :(

Member Avatar for freshmem

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.