updating multiple rows with checkbox

Question

Helleshtern 0 Newbie Poster

14 Years Ago

Hello!

I have a script to update multiple rows in database but it update all and I need to only values in checked rows be updated. What should I change to update only checked rows?

<?php
include 'connect_db.php';

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

// Count table rows
$count=mysql_num_rows($result);
if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status='".$_POST["status".$id]."', name='".$_POST["name".$id]."' WHERE id='".$id."'";
        $result1=mysql_query($sql1);
    }
    if($result1){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=update_multiple.php\">";
    }
}
else
{
?>
<strong>Update multiple rows in mysql</strong><br>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><strong>Id</strong></td>
<td><strong>Status</strong></td>
<td><strong>Name</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result))
{
?>
<tr>
<td><input type="hidden" name="id[]" value="<?php echo $rows['id']; ?>" /><?php echo $rows['id']; ?></td>
<td ><input name="status<?php echo $rows['id']; ?>" type="checkbox" id="status" value="1"
<?php if ($rows['status'] ==1) { echo "checked";} else {} ?>
></td>
<td><input name="name<?php echo $rows['id']; ?>" type="text" id="name" value="<?php echo $rows['name']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="3" 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
}
 
mysql_close();
?>

php

3 Contributors
6 Replies
3K Views
2 Days Discussion Span
Latest Post 14 Years Ago Latest Post by Helleshtern

qazplm114477 14 Junior Poster

14 Years Ago

I'm not 100% sure but you can try changing your code to this

<tr>

<td><input type="hidden" name="id[]" value="<?php echo $rows['id']; ?>" /><?php echo $rows['id']; ?></td>

<td ><input name="status<?php echo $rows['id']; ?>" type="checkbox" id="status" value="1"


//to

//remove the hidden field completely
<input type = "checkbox" name="status[]" value = "<?php echo $rows['id']; ?>">

//then

 foreach($_POST['status'] as $id)

Hope this helps

Edited 14 Years Ago by qazplm114477 because: n/a

LethargicCoder 0 Junior Poster in Training

14 Years Ago

Hello!

I have a script to update multiple rows in database but it update all and I need to only values in checked rows be updated. What should I change to update only checked rows?

<?php
include 'connect_db.php';

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

// Count table rows
$count=mysql_num_rows($result);
if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status='".$_POST["status".$id]."', name='".$_POST["name".$id]."' WHERE id='".$id."'";
        $result1=mysql_query($sql1);
    }
    if($result1){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=update_multiple.php\">";
    }
}
else
{
?>
<strong>Update multiple rows in mysql</strong><br>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><strong>Id</strong></td>
<td><strong>Status</strong></td>
<td><strong>Name</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result))
{
?>
<tr>
<td><input type="hidden" name="id[]" value="<?php echo $rows['id']; ?>" /><?php echo $rows['id']; ?></td>
<td ><input name="status<?php echo $rows['id']; ?>" type="checkbox" id="status" value="1"
<?php if ($rows['status'] ==1) { echo "checked";} else {} ?>
></td>
<td><input name="name<?php echo $rows['id']; ?>" type="text" id="name" value="<?php echo $rows['name']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="3" 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
}
 
mysql_close();
?>

From what I make out, you are displaying every row from a table, displaying id, name and a checkbox which is pre checked if the status=1. Then when the page is submitted, you want to update all rows that are checked with a possible name change and set the status to 1.

This should work for this case:

<?php
include 'connect_db.php';

if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status=1, name='".$_POST["name".$id]."' WHERE id='".$id."'";
        $result1=mysql_query($sql1);
    }
    if($result1){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=update_multiple.php\">";
    }
}
else
{
	// Moved this to else as it doesn't have to run for UPDATE
	$sql="SELECT * FROM $tbl_name";
	$result=mysql_query($sql);
	
	// Count table rows
	// $count=mysql_num_rows($result); // Don't see this being used.
	?>
	<strong>Update multiple rows in mysql</strong><br>
	<table width="500" border="0" cellspacing="1" cellpadding="0">
		<tr>
			<td>
				<form name="form1" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
				<table width="500" border="0" cellspacing="1" cellpadding="0">
				<tr>
					<td><strong>Id</strong></td>
					<td><strong>Status</strong></td>
					<td><strong>Name</strong></td>
				</tr>
				
				<?php
				while($rows=mysql_fetch_array($result))
				{
				?>
					<tr>
					<td><?php echo $rows['id']; ?></td>
					<td ><input name="id[]" type="checkbox" id="status<?php echo $rows['id']; ?>" value="<?php echo $rows['id']; ?>"
					<?php if ($rows['status'] ==1) { echo " checked";} else {} ?>></td>
					<td><input name="name<?php echo $rows['id']; ?>" type="text" id="name<?php echo $rows['id']; ?>" value="<?php echo $rows['name']; ?>"></td>
					</tr>
				<?php
				}
				?>
					<tr>
						<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
					</tr>
				</table>
				</form>
			</td>
		</tr>
	</table>
	<?php
	// Check if button name "Submit" is active, do this
}
 
