Inserting values from multiple checkboxes and textfields

Question

I am a beginner in PHP.I am stuck with a problem. The idea is that I have to assign actors to a selected movie and add a role for each. I need to pick several values from the list and add a description for each via texfields. My code adds all the checked values to the database, but it makes a mess with the values from the textfields, the checked values don't match with the description. I would be really grateful for your help! My code: Form:

<?php
$sqlquery = "SELECT artistId, firstname, lastname from $artists order by 2";
$result = mysqli_query($connect, $sqlquery);
if($result) {
    echo "<table class=\"addactor\">";
    echo "<tr>
        <td id=\"text\" colspan=\"2\"><h3>Assign an actor to the movie</h3></td>
    </tr>";
    while($sqlRow = mysqli_fetch_array($result, MYSQL_ASSOC)) {
        echo "<tr>";
        echo "<td>";
        echo "<input type=\"checkbox\" name=\"checkbox[]\" value=\"" . $sqlRow['artistId'] . "\"/> " . $sqlRow['firstname'] . " " . $sqlRow['lastname'] . "</td><td><input type=\"text\" name=\"textbox[]\"/></td>";
        echo "</tr>";

    }
    echo "<tr><td align=\"right\"><input type=\"submit\" name=\"submit\" id=\"submit\" value=\"Add\"></td><td><input type=\"reset\" name=\"reset\" id=\"reset\" value=\"Reset\"></td></tr></table>;";

}
print '</table>';

The connection to the database is in another file, which is included here.

The second part:

if($_POST) {
        $checkbox = $_POST['checkbox'];
        $txt = $_POST['textbox'];
        $len = sizeof($checkbox);
        for($i = 0; $i < $len; $i++) {
            $sqlqr = "INSERT INTO $role (artistId, movieCode, Description) VALUES ('" . $checkbox[$i] . "', '" . $_POST['moviecode'] . "', '" . $txt[$i] . "')";
            mysqli_query($connect, $sqlqr);
        }
        $query = "INSERT INTO $movies(movieCode, title, dateOfIssue,category, description, image) VALUES ('" . $_POST['moviecode'] . "', '" . $_POST['title'] . "', '" . $_POST['dateofissue'] . "','" . $_POST['category'] . "', '" . $_POST['desc'] . "', '" . $_POST['image1'] . "')";
        mysqli_query($connect, $query);
        if(mysqli_query($connect, $query) || mysqli_query($connect, $sqlqr)) {
            echo "<h4>1 record added</h4>";

        }
        else {
            die('Error: ' . mysqli_error($connect));
        }
        print '</form>';
    }

Answer 1

Unchecked values are not submitted and checkbox quantity not same with textbox. You should give input name array same keys :

$i = 0;
while($sqlRow = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    echo "<tr>";
    echo "<td>";
    echo "<input type=\"checkbox\" name=\"checkbox[".$i."]\" value=\"" . $sqlRow['artistId'] . "\"/> " . $sqlRow['firstname'] . " " . $sqlRow['lastname'] . "</td><td><input type=\"text\" name=\"textbox[".$i."]\"/></td>";
    echo "</tr>";
    $i++;
}

Use also this code:

$checkbox = $_POST['checkbox'];
$txt = $_POST['textbox'];
foreach ($checkbox as $key => $value)
    $sqlqr = "INSERT INTO $role (artistId, movieCode, Description) VALUES ('" . $value . "', '" . $_POST['moviecode'] . "', '" . $txt[$key] . "')";
    mysqli_query($connect, $sqlqr);
}

Answer 2

use mysql_escape_string($_POST['']) instead of the every field $_POST[''] in inside the mysqlquery.

Answer 3

As documented under 17.2.1 Control types:

When a form is submitted, only "on" checkbox controls can become successful.

In other words, the browser will only submit those checkbox controls that have been 'checked', yet will submit every textbox control irrespective of the status of the checkbox control with which you intended it to be associated.

Therefore, unless all checkbox controls were checked, the arrays $_POST['checkbox'] and $_POST['textbox'] created by PHP from the form submission will contain different numbers of elements—and, consequently, those with any given index may not match.

There are two ways of resolving this:

1. one can use client-side scripting to disable the textbox if the corresponding checkbox is unchecked: this will prevent the browser from submitting the textbox and, accordingly, the arrays in PHP will be aligned again (however note that this solution depends upon the availability of client-side script—you will have to test for and handle cases where such scripting is unavailable); or

2. one can give the controls explicit indexes to ensure that they are always aligned.

You also really ought to read up on proper string escaping (and how failure to do so exposes your application both to bugs and commonly exploited attack vectors): I thoroughly recommend @deceze's blog article, The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

In particular, as he describes in his article, you should ensure that you escape any HTML in your variables before transmission to the browser (in order to prevent XSS attacks and bugs where the text to be output contains characters that have special meaning in HTML, for example <):

$result = mysqli_query($connect, "
  SELECT   artistId, CONCAT(firstname, ' ', lastname) AS fullname
  FROM     $artists
  ORDER BY firstname
");

if ($result) {
  echo '
    <table class="addactor">
      <tr>
        <td id="text" colspan="2"><h3>Assign an actor to the movie</h3></td>
      </tr>';

  $i = 0;
  while ($sqlRow = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    echo '
      <tr>
        <td>
          <input type="checkbox"
                 name="checkbox[',$i,']"
                 value="', htmlentities($sqlRow['artistId']), '"
          />', htmlentities($sqlRow['fullname']), '
        </td><td>
          <input type="text" name="textbox[',$i,']"/>
        </td>
      </tr>';
    $i++;
  }

  echo '
      <tr>
        <td align="right">
          <input type="submit" name="submit" id="submit" value="Add">
        </td><td>
          <input type="reset" name="reset" id="reset" value="Reset">
        </td>
      </tr>
    </table>';
}

Also, concatenating unescaped strings supplied by the user directly into your SQL not only makes you vulnerable to SQL injection attack, but furthermore introduces bugs where the strings contain characters that have special meaning within SQL string literals (for example ').

The solution is to prepare SQL statements with placeholders for parameters that get subsituted with your variables upon command execution; this also provides a performance boost since the statements need only be prepared once irrespective of the number of times that they are executed:

if ($_POST) {
  $stmt = mysqli_prepare($connect, "
    INSERT INTO $movies
      (movieCode, title, dateOfIssue, category, description, image)
    VALUES
      (?, ?, ?, ?, ?, ?)
  ");

  mysqli_stmt_bind_param($stmt, 'ssssss',
    $_POST['moviecode'],
    $_POST['title'],
    $_POST['dateofissue'],
    $_POST['category'],
    $_POST['desc'],
    $_POST['image1']
  );

  mysqli_execute($stmt) or die('Error: ' . mysqli_error($connect));

  $stmt = mysqli_prepare($connect, "
    INSERT INTO $role
      (artistId, movieCode, Description)
    VALUES
      (?, ?, ?)
  ");

  mysqli_stmt_bind_param($stmt, 'sss',
    $checkbox,
    $_POST['moviecode'],
    $description
  );

  foreach ($_POST['checkbox'] as $i => $checkbox) {
    $description = $_POST['textbox' ][$i];
    mysqli_execute($stmt) or die('Error: ' . mysqli_error($connect));
  }

  echo '<h4>1 record added</h4></form>';
}