update multiple records based on radio button selections

[peppericious]

Children are applying to play on teams. There are more children than there are places, so some will get to play for the Senior team, some for the Junior team, some will be Reserve, and some will be Unsuccessful.

 

I want to be able to pull up all children and assign their application result in the db in one update process.

 

I have figured out - eventually, thanks to other posts I've seen here - how to do such a single-query-multiple-records-update using just one 'successful' checkbox for each child, using this code:

<?php
include('includes/mysqli_connect.php');

// if the form is submitted, mark 'successful' children in db
if(isset($_POST['submit'])) {
  $list_of_ids = join(',', $_POST['child']);
    $sql = "UPDATE table1 SET `result` = 'successful' WHERE id IN ($list_of_ids)";
    $r = mysqli_query($dbc, $sql);
    if(!$r) {
      echo "<p style='color:red'>There was a problem updating the records. Please try again later.</p>";
    }
}
// get the children and display them
$q = "SELECT `id`, `child`, `result` FROM table1 WHERE `delete` = 0";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) { 
$output = '<form method="post">';
while ($row = mysqli_fetch_array($r)) {
  $id = $row['id'];
  $child = $row['child'];
  $result = $row['result'];
    $output .= "<p><input type='checkbox' value='$id' name='child[]'> $child: <strong>$result</strong></p>";
    
}
$output .= "<p><input type='submit' name='submit' value='Update Records' /></p>
</form>";
echo $output;
}

 

However, I have not succeeded in figuring out how to modify the above code so that each child has 4 radio buttons (Senior, Junior, Reserve, Unsuccessful), rather than the single checkbox. Thanks in advance for your help which would allow me do that...

[Barand]

Give each group of radio buttons for the child name="child[$id]" and values of Senior, junior, reserve, unsuccessful

[peppericious]

Give each group of radio buttons for the child name="child[$id]" and values of Senior, junior, reserve, unsuccessful

 

 

Thanks, Barand. I'm closer to my destination but the update isn't working. I'm getting 'There was a problem updating the records...'. Can you see anything obviously problematic in my code below?

 

<?php
include('includes/mysqli_connect.php');

// if the form is submitted, mark 'successful' children in db
if(isset($_POST['submit'])) {
  $result = $_POST['result'];
  $list_of_ids = join(',', $_POST['result']);
    $sql = "UPDATE table1 SET `result` = $result WHERE id IN ($list_of_ids)";
    $r = mysqli_query($dbc, $sql);
    if(!$r) {
      echo "<p style='color:red'>There was a problem updating the records. Please try again later.</p>";
    }
}
// get the children and display them (if they're not marked for deletion)
$q = "SELECT `id`, `child`, `result` FROM table1 WHERE `delete` = 0";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) { 
$output = '<form method="post"><table width="100%" cellpadding="2">';
while ($row = mysqli_fetch_array($r)) {
  $id = $row['id'];
  $child = $row['child'];
    $output .= "<tr>
    <td>$id</td>
    <td>$child</td>
      <td><label>
        <input type='radio' name='result[$id]' value='senior' />
        Senior</label>   
      <label>
        <input type='radio' name='result[$id]' value='junior' />
        Junior</label>   
      <label>
        <input type='radio' name='result[$id]' value='reserve' />
        Reserve</label>   
      <label>
        <input type='radio' name='result[$id]' value='unsuccessful' />
        Unsuccessful</label>
    </td>
  </tr>";
}
$output .= "</table>
<input type='submit' name='submit' id='submit' value='Run update' />
</form>";
echo $output;
}

[Barand]

You need to check what is now posted and change processing accordingly. Use

 

echo '<pre>', print_r($_POST, 1), '</pre>';

[peppericious]

You need to check what is now posted and change processing accordingly. Use

 

echo '<pre>', print_r($_POST, 1), '</pre>';

 

.. that's giving me this, for example:

 

Array
(
    [result] => Array
        (
            [1] => senior
            [2] => junior
            [3] => senior
            [4] => senior
            [5] => unsuccessful
        )

    [submit] => Run update
)

 

... but those data aren't getting written to the db when I click submit. I'm still getting 'There was a problem updating the records...'.

 

Any thoughts?

[Barand]

In your old query you had a single value and wanted to update all ids in the list with that value.

 

That is no longer the case so you have to change the processing.

 

Now you want to set ids 1,3,4 to "senior" etc.

[peppericious]

... you have to change the processing.

 

Now you want to set ids 1,3,4 to "senior" etc.

 

.. yes, thanks. However, I cannot figure out how to do that... Could I impose on you for some pointers?...

[Barand]

You now need a query like

 

UPDATE table1 SET result =

CASE

    WHEN id IN (1,3,4) THEN 'senior'

    WHEN id IN (2) THEN 'junior'

    WHEN id IN (5) THEN 'unsuccessful'

END

[peppericious]

You now need a query like

 

UPDATE table1 SET result =

CASE

    WHEN id IN (1,3,4) THEN 'senior'

    WHEN id IN (2) THEN 'junior'

    WHEN id IN (5) THEN 'unsuccessful'

END

 

.. thanks, but I'm flummoxed now. I have no idea how to manipulate my post data such that I can build a query like the above. I'll have to think again.

[Barand]

try

 

foreach ($_POST['result'] as $id => $result) {
    $results[$result][] = $id;
}

$query = "UPDATE table1 SET result = CASE \n";

foreach ($results as $res => $ids) {
    $query .= sprintf("WHEN id IN (%s) THEN '%s'\n", join(',', $ids), $res);
}
$query .= 'END';

[peppericious]

 

try

 

foreach ($_POST['result'] as $id => $result) {
    $results[$result][] = $id;
}

$query = "UPDATE table1 SET result = CASE \n";

foreach ($results as $res => $ids) {
    $query .= sprintf("WHEN id IN (%s) THEN '%s'\n", join(',', $ids), $res);
}
$query .= 'END';

 

Thank you so, so much for your help with this. It works perfectly. I can't say I understand right of the bat how it works, exactly, but I'm studying it right now...

 

One final question now, as I look at your code: what does this line do, exactly?

$results[$result][] = $id;

Edited March 31, 2013 by peppericious