mysql_close();
?>

This design doesn't allow you to change the status to anything else thought. Once changed, it will forever have a status of 1 and always show up pre-checked and therefore update again next time unless you un-check the row.

Reply to this topic

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.

Helleshtern 0 Newbie Poster · Answer 1 · 2010-04-25T16:23:55+00:00

I tried this but it doesn't work. I guess ID field can't be removed because it's used in other part of script. It seems to need more changes.

Helleshtern 0 Newbie Poster · Answer 2 · 2010-04-27T02:42:03+00:00

I'm afraid it still doesn't work:

Warning: Invalid argument supplied for foreach() on line 14

I know I can change status only to "1" (if checked) and none (if unchecked). After some unsuccessful tries I found solution how update data only in checked rows + back status to "0" after finish.

I know it may not be much correct code but it works. I would be grateful if someone could give me advice is this proper?

if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status='1' WHERE id='".$id."'";
        $result1=mysql_query($sql1);
		
		$sql2="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."',status='0' WHERE status='1'";
        $result2=mysql_query($sql2);
    }
    if($result2){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=update_multiple.php\">";
    }
}

LethargicCoder 0 Junior Poster in Training · Answer 3 · 2010-04-27T05:45:49+00:00

I'm afraid it still doesn't work:
Warning: Invalid argument supplied for foreach() on line 14
I know I can change status only to "1" (if checked) and none (if unchecked). After some unsuccessful tries I found solution how update data only in checked rows + back status to "0" after finish.
I know it may not be much correct code but it works. I would be grateful if someone could give me advice is this proper?
if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status='1' WHERE id='".$id."'";
        $result1=mysql_query($sql1);
		
		$sql2="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."',status='0' WHERE status='1'";
        $result2=mysql_query($sql2);
    }
    if($result2){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=update_multiple.php\">";
    }
}

If you don't want to change the value of status, just don't change it. comment line 17 and un-comment 18

I ran this code without problems

<?php

$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
mysql_select_db ("db", $link);

$tbl_name = "dani_1";


if($_POST['Submit'])
{
    foreach($_POST['id'] as $id)
    {
        $sql1="UPDATE ".$tbl_name." SET status=1, name='".$_POST["name".$id]."' WHERE id='".$id."'";
        //$sql1="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."' WHERE id='".$id."'";
        $result1=mysql_query($sql1,$link);
    }
    if($result1){
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=updatemultiple.php\">";
    }
}
else
{
	$sql="SELECT * FROM $tbl_name";
	$result=mysql_query($sql,$link);

	?>

	<strong>Update multiple rows in mysql</strong><br>
	<table width="500" border="0" cellspacing="1" cellpadding="0">
		<tr>
			<td>
				<form name="form1" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
				<table width="500" border="0" cellspacing="1" cellpadding="0">
				<tr>
					<td><strong>Id</strong></td>
					<td><strong>Status</strong></td>
					<td><strong>Name</strong></td>
				</tr>

				<?php
				while($rows=mysql_fetch_array($result))
				{
				?>
					<tr>
					<td><?php echo $rows['id']; ?></td>
					<td ><input name="id[]" type="checkbox" id="status<?php echo $rows['id']; ?>" value="<?php echo $rows['id']; ?>"
					<?php if ($rows['status'] ==1) { echo " checked";} else {} ?>></td>
					<td><input name="name<?php echo $rows['id']; ?>" type="text" id="name<?php echo $rows['id']; ?>" value="<?php echo $rows['name']; ?>"></td>
					</tr>
				<?php
				}
				?>
					<tr>
						<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
					</tr>
				</table>
				</form>
			</td>
		</tr>
	</table>
	<?php
}
mysql_close($link);
?>

[IMG]http://img132.imageshack.us/img132/3305/snag0008.png[/IMG]

[IMG]http://img23.imageshack.us/img23/3514/snag0007m.png[/IMG]

Helleshtern 0 Newbie Poster · Answer 4 · 2010-04-27T15:42:07+00:00

I'm still getting the same error when click on submit.