Insert multiple rows without inserting blanks sql php

Question

I have three input boxes that I would like to repeat ten times in one form/table called volunteer_id, position_id, and memo. Without having to write 10 sql queries I would like to reuse the same statement or loop it (which I know is bad) so it reads each of the 10 sets and inserts a new row per set. I would also like to allow the user to input between 1 and 10 sets so if they only enter 2 sets, for instance, it doesn't insert another 8 blank rows but still submits the 2 they entered. I apologize if this is inarticulate, I've been scratching my head on it for weeks now, looked everywhere, and finally broke down and posted here.

So, things I'm trying to accomplish: label each input so it can be reused 10 times submit an insert for up to 10 records, not blanks

Here is a snippet of my current code with most of the relevant information.

<INPUT TYPE="text" NAME="volunteer_id" SIZE="7" value="volunteer_id"> <br/>
<INPUT TYPE="text" NAME="position_id" SIZE="7" value="position_id"> <br />
<TEXTAREA name="memo" cols="45" rows="2">Memo</TEXTAREA> <br />

$volunteer_id = $_POST['volunteer_id'];
$memo = $_POST['memo'];
$position_id = $_POST['position_id'];

$query = "INSERT INTO work(volunteer_id, memo, position_id) VALUES (:volunteer_id, :memo, :position_id)";

$q = $dbo->prepare($query);
$q->execute(array('':volunteer_id'=>$volunteer_id, ':memo'=>$memo, ':position_id'=>$position_id));

CD001 · Accepted Answer · 2014-12-04 10:27:24Z

3

Firstly PHP doesn't deal brilliantly with GET/POST vars having the same name, each will overwrite the previous so you'll need something like:

<label>Volunteer: <input type="text" name="volunteer_id[0]" size="7" /></label>
<label>Position: <input type="text" name="position_id[0]" size="7" /></label>
<label>Memo: <textarea name="memo[0]" cols="45" rows="2"></textarea></label>

These should be indexed in the order received but to be on the safe side, in the code that generates your HTML you should add the index into the name (e.g. volunteer_id[0]) - empty <textarea> data doesn't get posted so you'll need to ensure it exists with the right index or you could end up with memos not lining up with volunteers/positions.

You can just loop a function to create your output; that will give you 3 arrays in $_POST when received by the PHP (I'm assuming your form method is "post"), like so:

//these will all be arrays
$aVolunteers = $_POST['volunteer_id'];
$aPositions = $_POST['position_id'];
$aMemos = $_POST['memo'];

Next, to build the query:

//how many volunteers are there
// - text input fields are always posted even when empty
//   so counting volunteers should tell us how many data sets we've got
$iNumSets = count($aVolunteers);

//begin the query
$sQuery = "INSERT INTO work(volunteer_id, position_id, memo) VALUES";

//create an array of parameters to bind
$aBoundParams = array();

//loop to the total number of data sets
for($i = 0; $i < $iNumSets; $i++) {

  //append the "values" to the query string
  $sQuery .= "(?, ?, ?), ";

  //add the values to the bound parameters array
  //  assumes the database fields are NULLable
  $aBoundParams[] = !empty($aVolunteers[$i]) ? $aVolunteers[$i] : null;
  $aBoundParams[] = !empty($aPositions[$i]) ? $aPositions[$i] : null;

  //this one is the most important one to check !empty()
  //as it's possible $aMemos[$i] doesn't exist
  $aBoundParams[] = !empty($aMemos[$i]) ? $aMemos[$i] : null;
}

//trim the trailing ", "
$sQuery = substr($sQuery, 0, -2);

If your data sets were posted 3 times you should now have a query that looks like:

INSERT INTO work(volunteer_id, position_id, memo) VALUES(?, ?, ?), (?, ?, ?), (?, ?, ?)

And an array, length 9, like this:

array(
  $aVolunteers[0],
  $aPositions[0],
  $aMemos[0],
  $aVolunteers[1],
  $aPositions[1],
  $aMemos[1],
  $aVolunteers[2],
  $aPositions[2],
  $aMemos[2]
)

You should now be able to prepare the query, bind the parameters/values and execute it.

//prepare the statement
$oStmt = $dbo->prepare($sQuery);

//bind the parameters to the statement
//  parameters need to be bound by reference - note &$vParamValue
foreach($aBoundParams as $iParamIndex => &$vParamValue) {

  $oStmt->bindParam(

            //bound params/values are 1 indexed rather than 0
            ($iParamIndex + 1), 

            //value to bind
            $vParamValue,

            //basic variable type prep - assuming you're using PDO
            (is_int($vParamValue) ? PDO::PARAM_INT : PDO::PARAM_STR)

            );
}

//execute the statement
$oStmt->execute();

edited Dec 4, 2014 at 10:27

answered Dec 3, 2014 at 23:16

CD001

8,5123 gold badges26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mystery Over a year ago

I believe name="foobar[]" also works for building up an array, avoiding explicit indexing.

user4032063 Over a year ago

Obviously I'm doing something wrong with my prepare and execute, I added your second and third code block to my code and added indexes to my volunteer_id[0] etc but am getting invalid parameter errors now.

$q = $dbo->prepare($sQuery); $q->execute(array(':volunteer_id'=>$aVolunteers,':memo'=>$aMemos, ':position_id'=>$aPositions));

CD001 Over a year ago

@user4032063 we're not using named parameters but indexed ones now - though it did occur to me this morning that indexed parameters start from 1 not 0 so I need to tweak the answer slightly... it was kinda late last night, from my part of the world, when I wrote this.

CD001 Over a year ago

@AndreaFaulds with the HTML inputs though you want explicit indexing - <textarea> data is not passed through in the POST if it's empty so you could get the situation here where, if there are 5 data sets coming in and the 4th memo is empty then the 5th memo will become the 4th memo, and the 5th memo will be empty (rather than the 4th) if you don't use explicit indexes ... if that makes sense :)

Collectives™ on Stack Overflow

Insert multiple rows without inserting blanks sql php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related