Insert to database multiple options from HTML form via PHP

Question

I have drop-down list, after selecting any value It adding new row with some fields, example shown in image below:

I have insert.php to insert values to MySQL database. But there is problem, that only values from first row are inserted to database.

PHP looks like:

foreach($_POST['CertificateType'] as $key => $val){ 

    $CertificateType = $val;

    $CertificateType = $_POST['CertificateType'][$key]; 
    $STCWCode        = $_POST['STCWCode'][$key];            
    $CertNo          = $_POST['CertNo'][$key];          
    $FromCert        = $_POST['FromCert'][$key];            
    $ToCert          = $_POST['ToCert'][$key];  

    $CertificateType = mysqli_real_escape_string($link, $CertificateType);  
    $STCWCode        = mysqli_real_escape_string($link, $STCWCode);                 
    $CertNo          = mysqli_real_escape_string($link, $CertNo);           
    $FromCert        = mysqli_real_escape_string($link, $FromCert);                 
    $ToCert          = mysqli_real_escape_string($link, $ToCert);      

    $sql3 = "INSERT INTO Tbl (
        CertificateType     
        ,UserId    
        ,STCWCode               
        ,CertNo                 
        ,FromCert               
        ,ToCert
        ,DateCreated
    ) VALUES (
        '$CertificateType',
        '$UserID',  
        '$STCWCode',            
        '$CertNo',          
        '$FromCert',            
        '$ToCert',
        now())";
    if(mysqli_query($link, $sql3)){
        echo "Resume created successfully.";
    } else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
    }
}

HTML looks like:

<fieldset class="fieldset-borders">
    <legend>4. Licenses & Certificates</legend>
    <ul class="header"> 
      <li>
        <select id='options' name="CertificateType[]" class="field-style div-format align-left">
          <option selected disabled value="0">Select certificates</option>
          <option value="1">One</option>
          <option value="2">Two</option>
          <option value="3">Three</option>  
        </select>  
      </li>
    </ul>  
    <ul class="cert" id="cert">     
      <li>
        <ul class="column">         
          <li><p class="test-label11">Name</p></li>                     
        </ul>
      </li>
      <li>
        <ul class="column">         
          <li><p class="test-label11">STCW Code</p></li>                        
        </ul>
      </li>
      <li>
        <ul class="column">         
          <li><p class="test-label11">Cert. No</p></li>                     
        </ul>
      </li>
      <li>
        <ul class="column">         
          <li><p class="test-label11">Place of Issue</p></li>                       
        </ul>
      </li>
      <li>
        <ul class="column">         
          <li><p class="test-label11">Date of Issue</p></li>                        
        </ul>
      </li>
      <li>
        <ul class="column">         
          <li><p class="test-label11">Date of Expire</p></li>                       
        </ul>
      </li>
      </ul>
        <div class="action2" ></div>


</fieldset>

Javascript code you can check at JS FIDDLE

I've created JS FIDDLE to check that part of the form. Have you ideas how to fix It?

Do you get some error message? Do you have a table that has an auto incremented ID? — Jan, Mar 06 '16 at 17:02
@Jan No, there is no any errors, inserting only first row values. Yes, there is Id - auto increment. — Infinity, Mar 06 '16 at 17:05
add to the Php script (error_log("Running");) and check the apache error log file to see how may times you see (Running) in the log — Tasos, Mar 06 '16 at 17:10
@Tasos I've tried to use `error_log("Running");`, but nothing happens (nothing printing) — Infinity, Mar 06 '16 at 17:33
because you may be looking at the wrong log -- what are you running, WAMP? — Tasos, Mar 06 '16 at 17:59
and check if you enabled error log for php -- http://stackoverflow.com/questions/9230984/error-logging-with-wamp-server-in-php — Tasos, Mar 06 '16 at 18:08

Hiren patel · Answer 1 · 2016-03-17T17:35:56.590

You need to create forms with this format

