update a field value for multiple records at once

[jeff5656]

How would I modify my code to allow a user to select multiple records (with a checkbox) and then process those records?  The checkbox, if selected would change the value of the field called "completed", which is an enum field that has either a "y" or a "n".

 

So the following spits out all the records (in the WHILE loop).  I want a checkbox next to each record then they would submit and those records with a checked box get the "completed" field changed. 

 

$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'n' "; 
$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {
<td ><a name="foobar" href="updatebill.php?action=unbill&id=<?php
echo $row['dos_id'].'&completed='.$row['completed']; ?>">	<?php echo $row['completed'];?></a></td>

 

Here is the processing script (updatebill.php):

 

$completed = ($_GET['completed'] == 'y')? 'n' : 'y'; 


$sql = "UPDATE dos SET
completed = '$completed'

WHERE dos_id = '" . $_GET['id'] . "'";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";
$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());
}

 

 

[redarrow]

<?php
$completed = ($_GET['completed'] == 'y' || 'n' ); 

$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'n' ";

$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {
?>
<td ><a name="foobar" href="updatebill.php?action=unbill&id=<?php
echo $row['dos_id'].'&completed='.$row['completed']; ?>">   <?php echo $row['completed'];}?></a></td>


$sql = "UPDATE dos SET
   completed = '$completed'
   
   WHERE dos_id = '" . $_GET['id'] . "'";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";

$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());
   }
$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'y' ";



$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {

//show ever think
}else{

// show what ever.
}
?>

what you mean...

[jeff5656]

That line refers to what happens if you click the href attached to "completed". 

[redarrow]

post your whole code looks funky show me please.

[premiso]

 

<?php
$completed = ($_GET['completed'] == 'y' || 'n' ); 

$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'y' ";
$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {

//show ever think
}else{

// show what ever.
}
?>

what you mean...

 

I think he is asking for how to setup the checkbox bud.

 

$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'n' ";
$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {
    echo '<td>' . $row['dos_id'] . '  completed? <input type="checkbox" name="completed[' . $row['dos_id'] . ']" ' . (($row['completed'] == 'y')?'checked':'') . ' /></td>';
}

 

First off you need a form to do this, inside the form I would put the ID you are going to reference.

 

Then when you process it, you would do this:

<?php
// change this to post or get depending on what you have the form do
if (isset($_POST)) {
    if (is_array($_POST['completed'])) {
         foreach ($_POST['completed'] as $id => $val) {
              if ($val == "on") { // I am not sure what you check here to see if the checkbox is checked
                     $sql = "UPDATE dos SET completed = 'y' WHERE dos_id = '" . $id . "' LIMIT 1";
                     mysql_query($sql) or die("SQL WAS $sql <br /> ERROR: " . mysql_error());
              }
        }
   }
}
?>

 

That should get you on your way.

[redarrow]

quick bash mate.

 

##ok sorry didnt understand you so sorry.

<?php
$completed = ($_GET['completed'] == 'y' || 'n' ); 

$query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'n' ";

$results = mysql_query ($query) or die (mysql_error());
while ($row = mysql_fetch_assoc ($results)) {
?>

<td ><a name="foobar" href="updatebill.php?action=unbill&id=<?php
echo $row['dos_id'].'&completed='.$row['completed']; ?>">   <?php echo $row['completed'];}?></a></td>

<?php

$sql = "UPDATE dos SET
   completed = '$completed'
   
   WHERE dos_id = '" . $_GET['id'] . "'";
if (isset($sql) && !empty($sql)) {
echo "<!--" . $sql . "-->";

$result = mysql_query($sql) or die ("Invalid query: " . mysql_error());
   }

   if($row['completed']=='y'){
   $query = "SELECT * FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'y' ";

$results = mysql_query ($query) or die (mysql_error());

while($row = mysql_fetch_assoc($results)) {

//show ever think

}
   }
?>

[jeff5656]

Sorry I am trying to understand this:

 

<?php echo '<td>' . $row['dos_id'] . '  completed? <input type="checkbox" name="completed[' . $row['dos_id'] . ']" ' . (($row['completed'] == 'y')?'checked':'') . ' /></td>';?>

 

Don't I need to put a value="y" or a value="n" here if it is checked?

[premiso]

Sorry I am trying to understand this:

 

<?php echo '<td>' . $row['dos_id'] . '  completed? <input type="checkbox" name="completed[' . $row['dos_id'] . ']" ' . (($row['completed'] == 'y')?'checked':'') . ' /></td>';?>

 

Don't I need to put a value="y" or a value="n" here if it is checked?

 

No, I think, now do not quote me on this, that checkboxes just return if they were "on" or "off". Given that, if the checkbox was on, you set it to 'y' if it was off you set it to 'n'.

 

The best bet is to create a static test page and setup multiple checkboxes, check half them then post it to a page that does a vardump of the $_POST data, and that will tell you the truth of what it does.

[Psycho]

A checkbox returns the value of the checkbox when it is checked. If it is not included in the post data at all. So the checkboxes should be set up with a value equal to the ID of the record. This is a pretty simple process that consists of two steps in my opinion.

 

1. Create a form with a checkbox for each record. The checkboxes should have the same name in the form of an array (e.g. 'IDS[]')

 

2. The page that processes the form will run a single query along the lines of "UPDATE table SET field='value' WHERE field2 in ([comma separated list from the checkbox array])"

 

Here is some sample code based upon what you posted:

 

Code to generate teh checkboxes (needs to be in a form that posts to the processing page)

$query = "SELECT dos_id, completed FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND dos.completed = 'n' "; 
$results = mysql_query ($query) or die (mysql_error());

//Create checkboxes for each record
while ($row = mysql_fetch_assoc ($results))
{
    echo "<input type=\"checkbox\" name=\"dos_ids[]\" value=\"{$row['dos_id']}\"> {$row['completed']}<br>\n";
}

 

Code for the processing page

if (isset($_POST['dos_ids']))

{

    //Set 'completed' value for ALL records to 'n'

    $sql = "UPDATE dos SET completed = 'n'";

    $result = mysql_query($sql) or die ("Invalid query: " . mysql_error());

 

    //Set 'completed' value to 'y' for all of the checked records

    $idList = "'" . implode("', '", $_POST['dos_ids']) . "'";

    $sql = "UPDATE dos SET completed = 'n' WHERE dos_id IN ($idList)";

    $result = mysql_query($sql) or die ("Invalid query: " . mysql_error());

}[code=php:0]

 

Note: I did not test any of that, but it should give you the process for doing what you have asked. Also, that only works if you are always going to have a page where all of the records are being updated at the same time. If you will only have some of the records to be updated you will need to pass some information from the form to the processing page to identify what records were being updated.