Insert Multiple Rows into MySQL

[skinbug]

Hi, I am posting in PHP forum as I believe it is a PHP problem. I have seen examples of inserting multiple rows and it seems there are generally 2 options...to use multiple insert commands or to 'concatonate' the query. I have tried these options but with no success.

So I have a form which I will provide snippets of but generally it is like this...one project field, many (up to ten) task fields. For the task fields, I want them to go into their own row. I don't think an array is right - I am already using arrays to store checkbox data, but maybe I'm wrong on that one, I thought multiple rows is more appropriate. So the code...

project-new.php is the form

<form action="process.php" method="post">
   <ul>
   <li><label>Project Title:</label>
   // the form object here is used to store data if incorrect values are entered
   // and for error messages
   <input type="text" name="title" value="<?php echo $form->value("title"); ?>" /><?php echo $form->error("title"); ?></li>
   
   <li><label>Task:</label>
   <input type="text" name="task" value="<?php echo $form->value("task"); ?>" /><?php echo $form->error("task"); ?></li>
   
   <li><label>Task:</label>
   <input type="text" name="task" value="<?php echo $form->value("task"); ?>" /><?php echo $form->error("task"); ?></li>
   </ul>

   <input type="hidden" name="subproject" value="1" />
   <input type="submit" value="Submit Project" />
</form>

The two task fields are called the same thing as they need to get posted in process.php (is this where the error is?)

This form goes to process.php

if(isset($_POST['subproject'])) {
   $this->procLogin();
}

function procProject() {
   global $session, $form;

   $retval = $session->newProject($_POST['title'], $_POST['task']);
}

// further code which deals with the retvals...

This then goes to session.php for validation which I have left out for this example as I don't think it is where the problem is, but assuming the user has passed the validation checkes, their details go to database.php for the INSERT command

function addNewProject($projecttitle, $task) {
   $q = INSERT INTO projects VALUES ('$projecttitle', '$task');

   return mysql_query($q, $this->connection);
}

The return vaules go back to process.php where it determines a success/failed insert attempt and then referrs the user to the appropriate location.

So i have tried to only include code which I see as relevent and only provided a snippet. If any more is required I can post more.

As far as I can tell, it comes down to how the values are posted out. I tried a 'concatonating' the INSERT but it would insert the data into the wrong fields, fail validation and fail overall.

I tried duplicate inserts, ie $q = INSERT... and $q1 = INSERT... but that just inserted the data in task 2, not both of them.

Any help is greatly appreciated!!

[diafol]

I'm unsure if I understand you correctly, but INSERT allows multiple rows to be added to your DB:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

This is straight from the MySQL manual.

As I see it, you need to pull the $_POST variables from your form, sanitize them with something like mysql_real_escape_string() while giving them an array item.

//LATER
Having re-read your post, you seem to have repeat form fields with no unique or array names. They will overwrite each other by the time the $_POST variable arrives. Do this:

<input type="text" name="task[]" value="..." />

This will turn your $_POST variable into an array of task values.

Your form handler with then process this via a loop (while or foreach - whichever you prefer) which can build an SQL snippet to add to your main statement.

...
$task = $_POST['task'];
$projecttitle = $_POST['title'];
...
addNewProject($projecttitle,$task);
...
function addNewProject($projecttitle, $task) {
   foreach($task as $t){
      $snippet .= ",('" . mysql_real_escape_string($projecttitle) . "," . mysql_real_escape_string($t) . ")"; 
   }
   $snippet = substr($snippet,1); //just takes off the leading comma
   $q = "INSERT INTO projects (project_title, task_title)  VALUES $snippet";
   return mysql_query($q, $this->connection);
}
...

However, this is wasteful and not 'normalized'. You should have a projects table and a tasks table:

projects
======
project_id (PK, autoincrement, int)
project_title (varchar)

tasks
====
task_id (PK, autoincrement)
project_id (FK, int)
task_title (varchar)

You should add a project to the projects table. Use the mysql_insert_id() to get the project_id so it can be used in the sql loop:

...
$task = $_POST['task'];
$projecttitle = $_POST['title'];
...(add the project to the DB)...
$projectid = mysql_insert_id();
...
addNewProject($projectid,$task);
...
function addNewProject($projectid, $task) {
   foreach($task as $t){
      $snippet .= ",('" . $projectid . "," . mysql_real_escape_string($t) . ")"; 
   }
   $snippet = substr($snippet,1); //just takes off the leading comma
   $q = "INSERT INTO tasks (project_id, task_title) VALUES $snippet";
   return mysql_query($q, $this->connection);
}
...

That will give you a better relational model.

[skinbug]

Hi, sorry it has taken so long to get back on this, but have been busy with other stuff...still trying to get this working though. Firstly, I do agree with what you say about having a better normalized db table, if time permits, I will do this but for the time being, I'm sticking with how it is.

I can now insert multiple rows, but it is duplicating the data...either the first row or the last row is duplicated, depending on how I code it. I can't seem to distinguish between the data in the form...the code so far...

HTML form:

// This is just a small section of the overall form, but provides enough to demonstrate two db table row entries.
<form action="process.php" method="post">
   <ul>
   <li><label>Project Title:</label>
   <input type="text" name="title01" value="<?php echo $form->value("title01"); ?>" /><?php echo $form->error("title01"); ?></li>

   <li><label>Task 1:</label>
   <input type="text" name="task01" value="<?php echo $form->value("task01"); ?>" /><?php echo $form->error("task01"); ?></li>


   <li><label>Project Title:</label>
   <input type="text" name="title02" value="<?php echo $form->value("title02"); ?>" /><?php echo $form->error("title02"); ?></li>

   <li><label>Task 2:</label>
   <input type="text" name="task02" value="<?php echo $form->value("task02"); ?>" /><?php echo $form->error("task02"); ?></li>
</ul>

<input type="hidden" name="subproject" value="1" />
<input type="submit" value="Submit Project" />

</form>

This is sent to process.php and is looking for subproject:

if(isset($_POST['subproject'])) {
   $this->procProject();
} 

function procProject() {
   global $session, $form; 

   $retval = $session->newProject($_POST['title01'], $_POST['task01'], $_POST['title02'], $_POST['task02']);

   // New project successful
   if($retval == 0) {
      $_SESSION['newproject'] = true;
      header("Location: ".$session->referrer);
   }
   else if($retval == 1) { // Error found with form
      $_SESSION['value_array'] = $_POST;
      $_SESSION['error_array'] = $form->getErrorArray();
      header("Location: ".$session->referrer);
   }
   else if($retval == 2) { // Registration attempt failed
      $_SESSION['newproject'] = false;
      header("Location: ".$session->referrer);
   }
}

So the $retval goes to the session object and looks for newProject to do validation:

function newProject($subtitle01, $subtask01, $subtitle02, $subtask02)
   global $database, $form;

   // validation takes place to strip slashes etc
   // have not included since it should not prevent data duplication problem

   if($form->num_errors > 0) {
      return 1; // Errors with form
   }
   else { // No errors, add the new project to the projects table
      if($database->addNewProject($subtitle01, $subtask01, $subtitle02, $subtask02)
         return 0; // New project added succesfully
      }
      else {
         return 2; // New project attempt failed
      }
   }
}

So, assuming there are no errors with the form or fundanmental issues, the data calls the database object and looks for addNewProject:

function addNewProject($title, $task) {
   
   if(get_magic_quotes_gpc()) {
      $title = stripslashes($title);
      $task  = stripslashes($task);
   }
   $title = mysql_real_escape_string($title, $this->connection);
   $task  = mysql_real_escape_string($task, $this->connection);

   // TBL_PROJECTS is defined on another page...is the table name
   $q = "INSERT INTO ".TBL_PROJECTS." VALUES
   ('$title', '$task'),
   ('$title', '$task')";

   return mysql_query($q, $this->connection);
}