<form action="insert.php" method="post">  
<ul>
    <li>
        <input name="CertificateType[0]" type="hidden">
        <input name="CertificateType[0]['STCWCode']" type="text">
        <input name="CertificateType[0]['CertNo']" type="text">
        <input name="CertificateType[0]['PlaceofIssueCert']" type="text">
        <input name="CertificateType[0]['FromCert']" type="date">
        <input name="CertificateType[0]['ToCert']" type="date">
    </li>
    <li>
        <input name="CertificateType[1]" type="hidden">
        <input name="CertificateType[1]['STCWCode']" type="text">
        <input name="CertificateType[1]['CertNo']" type="text">
        <input name="CertificateType[1]['PlaceofIssueCert']" type="text">
        <input name="CertificateType[1]['FromCert']" type="date">
        <input name="CertificateType[1]['ToCert']" type="date">
    </li>
    ...
</ul>
</form>

When you submit form with this format , you will receive Numeric Key array in $_POST['CertificateType'] in insert.php

array(
  "0" => array(
           "STCWCode" => "somevalue",
           "CertNo" => "somevalue",
           "PlaceofIssueCert" => "somevalue",
           "FromCert" => "somevalue",
           "ToCert" => "somevalue",  
         ),
  "1" => array(
           "STCWCode" => "somevalue",
           "CertNo" => "somevalue",
           "PlaceofIssueCert" => "somevalue",
           "FromCert" => "somevalue",
           "ToCert" => "somevalue",  
         ),
   . . .
)

Here each index will represent one row.This can be retrived using foreach loop as below :

Update your php code as

foreach($_POST['CertificateType'] as $val){ 

$CertificateType = $val;

$CertificateType = $val; 
$STCWCode        = $val['STCWCode'];            
$CertNo          = $val['CertNo'];          
$FromCert        = $val['FromCert'];            
$ToCert          = $val['ToCert'];  

$CertificateType = mysqli_real_escape_string($link, $CertificateType);  
$STCWCode        = mysqli_real_escape_string($link, $STCWCode);                 
$CertNo          = mysqli_real_escape_string($link, $CertNo);           
$FromCert        = mysqli_real_escape_string($link, $FromCert);                 
$ToCert          = mysqli_real_escape_string($link, $ToCert);      

$sql3 = "INSERT INTO Tbl (
    CertificateType     
    ,UserId    
    ,STCWCode               
    ,CertNo                 
    ,FromCert               
    ,ToCert
    ,DateCreated
) VALUES (
    '$CertificateType',
    '$UserID',  
    '$STCWCode',            
    '$CertNo',          
    '$FromCert',            
    '$ToCert',
    now())";
