How to insert multiple rows in a table at a time

Question

I have a MySQL database and form in php.

I have successfully updated into MySQL database when a user submit his details.

Now i have a question.

I have 5 forms with same fields in the same web page. Now a user submit all 5 forms and click submit button, then save that details into MySQL database.

I know the code if a user submit single form how to save into database. But i need submit multiple forms with the same fields how to save in database.

My code is looking like this if a user submit single form.

<?
if( $_POST )
{
  $con =     mysql_connect("xx","xx","xx");
    if (!$con)
  {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("xx", $con);
$users_id = $_POST['id'];
 $users_name = $_POST['name'];
 $users_Telephone = $_POST['Telephone'];
 $users_E_mail = $_POST['E_mail'];
 $users_country = $_POST['country'];
 $users_visa_categeory = $_POST['visa_categeory'];
 $users_other_category = $_POST['other_category'];
  $users_passport_no = $_POST['passport_no'];
  $users_remarks = $_POST['remarks'];
 $users_date = $_POST['date'];
  $query = "
  INSERT INTO `xx`.`xx` (
  `id`, 
  `name`, 
  `Telephone`, 
  `E_mail`, 
  `country`,
    `visa_categeory`, `other_category`,  `passport_no`,   `remarks`,  `date`   
        )
        VALUES ('$users_id', '$users_name', '$users_Telephone', '$users_E_mail',
        '$users_country',  '$users_visa_categeory', '$users_other_category',     '$users_passport_no', '$users_remarks', '$users_date'
          );";
  mysql_query($query);
  echo"<br /><br />";
  echo"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n    bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
  printf("    <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs    p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&    nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Your Reference id is %d\n (Please note this reference id for future)<p>",     mysql_insert_id());

  mysql_close($con);
}
?>
<table style="
background-color:#999;
color:#000;
text-align: left; width:500px; margin-left:250px; margin-top:20px;" border="1">
 <tr>
 <td style="color:#fff; text-indent:10px">Your Name:</td>
   <td style="color:#00F; text-indent:10px"><?php echo $_POST["name"]; ?></td>
 </tr>
    <tr> <td style="color:#fff; text-indent:10px">Telephone:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["Telephone"]; ?> </td>
    <tr> <td style="color:#fff; text-indent:10px">E_mail:</td><td style="color:#00F;    text-indent:10px"><?php echo $_POST["E_mail"]; ?></td> </tr>
    <tr> <td style="color:#fff; text-indent:10px">Country:</td><td style="color:#00F;    text-indent:10px"><?php echo $_POST["country"]; ?> </td> </tr>
    <tr> <td style="color:#fff; text-indent:10px">Visa_Categeory:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["visa_categeory"]; ?></td> </tr>
    <tr> <td style="color:#fff; text-indent:10px">Other_Category:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["other_category"]; ?> </td> </tr>
    <tr> <td style="color:#fff; text-indent:10px">Passport_No:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["passport_no"]; ?></td> </tr>
    <tr> <td style="color:#fff; text-indent:10px" valign="top">Remarks:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["remarks"]; ?> </td> </tr>
    <tr> <td style="color:#fff; text-indent:10px" valign="top">Date:</td><td    style="color:#00F; text-indent:10px"><?php echo $_POST["date"]; ?> </td> </tr>

 </tr>

 </table>

Why do you want to submit multiple forms with the same information? — Alvaro, Jul 02 '13 at 08:59
Enumerate each instance (e.g. `firstname[]`) and then use a `for` loop to iterate through the posted values — Strawberry, Jul 02 '13 at 08:59
If they are separate forms then you will need to use Javascript to submit all the forms via Ajax. Otherwise put all fields on the same form with different field names. — karmafunk, Jul 02 '13 at 08:59
@Strawberry but no more than one form can be submitted at once... — Alvaro, Jul 02 '13 at 08:59
It would be more use if you show the input page rather than the update and display page. — RiggsFolly, Jul 02 '13 at 09:01
@Steve I am saving some information(Not all) with different id. For Ex: name,telephone,email are common, but some fields are different. — user2541451, Jul 02 '13 at 09:02
@Steve I think that in that sense, it's really just one form - but I could be mistaken. — Strawberry, Jul 02 '13 at 09:02
@BurhanKhalid How to save multiple forms in a database on one click — user2541451, Jul 02 '13 at 09:03
hello SQL injection! http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php?rq=1 — Carlos Campderrós, Jul 02 '13 at 09:06
@user2541451 you still looking in the wrong way... You should not try to post the same information 5 times but instead just calling a loop with a different ID each time... — Alvaro, Jul 02 '13 at 09:08

Sergi Juanola · Answer 1 · 2013-07-02T09:19:36.180

You should be naming each "form" inputs with special names. One would be appending a number to each input, so that it detects from which form you are talking about.

Then, just concatenate multiple VALUES using commas:

<?
// You may know the form count?
define('NUM_FORMS', 5);
if( $_POST )
{
  $con =     mysql_connect("xx","xx","xx");
  if (!$con)
  {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("xx", $con);

$userBlocks = array();

for ($i=0; $i < NUM_FORMS; $i++) { 
  $users_id = $_POST['id'.$i];
  $users_name = $_POST['name'.$i];
  $users_Telephone = $_POST['Telephone'.$i];
  $users_E_mail = $_POST['E_mail'.$i];
  $users_country = $_POST['country'.$i];
  $users_visa_categeory = $_POST['visa_categeory'.$i];
  $users_other_category = $_POST['other_category'.$i];
  $users_passport_no = $_POST['passport_no'.$i];
  $users_remarks = $_POST['remarks'.$i];
  $users_date = $_POST['date'.$i];

  // Add each 'user query' into an array
  $userBlocks[] = "('$users_id', '$users_name', '$users_Telephone', '$users_E_mail',
  '$users_country',  '$users_visa_categeory', '$users_other_category',     '$users_passport_no', '$users_remarks', '$users_date'
  )";
}

if(!empty($userBlocks))
{
  $query = "
  INSERT INTO `xx`.`xx` (
    `id`, 
    `name`, 
    `Telephone`, 
    `E_mail`, 
    `country`,
    `visa_categeory`, `other_category`,  `passport_no`,   `remarks`,  `date`   
    )
  VALUES ".implode(", ", $userBlocks).";";
  // Then implode the queries split by commas
  mysql_query($query);
}

yet he can store the information in the post as "different forms" just using one. It all depends on the variable names. If you see, I haven't told anything about multiple forms, but multiple values — Sergi Juanola, Jul 02 '13 at 09:12

score 0 · Answer 2 · answered Jul 02 '13 at 09:03

0

You should not be submitting multiple forms at the same time. The only way I can think about doing so is using AJAX and Javascript and is not a good solution anyway.

You should look for another way to accomplish what you want. It seems you are in the wrong way.

You can insert multiple rows in a the table by just calling the insert statement more than once. You can do it in a loop, for example:

//getting the connection...
$conn = $this->_db->getConn(); 

for($a = 0; $a<5; $a++){
  $query = $conn->prepare('Insert into reservation (name, idUser, text) values ('a', '132', 'cccc')');
  $query->execute();
}

answered Jul 02 '13 at 09:03

Alvaro

40,778
30
164
336

You're expecting he knows about PDO and his system is ready to use such. Despite he would be using this, it is not an excuse to skip his example with a copy-paste of your own. PS: You are preparing a query. If done multiple times, you could be preparing it once and passing the parameters in the `execute()` – Sergi Juanola Jul 02 '13 at 09:14
This was just an example about how to accomplish it. (as i pointed out). I prefer not to encourage to use `mysql_query` as it is not secure anymore, plus, his query is too big to illustrate the procedure in a simple way. PS: that was a mistake :) – Alvaro Jul 02 '13 at 09:23
`mysql_query` has never been secure, just as a reminder. But yes, I see your point. Anyway, he could escape every input. By the way, check my answer and my commend again. – Sergi Juanola Jul 02 '13 at 09:31

score -1 · Answer 3 · answered Jul 02 '13 at 09:05

You can only ever submit a single form at a time from a page.

You will need to come up with a naming scheme for your form elements, so that when they are submitted you can pull out the individual form elements.

eg. Give HTML Like this

<form method=post>
<input name="formA_Name">
<input name="formA_Passport">
....
<input name="formB_Name">
<input name="formB_Passport">
....
<input name="formB_Name">
<input name="formB_Passport">
....
<input type=submit>
</form>

You submit to a php page like below and pull out what you want.

//Pull details out into seperate arrays
foreach ($_POST as $Key = > $Value)
{
  list($Form, $KeyName) = explode('_', $Key);
  $forms[$Form][$KeyName] = $Value; 

}

//This creates arrays like this
// ['FormA']['FormA_Name'] = 'Name Submitted'
// ['FormB']['FormB_Name'] = 'Name Submitted'

You can then use this structure to submit to your database.

How to insert multiple rows in a table at a time

3 Answers3