php update multiple rows based on checkbox selections

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

php update multiple rows based on checkbox selections

Post by cjkeane »

hi everyone:

i need to update multiple rows based on if checkboxes are selected. i have a dropdon list with case workers names, when a name is selected from the list, and the input button 'apply' is clicked, i need to update every row with the new case workers name. i've been struggling with it for quite a while so i'm posting to see if anyone could have any pointers for me?

every row has a checkbox and when any checkbox is selected, i need to update the caeworkers name to the name selected from the caseworker dropdown list. currently nothing happens when 'apply' is clicked. i'm not sure what i'm doing wrong.

my code is:

Code: Select all

<?php
	if(isset($_POST['Apply'])){ 
	$NewCaseWorker = $_POST['CaseWorker'];
	foreach($_POST['selectreassign'] as $key => $value) { 
    	mysql_query("UPDATE records SET CaseWorker='$NewCaseWorker' WHERE id='$value')") or die(mysql_error());
  	 }
}
?>
<form id="form1" name="form1" method="post" action="">
<table width="300" border="0" cellspacing="1" cellpadding="1">
<tr>
<td>Reassign To</td>
     <td>

<?php  
        $result=mysql_query("SELECT ProperName FROM caseworker"); 
        $options=""; 
        while ($row=mysql_fetch_array($result))  { 
            $selected = ($row['ProperName']==$CaseWorker) ? ' selected="selected"' : '';
	    $options .= "<option value=\"{$row['ProperName']}\"{$selected}>{$row['ProperName']}</option>\n"; 
         } 
?>

          <select name="CaseWorker" id="CaseWorker" >
               <option value="">< select owner > <?php echo $options ?></option>
          </select> 
          <input type="submit" name="Apply" id="Apply" value="Apply" />
     </td>
</tr>
</table>
</form>
<br />

<?php
$ReassignTo = $_POST['CaseWorker'];

// get results from database
    $string = "SELECT * FROM records ORDER BY id DESC"; 
    $query = mysql_query($string) or die (mysql_error());
    $num_rows = mysql_num_rows($query);

if($num_rows>0) {
     $pages = new Paginator;
     $pages->items_total = $num_rows;
     $pages->paginate();
     echo $pages->display_pages();
     echo $pages->display_items_per_page();
     echo '<hr>';
} else {
echo "<div id='titles_smaller'>No data available.</div><br />";
}
     echo "<table class='sortable' width='100%' border='0' cellpadding='1'>";
     echo "<tr><th nowrap>Select</th> <th nowrap>ID #</th><th nowrap>Company Name</th><th nowrap>Case Worker</th><th nowrap>Recorded</th></tr>";
     $string = $string."$pages->limit";
     $query = mysql_query($string) or die (mysql_error());
     $result = mysql_fetch_array($query);

if($result==true) { 
do 
     { 
     $id = $result['id'];
     echo "<tr>";
     echo "<td>";
     echo "<input type='checkbox' name='selectreassign[]' value='<?php echo $id ?>' />";
     echo "</td>";
     echo '<td nowrap><a href="details.php?id=' . $result['id'] . '">' . $result['id'] . '</a></td>';
     echo '<td nowrap>' . $result['CompanyName'] . '</a></td>';
     echo '<td nowrap>' . $result['CaseWorker'] . '</td>';	
     echo '<td nowrap>' . $result['DateRecorded'] . '</td>';	
     echo "</tr>"; 
    }
	while($result = mysql_fetch_array($query));
    }                
     // close table>
     echo "</table><hr>"; 
      
if($num_rows>0) {
     echo $pages->display_pages().$pages->display_items_per_page();
}
     // end pagination
}

?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: php update multiple rows based on checkbox selections

Post by social_experiment »

Can you paste the code used to create the checkboxes
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Idri
Forum Newbie
Posts: 19
Joined: Sun May 29, 2011 9:21 am

Re: php update multiple rows based on checkbox selections

Post by Idri »

When stumbling across errors in code, it's best if you have the errors shown to you when running through your work.
You can enable this by placing

Code: Select all

error_reporting(E_ALL);
at the top of your code. This should help you solve most of these issues.

Now, the reason as to why nothing is happening is because the checkboxes are not located within your form. Thus they won't get sent over with the other data.
You can check this by placing the following.

