PHP/MYSQL. Updating multiple rows failing in the `for loop`

Question

I have following HTML table:

<form method="post" action="update-table.php">
    <table class="table-data" style="width: 960px;">
        <thead>
            <tr>
                <td class="sorting" rowspan="1" colspan="1" style="width: 193px;">ID</td>
                <td class="sorting" rowspan="1" colspan="1" style="width: 54px;">Live</td>

            </tr>
        </thead>
        <tbody>
            <tr class="odd">
                <td class="nth-1"><input type="text" value="12" name="id"></td>
                <td class="nth-2"><input type="checkbox" checked="checked" name="live"></td>

            </tr>
            <tr class="even">
                <td class="nth-1"><input type="text" value="11" name="id"></td>
                <td class="nth-2"><input type="checkbox" checked="checked" name="live"></td>

            </tr>
            <tr class="odd">
                <td class="nth-1"><input type="text" value="10" name="id"></td>
                <td class="nth-2"><input type="checkbox" checked="checked" name="live"></td>

            </tr>
        </tbody>
    </table>
<input type="submit" />

and I'm trying to update live values accordingly to the ids with this file:

<?php
# update
session_name('users');
session_set_cookie_params(2*7*24*60*60);
session_start();

define('INCLUDE_CHECK',true);

require 'connect.php';
require 'functions.php';

if(!$_SESSION['id']) {
    header ("Location: index.php"); 
}


        //Function to sanitize values received from the form. Prevents SQL injection
        function clean($str) {
            $str = @trim($str);
            if(get_magic_quotes_gpc()) {
                $str = stripslashes($str);
            }
            return mysql_real_escape_string($str);
        }

        //Sanitize the POST values
        $id = clean($_POST['id']);
        //$usr2 = $_SESSION['usr'];
        $live = (isset($_POST['live']))?1:0;
        //$updated = date("F j, Y, g:i a",time()+60*60);

        //Create INSERT query
        foreach ($display_order as $id => $live) {
            $sql = "UPDATE news SET display_order = $live WHERE id = $id";
            $result = mysql_query($sql);

            if($result) {
                //header("location: notes.php");
                //exit();
                print_r($sql);
                print_r($display_order);
            }else {
                die("Query failed");

            }
        }
?>

But I'm getting an error:

Warning: Invalid argument supplied for foreach() in C:\Documents and Settings\USER\Desktop\Dropbox\wamp_at_work\update-table.php on line 33

Line 33 is: foreach ($display_order as $id => $live) {

What the problem? Any suggestions much appreciated.

Answer 1

The problem is that you're duplicating the 'name' attributes of the input elements. What you want to do is send arrays back to the server, ie:

Updated with extended answer:

<?php

$inp_orders = !empty($_POST['live']) ? $_POST['live'] : array();

// proccess post
if ($inp_orders)
{
        foreach ($inp_orders as $id => $live) {
            $id = (int) $id;
            $live = (bool) $live;

            // update orders
            $sql = "UPDATE news SET display_order = $live WHERE id = $id";
            $result = mysql_query($sql);

                if($result) {
                    echo 'News updated';
                }else {
                    die("Query failed");
                }
        }
}

// get orders from db
$res = mysql_select("SELECT * FROM news");
$view_orders = array();

    while ($row = mysql_fetch_assoc($res))
    {
        $view_orders['id'] = $row['id'];
        $view_orders['live'] = $row['live'];
    }

?>


<form method="post" action="">
    <table class="table-data" style="width: 960px;">
        <thead>
            <tr>
                <td class="sorting" rowspan="1" colspan="1" style="width: 193px;">ID</td>
                <td class="sorting" rowspan="1" colspan="1" style="width: 54px;">Live</td>

            </tr>
        </thead>
        <tbody>
                    <?php foreach ($view_orders as $order): $id = (int) $order['id']; $odd = true; ?>
                    <tr class="<?php echo $odd ? 'odd' : 'even' ?>">
                        <td class="nth-1"><?php echo $id ?></td>
                        <td class="nth-2"><input type="checkbox" <?php echo $order['live'] ? 'checked="checked"' : ''  ?> name="live[<?php echo $id ?>]" /></td>
                    </tr>
                    <?php $odd = $odd ? false : true; endforeach; ?>
        </tbody>
    </table>
    <input type="submit" />
</form>

Answer 2

I'm going to assume people can not change the ID. So do this:

<td class="nth-2">
  <input type="hidden" name="live[12]" value="0">
  <input type="checkbox" name="live[12]" value="1" checked>
</td>

That will get you an array called $_POST['live'] with either 0 or 1 in it depending on if they clicked it.

The hidden field is because if they don't click it nothing at all is sent, which can be more difficult to parse, so first I send a 0, then overwrite it if the checkbox is checked.

If people can change the ID you'll need to modify it a bit. Leave a comment if so.