Multiple insert function with bindParam

[pandglobal]

I have been trying to insert multiple rows into the database with bindParam and not using loop in its execution, but it seems that is not working, i Keep getting duplicate primary key error.

TABLE STRUCTURE
id | name | comment | date

$data = array('orange', 'mango', 'apple', 'banana');

$count = count($data);
$mulvalue = array_fill(0, $count, '(?,?,?,?)');
$break = implode (',', $mulvalue);
$ins = $conn->prepare ("INSERT INTO table (id, name, comment, date) VALUES $break)";
$i = 1;
foreach($data as $da){
$id = rand(111111, 999999);
$name = $da;
$comment = 'test me';
$date = date('Y-m-d H:i:s');

$ins->bindParam ($i++, $id, PDO::PARAM_STR);
$ins->bindParam ($i++, $name, PDO::PARAM_STR);
$ins->bindParam ($i++, $comment, PDO::PARAM_STR);
$ins->bindParam ($i++, $date, PDO::PARAM_STR);
}

$ins->execute ();

That the code but is not working, also my primary key is the id column which must have one unique value.

[mabismad]

bindParam uses a reference to the variable, so using it inside of a loop ends up with last value the variable contains when ->execute is called.

BindValue would actually work, but avoid this problem completely by not using bindParam/bindValue and simply supply an array of values to the ->execute() call. Keep It Simple (KISS.)

Your id column should be an auto-increment primary index anyways and you should simply leave it out of the query.

[pandglobal]

@mabismad

so using it inside of a loop ends up with last value

you mean using it execute () outside of a foreach call?

[pandglobal]

@mabismad

Your id column should be an auto-increment

Noo my id is like an invoice number of upto 6unique digits including letters too, never wanted to use auto increment for id.

[SamA74]

@pandglobal

$id = rand(111111, 999999);

There is nothing to prevent duplicated IDs here. rand() may pick the same number again.

[pandglobal]

I know rand() isnt 100% unique but i don’t think rand() is the problem here because i have ran that code over and over again but is not working, atleast it will take some small chances for rand() to pick same number. my code is not entirely okay for now, not really a rand().

[droopsnoot]

To me, it should be laid out more like this:

$data = array('orange', 'mango', 'apple', 'banana');
$ins = $conn->prepare ("INSERT INTO table (id, name, comment, date) VALUES (?,?,?,?)";
$i = 1;
$ins->bindParam ($i++, $id, PDO::PARAM_STR);
$ins->bindParam ($i++, $name, PDO::PARAM_STR);
$ins->bindParam ($i++, $comment, PDO::PARAM_STR);
$ins->bindParam ($i++, $date, PDO::PARAM_STR);

foreach($data as $da){
$id = rand(111111, 999999);
$name = $da;
$comment = 'test me';
$date = date('Y-m-d H:i:s'); // this could be left out and the column set to default to "now"
$ins->execute ();
}

Prepare the query before the loop, bind the variables by name to the parameters before the loop, and then call execute() inside the loop for each set of values that you assign to the variables. As in the comment, if the date is always just going to be “now”, leave it out of the query and set the default for the column in your table definition.

Or as it’s PDO you could use an array for the parameters:

$data = array('orange', 'mango', 'apple', 'banana');
$ins = $conn->prepare ("INSERT INTO table (id, name, comment, date) VALUES (?,?,?,?)";

foreach($data as $da){
  $ins->execute (array(rand(111111,999999), $da, 'test me', date('Y-m-d H:i:s')));
}

[Drummin]

You might also want to just query the table for the last invoice number and increment it.

$sql_last_number = "SELECT MAX(id) as last_number FROM `table`";
$query_last_number = $conn->prepare($sql_last_number);
$query_last_number->execute();		 
$row_last_number = $query_last_number->fetch(PDO::FETCH_ASSOC);											  
$last_number = $row_last_number['last_number'];

Depending strictly how you want your 6 digit to start you can add leading zeros, i.e. 000001 OR you can create a start number of your liking, e.g. 100000 you can specify this default.

//Optional start number
//$last_number = (!empty($last_number) ? $last_number : 99999);// +1 will be 1000000 

//Digits for invoice number
$digits_invoice_no = (!empty($last_number) ? strlen($last_number) : 6);
//increment the last number
$new_i_no = $last_number + 1;  

$new_invoice_no = sprintf('%0'.$digits_invoice_no.'d', $new_i_no);

NOTE: this code would need to be inside your FOREACH $data loop before you execute the query.

$data = array('orange', 'mango', 'apple', 'banana');  

$sql = "INSERT INTO `table` (`id`, `name`, `comment`) VALUES(:id, :name, :comment)";
$query = $conn->prepare($sql);

$comment = 'test me';

foreach($data as $name){ 

	$sql_last_number = "SELECT MAX(id) as last_number FROM `table`";
	$query_last_number = $conn->prepare($sql_last_number);
	$query_last_number->execute();		 
	$row_last_number = $query_last_number->fetch(PDO::FETCH_ASSOC);											  
	$last_number = $row_last_number['last_number'];
	
	//Optional start number
	//$last_number = (!empty($last_number) ? $last_number : 99999);// +1 will be 1000000 
	
	//Digits for invoice number
	$digits_invoice_no = (!empty($last_number) ? strlen($last_number) : 6);
	//increment the last number
	$new_i_no = $last_number + 1;  
	
	$new_invoice_no = sprintf('%0'.$digits_invoice_no.'d', $new_i_no);
	
	$values = array(':id' => $new_invoice_no, ':name' => $name, ':comment' => $comment);

	$query->execute($values);
}

NOTE: The field date is datetime with defaul value of CURRENT_TIMESTAMP

[pandglobal]

@droopsnoot

and then call execute()

thanks alot but I want to know the effect of this code because all i tried to avoid is calling execute () inside a loop because i am thinking it will be kind of slow and the faster way is to only execute once.
The above code is it different from me running the entire code inside a loop because thats how i used to do it and it worked but just that if the array is over 1000 then it takes a little longer to finish executing.

foreach (){
prepare
bind
execute
}

Alll codes inside the foreach loop and it works but has a terrible effect on the server especially for large arrays.

[pandglobal]

@Drummin

$last_number = $row_last_number['

I love this method of using last number but will use it in something else, but first i want to try it on a table without primary key using auto increment, and if the codes inserts properly then i will start looking for ways to work on the duplicate keys

[mabismad]

Do NOT select the current maximum value and increment it. This is not concurrent safe (atomic.) When there are concurrent instances of your script, each instance will get the same starting value, increment it, and attempt to use it, producing duplicate values, which your database table design should produce a duplicate error for, requiring you to generate another number, and try to insert it, …

Generating your own number is even more of a problem when doing a multi-value insert, as the query will fail upon the first duplicate error. You must then determine which row(s) got inserted, generate a new number for the row that failed, insert it, then continue inserting the remaining row(s.) Do you really want to write the code to correctly do all of that?

[pandglobal]

@mabismad

Do you really want to write the code to correctly do all of that?

Noooo, i prefer to generate a random code in php before inserting, am not worried about the duplicate keys error because i know if i got it right there will be no such error because i have a way of generating unique id and i can extend it to 20 digits or 10digits to make sure the id remain unique, but my huge problem is i don’t want to call the execute function inside a loop, i want to bind and prepare the query statement inside the loop but i want to only call execute() once

[mabismad]

@pandglobal

but my huge problem is … i want to only call execute() once

My first reply in the thread stated how to do that. Code that implements those points -

<?php

// test data
$data = array('orange', 'mango', 'apple', 'banana');
$comment = 'test me';
$date = date('Y-m-d H:i:s');

// build sql query statement
$count = count($data);
$mulvalue = array_fill(0, $count, '(?,?,?)');
$break = implode (',', $mulvalue);
$sql = "INSERT INTO table (name, comment, date) VALUES $break";
$stmt = $conn->prepare($sql);

// build prepared query input parameters
$params = [];
foreach($data as $name)
{
	$params = array_merge($params,[$name,$comment,$date]);
}

// examine the query/params
echo $sql;
echo '<pre>'; print_r($params);

$stmt->execute($params);

[pandglobal]

@mabismad

$stmt->execute($params)

perfectly perfect! thanks alot this is exactly what I am looking for.

[droopsnoot]

I know you have a solution, but my point was that this isn’t the best way of doing it:

foreach (){
prepare
bind
execute
}

it would be better to do

prepare
bind // as long as you do not bind VALUE, only to the variable name
foreach (){
execute
}

[pandglobal]

thanks alot @droopsnoot but i don’t wish to call execute() inside any loop, i prefer prepare sql outside the loop, bind the variables inside a loop and then execute outside a loop, and which is exactly what @mabismad had already pointed out to me, so i modified it to meet my creteria and then ran the code and it worked perfectly.
The reason i chose to bind the variables inside the loop was because there are values contained in the array that can only be singled out through a foreach loop and therefore there is no way i can bind values without looping.
with this work flow.

prepare sql
foreach (){
bindParam
}
execute()

Thanks everyone you saved my countless hours of digging here and there without any success, i really appreciate your guides and thoughts

[droopsnoot]

@pandglobal

The reason i chose to bind the variables inside the loop was because there are values contained in the array that can only be singled out through a foreach loop and therefore there is no way i can bind values without looping.

That’s why I said you need to bind the variables and not bind the values. Using bindParam() before the loop means that you will use whatever value that variable contains when you call execute(). If you were to use bindValue(), you would need to call it inside the loop.

As I noted, I see that you have a solution that works for you, but it’s not always clear (despite the doc) about the distinction between bindParam and bindValue.

[pandglobal]

@droopsnoot

you were to use bindValue()

this is really educative, i never thought bindValue() exist all i know is bindParam() but however it worked for me.

$data = array('mango', 'pawpaw', 'orange');

$count = count($data);
$mulvalue = array_fill(0, $count, '(?,?,?,?)');
$break = implode (',', $mulvalue);

$add = $con->prepare("INSERT INTO table (id, name, comment, date) VALUES $break"):
$params = [];
foreach($data as $name){
$id = rand(111,999).rand(111,999);
$comment = 'test comment';
$date = date('Y-m-d H:i:s);
$params = array_merge($params,[$id,$name,$comment,$date]);
}
$add->execute ($params);

Thats exactly how i did mine and it worked, my id was never auto increment, to avoid repeat id i can make it a length of 12 plus mixed alphabets and is working for me just the way you explained it.

but am now curious to know if am to use bindValue() how will the code look like? just curious

[droopsnoot]

If you use bindValue, the basic structure would be as you had before:

prepare
foreach() {
  bindvalue
  execute
}

[pandglobal]

@droopsnoot

prepare
foreach() {
  bindvalue
  execute
}

nooooo i don’t like this, i prefer our new method now using bindParam