-3

I am creating an HTML form with repeatable rows. One of the fields in each row (docname1) is a multi-select field.

The problem is that I am only able to access the first selected value of the select field in a given row, but of course, I need to access all of them.

Here is the simplified HTML form and the initial row of repeatable fields:

<table>
    <tbody id="TBody">
        <tr id="TRow">
            <td><select name="country[]" id="country">
                <!-- irrelevant population of options -->
                </select></td>
            <td><select name="state[]" id="state">
                <!-- irrelevant population of options -->
                </select></td>
            <td><input type="text" name="qty[]" id="ccc"></td>
            <td><input type="text" name="price1[]" id="ddd"></td>
            <td><input type="text" name="discunt[]"  id="eee"></td>
            <td><input type="text" name="tot4[]" id="fff"></td>
            <td><select name="tech1[]" id="ggg">
                <!-- irrelevant population of options -->
                </select></td>
            <td><select name="docname[]" id="iii">
                <!-- irrelevant population of options -->
                </select></td>

<!-- <select name="docname1[][]" multiple> is the concern: -->
            <td><select class="chosen-select" name="docname1[][]" multiple>
                <!-- irrelevant population of options -->
                </select></td>

            <td><input type="text" name="remarks3[]" id="zzz">
                <!-- some other irrelevant hidden fields -->
            </td>
            <td class="NoPrint"><button type="button" onclick="BtnDel(this)">x</button></td>
        </tr>
    </tbody>
</table>

and below is processing code

if (isset($_POST['submit'])) {
    // declare database connection as $con
    // Process each set of form inputs
    $numRows = count($_POST['city']);  // Get the number of rows

    for ($i = 0; $i < $numRows; $i++) {
        // Handle multi-select field docname1
        $docname1Array = isset($_POST['docname1'][$i]) ? $_POST['docname1'][$i] : [];
        $docname1 = implode(',', (array) $docname1Array);

        // Retrieve and sanitize form inputs
        $country = mysqli_real_escape_string($con, $_POST['country'][$i]);
        $state = mysqli_real_escape_string($con, $_POST['state'][$i]);
        $city = mysqli_real_escape_string($con, $_POST['city'][$i]);
        $qty = mysqli_real_escape_string($con, $_POST['qty'][$i]);
        $price1 = mysqli_real_escape_string($con, $_POST['price1'][$i]);
        $tot4 = mysqli_real_escape_string($con, $_POST['tot4'][$i]);

        // Prepare SQL statement
        $sqlInsertItem = "
            INSERT INTO iap44 (country, state, city, qty, price1, tot4, docname1) 
            VALUES ('$country', '$state', '$city', '$qty', '$price1', '$tot4', '$docname1')";
        
        // Execute SQL statement
        $rs1 = mysqli_query($con, $sqlInsertItem);

        if (!$rs1) {
            echo "Error: " . mysqli_error($con);
        }
    }

    // Debugging output
    echo "<pre>";
    print_r($_POST);    
    echo "</pre>";

    // Close the connection
    mysqli_close($con);
}
9
  • 1
    use always prepared statements to aoid sql injection Commented Aug 2, 2024 at 20:17
  • 2
    so switch to prepared statements and see it the problem persists Commented Aug 2, 2024 at 20:34
  • 1
    and enable the error reporting stackoverflow.com/questions/12227626/… Commented Aug 2, 2024 at 20:43
  • 2
    @nbk Why would it make a difference? The problem is almost certainly happening in the implode() call, not the database query. Commented Aug 2, 2024 at 21:01
  • 2
    you must get an error show show us the error and a comma separated string, shouldn't be stored in a column,see stackoverflow.com/questions/3653462/… Commented Aug 2, 2024 at 21:22

2 Answers 2

0

If you are not having multiple values for country, state and city then try to do following things:

  1. Use name attributes like following: country, state, and city instead of country[], state[], and city[] respectively.
  2. Use name attribute for docname1 like this docname1[].
  3. It is not good practice to store same record multiple times only for one changed value, so what you can do is, create another table and store docname1 all values along with foreign key of iap4 inserted record. For this you can use following approach:
