CODEWITHSUNDEEP
phphtmlsqlformspdo
user4032063
user4032063

Reputation: 37

Insert multiple rows without inserting blanks sql php

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));

Upvotes: 1

Views: 531

Answers (1)

CD001
CD001

Reputation: 8482

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();

Upvotes: 3

Related Questions