if(mysqli_query($link, $sql3)){
    echo "Resume created successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}

This is the best answer in terms of technical code but you should explain why they should change it to this format i understand the OP might not. — Barkermn01, Mar 17 '16 at 13:00
@Hirenpatel thank you for your answer, but there could be added rows like 100 or more, so I need repeat It ``, `` up to 100 times? And what will happen If user will add 101 row? It will crash... There is no any dynamic way to achieve It or I misunderstood something? — Infinity, Mar 18 '16 at 13:03
you need to create this type of form using the jquery code you are using. you dont need to create every `` manually . you can modify your jquery to generate form in this format. If you add 100 rows ,On server side you will get one array containing 100 rows (index from 0 to 99). you can traverse it using foreach loop. As here it will be numeric key array , so it will make it easy to loop through. How can you say , it will crash ? @Infinity — Hiren patel, Mar 18 '16 at 13:14
In the Question itself , your approach is to to get multiple `CertificateType` in post request. Using your approach , Number of time your foreach loop traverse , it will hit database. I would suggest , not to use insert query in forloop. Instead of , Use PDO http://www.w3schools.com/php/php_mysql_insert_multiple.asp — Hiren patel, Mar 18 '16 at 13:24

itzmukeshy7 · Accepted Answer · 2016-03-18T13:28:39.790

Use your fiddle JavaScript and add certificate type in hidden field for every row; and name it RowCertificateType same as other fields for row;

<input type="hidden" name="RowCertificateType[]" value="" />
<!-- set value of this field same as you are showing in li as label for this row; -->

then in your php script use as follows:

foreach($_POST['RowCertificateType'] as $key=> $val){

  $CertificateType = $val;

  $STCWCode = $_POST['STCWCode'][$key];
  $CertNo = $_POST['CertNo'][$key];
  $FromCert = $_POST['FromCert'][$key];
  $ToCert = $_POST['ToCert'][$key];

  $CertificateType = mysqli_real_escape_string($link, $CertificateType);
  $STCWCode = mysqli_real_escape_string($link, $STCWCode);
  $CertNo = mysqli_real_escape_string($link, $CertNo);
  $FromCert = mysqli_real_escape_string($link, $FromCert);
  $ToCert = mysqli_real_escape_string($link, $ToCert);

  $sql3 = "INSERT INTO Tbl (
        CertificateType     
        ,UserId    
        ,STCWCode               
        ,CertNo                 
        ,FromCert               
        ,ToCert
        ,DateCreated
    ) VALUES (
        '$CertificateType',
        '$UserID',  
        '$STCWCode',            
        '$CertNo',          
        '$FromCert',            
        '$ToCert',
        now())";
  if(mysqli_query($link, $sql3)){
    echo "Resume created successfully.";
  }else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  }
}

:)

