skip to Main Content

I am creating dynamic multiple row to store data into database. all working fine on front end as rows created and also multiple value selected in docname1 field by selected choosen. but the issue is that when I process code, then its save data sucessfully but docname1 storing only first value, rather than multiple selected one.

here is php code

<table class="table table-bordered">
                    <thead class="table-success" style="background-color: #3fbbc0;">
                      <tr>
                     <th width="10%"><center>Service</th>
                        <th width="10%"><center>Type</th>
                        <th width="10%"><center>Consultant</th>
            
            <th width="10%"><center>Qty/Graf.</th>                       
            <th width="10%"><center>Rate/Unit</th>
            <th width="10%"><center>Discount</th>           
            <th width="10%"><center>Total</th>
            <th width="10%"><center>Reff. By</th>
                        <th width="10%"><center>Asst By</th> 
                       <th width="10%"><center>OT</th> 
            <th width="10%"><center>Remarks</th>
            <th width="5%"></th>

                                               
                         <button type="button" class="btn btn-sm btn-success" onclick="BtnAdd()">Add Item</button>                         
                        </th>
</tr>
                    </thead>
                    <tbody id="TBody">
                      <tr id="TRow" class="d-none">



   <td><Select  class="country form-control text-end" name="country[]" id = "country"  required>
<option value=""> Select Service</option>
                <?php
                include('db1.php');
                $query = "select * from country";
                // $query = mysqli_query($con, $qr);
                $result = $con->query($query);
                if ($result->num_rows > 0) {
                    while ($row = mysqli_fetch_assoc($result)) {
                ?>
                <option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
                <?php
                    }
                }     ?>    </select> </td>

              

 <td><Select  class="state form-control text-end" name="state[]" id = "state" required>

<option value="">select Type</option></select></td>
 <td><Select  class="city form-control text-end" name="city[]" id = "city" required onchange="GetDetail(this.closest('tr'))" >

<option value="">Select Cons</option></select></td>
            

 
            
        <td><input type="text" class="qty form-control text-end" name="qty[]" id="ccc" onchange="Calc(this);"  Required></td>
    <td><input type="text" class="price form-control text-end" name="price1[]" id="ddd"   onfocus="Calc(this);" readonly style="background-color: #3fbbc0;"></td>
    <td><input type="text" class="discunt form-control text-end" name="discunt[]"  id="eee" onchange="Calc(this);"  ></td>
    <td><input type="text" class="tot4 form-control text-end" name="tot4[]" id="fff" oninput="Calc(this);"  Required readonly style="background-color: #3fbbc0;"></td>                  
       <td><Select class="form-control text-end" name="tech1[]" id="ggg" Required onfocus="Calc(this);">
       <option value="">Select Reff</option>
         <?php
         include('db.php');
        $sql = mysqli_query($con,"SELECT * FROM reff");
         while($row=mysqli_fetch_array($sql))
        {
         echo '<option value="'.$row['reffered'].'">'.$row['reffered'].'</option>';
          } ?>
           </select></td>

        <td><Select class="form-control text-end" name="docname[]" id="iii" required onfocus="Calc(this);">
    <option value="">Select Asst</option>
        <?php
        include('db.php');
      $sql = mysqli_query($con,"SELECT * FROM tech1");
        while($row=mysqli_fetch_array($sql))
         {
         echo '<option value="'.$row['techname'].'">'.$row['techname'].'</option>';
            } ?>  </select></td>

    <td> <select class="form-control text-end chosen-select" name="docname1[][]" multiple required>
    <option value="">Select OT</option>
        <?php
        include('db.php');
      $sql = mysqli_query($con,"SELECT * FROM mmmach");
        while($row=mysqli_fetch_array($sql))
         {
         echo '<option value="'.$row['mach1'].'">'.$row['mach1'].'</option>';
            } ?>  </select></td>


<td><input type="text" class="form-control text-end" name="remarks3[]"  id="zzz" >
         <input type="hidden" class="form-control text-end mop" name="remarks4[]"  id="zzz9" ><input type="hidden" class="zzz1 form-control text-end" name="zzz1[]"  id="zzz1" ><input type="hidden" class="zzz2 form-control text-end" name="zzz2[]"  id="zzz2" ><input type="hidden" class="zzz3 form-control text-end" name="zzz3[]"  id="zzz3" ><input type="hidden" class="zzz4 form-control text-end" name="zzz4[]"  id="zzz4" ><input type="hidden" class="zzz5 form-control text-end" name="zzz5[]"  id="zzz5" ></td>
        <td class="NoPrint"><button type="button" class="btn btn-success"  style="line-height: 1;" onclick="BtnDel(this)">x</button></td>
            
                      </tr>   </tbody> </table>

and below is processing code

<?php

if (isset($_POST['submit'])) {
    // Database details
    $host = "localhost";
    $username = "uuuuui";
    $password = "899999";
    $dbname = "uuii";

    // Creating a connection
    $con = mysqli_connect($host, $username, $password, $dbname);

    // Ensure the connection is made
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }

    // 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);
}
?>

2

Answers


  1. 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++) {
    
    Login or Signup to reply.
  2. 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 submit each selected value as docname[][]=foo&docname[][]=bar.

    Every time an empty braceis 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: 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][]

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search