if (isset($_POST['submit'])) {
  $country = $_POST['country'];
  $state = $_POST['state'];
  $city = $_POST['city'];
  $docname1 = $_POST['docname1'];

  $sqlInsertItem = "INSERT INTO iap4 (country, state, city) VALUES ('$country', '$state', '$city')";

  if (mysqli_query($con, $sqlInsertItem)) {
    $iap4_id = mysqli_insert_id($con);

    foreach ($docname1 as $docname) {
      $sqlInsertDoc = "INSERT INTO doc_names (iap4_id, docname) VALUES ('$iap4_id', '$docname')";

      if (!mysqli_query($con, $sqlInsertDoc)) {
        echo "Error inserting docname: " . mysqli_error($con);
      }
    }
  } else {
    echo "Error inserting item: " . mysqli_error($con);
  }
}
  1. If this is not what you need, and you wanna keep using single table to insert record then you should consider following things, that might be causing issues:

i) You have closed form submission condition here, which is not correct. You should close this bracket where logic of record insertion is ending.

$docname1 = $_POST['docname1']; // Multidimensional array
}

ii) You have used a condition to count cities and insert record, which I think is wrong. Because there might be only one city selected so it will execute only once. So, use condition on valid attribute.

for ($i = 0; $i < count($city); $i++) {
Sign up to request clarification or add additional context in comments.

3 Comments

actually i am using dynamic created rows, thats why i have to store country state city value with array instead of single one
Even if you are generating dynamic values, but when you are submitting the form if there is only single value is supposed to be submitted for country, state, and city then try my provided solution. I am pretty sure it will work.
No developer should be directly injecting user supplied data into a database query.
0

The problem that you are encountering is demonstrably problematic. Experiment at https://www.w3schools.com/tags/tryit.asp?filename=tryhtml_select_multiple (I don't know where else this can be easily reproduced online) by using cars[][] in the form, select a couple options, then submit.

You will see that your name="docname1[][]" multiselect approach will transfer the literal name to the submission payload like: docname1[][]=foo&docname1[][]=bar.

Every time an empty brace is used (on any level of an array, PHP will auto-increment the index of that element. Because you are using double empty braces, this effectively means that each value will be pushed to the next available parent-level index AND the next available 2nd-level index (which, in this context, will always be the 0 index of the newly created parent).

Basic example #1: https://3v4l.org/tjosn

$qs = 'city[]=paris&docname1[][]=foo&docname1[][]=bar';

parse_str($qs, $result);
var_export($result);

Output: (bar value is not in the same subarrayas foo)

array (
  'city' => array (
    0 => 'paris',
  ),
  'docname1' => 
  array (
    0 => 
    array (
      0 => 'foo',
    ),
    1 => 
    array (
      0 => 'bar',
    ),
  ),
)

Basic example #2: https://3v4l.org/5ggSY

$_POST['docname1'][][] = 'one';
$_POST['docname1'][][] = 'two';
$_POST['docname1'][][] = 'three';
var_export($_POST);

Output:

array (
  'docname1' => 
  array (
    0 => 
    array (
      0 => 'one',
    ),
    1 => 
    array (
      0 => 'two',
    ),
    2 => 
    array (
      0 => 'three',
    ),
  ),
)

Due to this inherent challenge, your dynamic/repeatable fields should be explicitly keyed on the parent level. Otherwise you will have no ability to track which multiselected value belongs with which group of other fields.

If these groups of fields are repeatable, you MUST uniquely id each element of your HTML document to make it valid. Also names will be like city[$i] and docname[$i][]. The $i, if not possibly populated by PHP, should be programmatically incremented by your client-side scripting language.

You might give your <tr> row a convenient attribute from which your client-side script can increment when generating another row. E.g. <tr data-num="0">. That number will align with all field suffixes and indexes of that row.

Once you've corrected your HTML form, implementing a prepared statement and executing looped insert queries is simple. See https://stackoverflow.com/a/60178576/2943403 for the basic shape of preparing and binding only once, then executing the statement inside a loop. Depending on your PHP version you may wish to change the syntax.

if (!empty($_POST['city'])) {
    $stmt = $con->prepare("
        INSERT INTO iap44 (country, state, city, qty, price1, tot4, docname1) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    foreach ($_POST['city'] as $i => $city) {
        // perform validation and sanitization before executing
        $stmt->execute([
            $_POST['country'][$i],
            $_POST['state'][$i],
            $city,
            $_POST['qty'][$i],
            $_POST['price1'][$i],
            $_POST['tot4'][$i],
            implode(',', $_POST['docname1'][$i])
        ]);
    }
}

There are many other refinements to recommend, but I'll draw the line at these essential parts.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.