Thank you for your answer, but with my poor knowledge I can't understand what exactly should I do in *HTML* side, could you provide more information, please? — Infinity, Mar 18 '16 at 13:09
[jsfiddle with js changes](https://jsfiddle.net/itzmukeshy7/swz55vth/) anything else? — itzmukeshy7, Mar 18 '16 at 13:23
Not really understood how, but It worked!! Thank you so much! — Infinity, Mar 20 '16 at 10:55

Nana Partykar · Answer 3 · 2016-03-10T15:54:45.327

See, what i found is:

First, Replace CertificateType[] with CertificateType in <select id='options' name="CertificateType[]" class="field-style div-format align-left"> Because, this dropdown is not multiple. On based upon onchange functionality only you are getiing STCWCode, CertNo, FromCert & ToCert values, which are multiple (Not CertificateType dropdown). What you can do is: you can append one hidden text namely CertificateTypeHidden[] in which value of CertificateType dropdown will get inserted.
As i pointed in Ist Point. CertificateType is not multiple. So, it's considering only one value. What you can do is : As i wrote above, instead of CertificateType, you can use CertificateTypeHidden[] Like foreach($_POST['CertificateTypeHidden'] as $key => $val){.

After Changes, code should look like this way. (If you follow above points)

<?php
foreach($_POST['CertificateTypeHidden'] as $key => $val){ 
  $CertificateType = $_POST['CertificateTypeHidden'][$key]; 
  $STCWCode        = $_POST['STCWCode'][$key];  
  $CertNo          = $_POST['CertNo'][$key];   
  $FromCert        = $_POST['FromCert'][$key];   
  $ToCert          = $_POST['ToCert'][$key];  
    .
    .
    .
}
?>

For my testing purpose. I removed foreach($_POST['CertificateType'] as $key => $val){ with foreach($_POST['STCWCode'] as $key => $val){. All appended multiple textbox values coming correctly.

<?php
foreach($_POST['STCWCode'] as $key => $val){ 
  $CertificateType = $_POST['CertificateTypeHidden'][$key]; 
  $STCWCode        = $_POST['STCWCode'][$key];  
  $CertNo          = $_POST['CertNo'][$key];   
  $FromCert        = $_POST['FromCert'][$key];   
  $ToCert          = $_POST['ToCert'][$key];  
    .
    .
    .
}
?>

This is the only issue. All well. Just append one hidden field. (<input type='hidden' name='CertificateTypeHidden[]'>). In which values of selected dropdown come and sit in value attribute of this hidden field. In submit page or insert.php page, do foreach using CertificateTypeHidden.

Go through it. All the best.

Thank you for you answer, It looks like detailed, but still I can't achieve correctly with HTML side, could you show me how HTML side should look like? — Infinity, Mar 18 '16 at 13:10
See, frankly speaking. I tried your code in my system & I was literally trying to add one more textbox for certificate type. But, i was unable to understand jsfiddle code which you provided from your end. Since, this code is of yours, so it will be easy for you. Just be calm and do what i say. As soon you select CertificateType dropdown. New row comes with textbox. So, you just need to add one hidden field namely `CertificateTypeHidden` where that selected dropdown value will come & sit. And, nothing to do more. After that my code will work. I'm 100% sure. *If any doubt, Feel Free To Ask* @Inf — Nana Partykar, Mar 18 '16 at 13:38

score 2 · Answer 4 · answered Mar 10 '16 at 14:38

2

you loop over $_POST['CertificateType'], which (with your configuration) will only contain one value (no multiselect on the select field)

answered Mar 10 '16 at 14:38

Christian Weber

21
5

score 2 · Answer 5 · answered Mar 10 '16 at 15:02

You are using whole wrong loop. Instead you need to run loop over for example STCWCode POST variable.

But yet I would suggest you to change javascript code, and make input names something as this row[0][STCWCode], row[0][CertNo] and so on where row[0] is for first row and row[1] will be for second row and so on. Then you can run loop with $_POST['row']

Mauricio Florez · Answer 6 · 2016-03-12T21:11:00.760

You need create a hidden input with selected value for certificate, the dropdown is only to create the new row but you need create a new input with Array form, maybe change the dropdown name for example CertificateType[] => CertificateTypeList and the new hidden input name with CertificateType[]

UL is not send with the forms only input types.

<select id='options' name="CertificateList">
  <option selected disabled value="0">Select certificates</option>
  <option value="1">One</option>
  <option value="2">Two</option>
  <option value="3">Three</option>  
</select>

Create the form tag just once and the inputs for each repetition

<form action="insert.php" method="post">  
<ul>
    <li>
        <input name="CertificateType[]" type="hidden">
        <input name="STCWCode[]" type="text">
        <input name="CertNo[]" type="text">
        <input name="PlaceofIssueCert[]" type="text">
        <input name="FromCert[]" type="date">
        <input name="ToCert[]" type="date">
    </li>
    <li>
        <input name="CertificateType[]" type="hidden">
        <input name="STCWCode[]" type="text">
        <input name="CertNo[]" type="text">
        <input name="PlaceofIssueCert[]" type="text">
        <input name="FromCert[]" type="date">
        <input name="ToCert[]" type="date">
    </li>
    ...
</ul>
</form>

score 1 · Answer 7 · answered Mar 06 '16 at 17:07

1

I haven't looked at everything yet but your input fields all seem to have the same name in the fiddle.

The first input of all the rows seem to be "name="STCWCode[]". Have you looked at your $_POST and verified that you can see all your input or if there is just input from the first row? I think the names might be an issue.

answered Mar 06 '16 at 17:07

Michel

21
3

I don't think that's problem with name, here is used arrays, as I know It should work.... – Infinity Mar 06 '16 at 17:34

Remya R · Answer 8 · 2016-03-17T12:32:52.983

you are trying to insert whole array in a single code. First take the count. And then fetch the contents from array and using a for loop insert it into database for example if my name for the textbox is cellname[]

then for insert this should be done

 $itemCount = count($_POST['cellname']);
 $info=$_POST['cellname'];
 for($i=0;$i<$itemCount;$i++) {
   $query = "INSERT INTO item (item_name) VALUES('$info[$i]') ";
  //rest of the queries
 }

`

Insert to database multiple options from HTML form via PHP

8 Answers8