Page 1 of 1

php update multiple rows based on checkbox selections

Posted: Fri Jun 24, 2011 9:00 pm
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
}

?>

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 4:57 am
by social_experiment
Can you paste the code used to create the checkboxes

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 5:02 am
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. :)

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 11:51 am
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>";
?>

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 12:05 pm
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?

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 12:13 pm
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.

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 12:24 pm
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?

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 12:40 pm
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
        )

)

Re: php update multiple rows based on checkbox selections

Posted: Sat Jun 25, 2011 12:53 pm
by cjkeane
Perfect! I figured it out with your help and a bit of troubleshooting!