Code: Select all

if(isset($_POST['Apply'])){
	print_r($_POST); // Add this line
As you can see it'll output just the CaseWorker and Apply. Simply close the form later on in the page, after the checkboxes, to fix this.
There's also a problem at the line of code where you fill in the checkbox value, if you look at it closely you should be able to spot it.

Code: Select all

<?php 
// code
echo "<input type='checkbox' name='selectreassign[]' value='<?php echo $id?>';
// More code
?>
Though it works, it might be worth giving the code where you fill your dropdown menu another look as well, the HTML code isn't what it should be. :)
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php update multiple rows based on checkbox selections

Post by cjkeane »

this section of the code i previously posted is where the checkboxes are created:

Code: Select all

<?php
	$id = $result['id'];
	?>
    <?php

	echo "<tr>";
	echo "<td>";
    echo "<input type='checkbox' name='selectreassign[]' value='<?php echo $id ?>' />";
	echo "</td>";
?>
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php update multiple rows based on checkbox selections

Post by cjkeane »

IDri:

i added the checks you suggested and now i'm receiving the following error:
Array ( [CaseWorker] => Smith, John [Apply] => Apply [selectreassign] => Array ( [0] => ) ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1.

at least now i see an error. as before nothing happened. i can see by the array that its finding the name selected in CaseWorker list, and its finding the name of the checkbox 'selectreassign' but no matter which caseworker i select, the array value still says Array ([0]). i'm not sure how to proceed. any suggestions?
Idri
Forum Newbie
Posts: 19
Joined: Sun May 29, 2011 9:21 am

Re: php update multiple rows based on checkbox selections

Post by Idri »

Sorry - I must have missed the syntax error. I just noticed it myself as well.

In your query you have a ')' which shouldn't be there.

As to why the ID's aren't working, re-read my previous reply and take a look at the bottom bit. Notice the <?php echo $id ?> in your echo statement. It shouldn't be there, just $id.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php update multiple rows based on checkbox selections

Post by cjkeane »

i noticed the ) a few moments ago as well. thanks.
i did read your message about my html being incorrect and i did remove the <?php ?> as part of the checkbox value, but it still didnt help. now i'm receiving the following error:
Array ( [CaseWorker] => John, Smith [Apply] => Apply [selectreassign] => Array ( [0] => $id ) ) Unknown column 'id' in 'where clause'
my mistake. i corrected it, now no sql errors occur, but i do still have the following displayed and the update query fails to update
Array ( [CaseWorker] => Smith, John [Apply] => Apply [selectreassign] => Array ( [0] => $id ) )

in addition, i selected multiple checkboxes on different rows and when i click 'apply' i receive the following:
Array ( [CaseWorker] => Smith, John [Apply] => Apply [selectreassign] => Array ( [0] => [1] => ) )

is there something wrong with my foreach loop?
Idri
Forum Newbie
Posts: 19
Joined: Sun May 29, 2011 9:21 am

Re: php update multiple rows based on checkbox selections

Post by Idri »

Did you by chance start the echo statement with single quotes? I'm guessing you did :)

There's a difference between single and double quotes. For example

Code: Select all

$foo = "bar";
echo '$foo'
// Outputs $foo
$foo = "bar"
echo "$foo";
// Outputs bar
Wrapping text in single quotes makes it a literal string, which means that it'll ignore any variables passed into it and interpret it as actual text.

Edit:

There's nothing wrong with your foreach loop as when I'm testing it with 2 checkboxes ticked it outputs
UPDATE records SET CaseWorker='bob' WHERE id='1'
UPDATE records SET CaseWorker='bob' WHERE id='2'
What print_r does is show you the array's structure and contents. I'm assuming that
Array ( [CaseWorker] => Smith, John [Apply] => Apply [selectreassign] => Array ( [0] => [1] => ) )
is from before you changed the echo. Once fixed it should look something along the lines of

Code: Select all

Array
(
    [CaseWorker] => Bob
    [Apply] => Apply
    [selectreassign] => Array
        (
            [0] => 1
            [1] => 2
        )

)
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php update multiple rows based on checkbox selections

Post by cjkeane »

Perfect! I figured it out with your help and a bit of troubleshooting!
Post Reply