It is the session object which then returns the values back to process.php, depending. 1 means you have failed validation, so re-enter the data; 2 is an overall failure (never experienced!); 0 means the data was successfully entered into the form, then successfully inserted to the db table.

So, currently, I am now able to insert multiple rows, but the data is being duplicated...title01 and task01 is being entered twice, while title02 and task02 are not being entered. I am convinced that the error(s) are in 2 possible places...POSTing the values in process.php and/or INSERTing the values in addNewPeoject function.

If anyone can see any glaring errors, your help is much appreciated. What this also leads to is how would the data get handled if the user only entered one title and task? Would it insert two rows with one holding a set of NULL values. I imagine I would only need to POST values if they exist. I have experimented with ISSET but have come up short...help?!

[Rimbhas]

<?php
$a=$_POST["no_of_members"];
$x=0;
while($a!=0)
{
$x+=1;
?>
<form action='process.php' method='POST'>
Member<?php echo $x; ?>'s Name:<input type='text' name='mname'> </br>
Age:<input type='text' name='age'> </br>
Designation:<input type='text' name='designation'></br></br>

<?php
$a-=1;
}
?>
<input type='submit' name='submit' value='Proceed'>
</form>

The above form will split into multiple field depending on no_of_members,so
What will be the code for inserting into DB...if (no_of_members>1)
HELP PLZ!!

[Stefano Mtangoo]

I'm unsure if I understand you correctly, but INSERT allows multiple rows to be added to your DB:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

This is straight from the MySQL manual.

As I see it, you need to pull the $_POST variables from your form, sanitize them with something like mysql_real_escape_string() while giving them an array item.

//LATER
Having re-read your post, you seem to have repeat form fields with no unique or array names. They will overwrite each other by the time the $_POST variable arrives. Do this:

<input type="text" name="task[]" value="..." />

This will turn your $_POST variable into an array of task values.

Your form handler with then process this via a loop (while or foreach - whichever you prefer) which can build an SQL snippet to add to your main statement.

...
$task = $_POST['task'];
$projecttitle = $_POST['title'];
...
addNewProject($projecttitle,$task);
...
function addNewProject($projecttitle, $task) {
   foreach($task as $t){
      $snippet .= ",('" . mysql_real_escape_string($projecttitle) . "," . mysql_real_escape_string($t) . ")"; 
   }
   $snippet = substr($snippet,1); //just takes off the leading comma
   $q = "INSERT INTO projects (project_title, task_title)  VALUES $snippet";
   return mysql_query($q, $this->connection);
}
...

However, this is wasteful and not 'normalized'. You should have a projects table and a tasks table:

projects
======
project_id (PK, autoincrement, int)
project_title (varchar)

tasks
====
task_id (PK, autoincrement)
project_id (FK, int)
task_title (varchar)

You should add a project to the projects table. Use the mysql_insert_id() to get the project_id so it can be used in the sql loop:

...
$task = $_POST['task'];
$projecttitle = $_POST['title'];
...(add the project to the DB)...
$projectid = mysql_insert_id();
...
addNewProject($projectid,$task);
...
function addNewProject($projectid, $task) {
   foreach($task as $t){
      $snippet .= ",('" . $projectid . "," . mysql_real_escape_string($t) . ")"; 
   }
   $snippet = substr($snippet,1); //just takes off the leading comma
   $q = "INSERT INTO tasks (project_id, task_title) VALUES $snippet";
   return mysql_query($q, $this->connection);
}
...

That will give you a better relational model.

Also PDO will enable you to bind different values and execute them on the same PDOStatement object. You just bind it to new value (looping array?) and execute untill all values are finished!

EDIT
I found this examples here:
http://stackoverflow.com/questions/5677901/multiple-inserts-into-3-tables-with-pdo

$sql = "INSERT INTO dates (date_one, date_two) VALUES(:one, :two)";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

for($i = 0; $i < count($_POST['date_range']); $i++) {
   $sth->execute(array(':one' => $_POST['date_range'][$i], ':two' => '$_POST['date_range_end'][$i]));
}