updating multiple rows with checkbox

Question

Helleshtern 0 Newbie Poster

16 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();
?>

mysql php

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

Dani AI

Generated 11 Months Ago

the reason every row is updating is that you post a hidden id[] for every row, then loop over that array. So even if a checkbox is unchecked, its id is still in $_POST['id']. The foreach warning you saw happens when the array you loop over does not exist (e.g., no boxes were checked or the input name does not match). was on the right track: drive your updates off the checkbox array itself, not the hidden ids. Also, avoid dynamic names like status$id and name$id; use bracketed arrays keyed by id. That makes the PHP trivial and prevents the foreach notice.

A simple pattern is to add a dedicated selection checkbox and post the editable fields as arrays keyed by id. Only rows with a checked box are updated.

<!-- table row -->
<td><input type="checkbox" name="selected[]" value="<?php echo (int)$row['id']; ?>"></td>
<td><input type="text" name="name[<?php echo (int)$row['id']; ?>]" value="<?php echo htmlspecialchars($row['name'], ENT_QUOTES); ?>"></td>
<td><input type="checkbox" name="status[<?php echo (int)$row['id']; ?>]" value="1" <?php echo $row['status'] ? 'checked' : ''; ?>></td>

<?php
// process
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $ids = isset($_POST['selected']) && is_array($_POST['selected']) ? array_map('intval', $_POST['selected']) : [];
    if ($ids) {
        $names  = $_POST['name']  ?? [];
        $status = $_POST['status'] ?? []; // only contains checked ids
        // mysqli/PDO strongly recommended; example with mysqli prepared stmt:
        $stmt = $mysqli->prepare("UPDATE $tbl_name SET name = ?, status = ? WHERE id = ?");
        foreach ($ids as $id) {
            $nameVal = $names[$id]  ?? '';
            $statVal = isset($status[$id]) ? 1 : 0; // set based on checkbox
            $stmt->bind_param('sii', $nameVal, $statVal, $id);
            $stmt->execute();
        }
        header('Location: update_multiple.php');
        exit;
    }
}

Notes:

If you prefer to use the existing status checkbox as the selector, name it status[ID] and iterate foreach (array_keys($_POST['status'] ?? []) as $id) { ... }. No warning, and only checked rows update.
The old mysql_* API is long deprecated; switch to mysqli or PDO and prepared statements, as above.

All 6 Replies

qazplm114477 14 Junior Poster

16 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 16 Years Ago by qazplm114477 because: n/a

LethargicCoder 0 Junior Poster in Training

16 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][/IMG]

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

Helleshtern 0 Newbie Poster

16 Years Ago

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

updating multiple rows with checkbox

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers