Inserting an array into database

Question

Does anyone have best practices when inserting an array into a database? I've used foreach loops with success in the past, as well as various array functions. Is there a better way?

Here is a sample code:

public function InsertRequests($id) {

$db = Database::getHandler();

$selected_requests = $this->GetRequests($this->allrequests);

  foreach($selected_requests as $requestid) {

$sql = "INSERT INTO requests (userid,requestid,date) VALUES (?,?,NOW())"; 
        $stmt = $db->prepare($sql);
        $stmt->bindParam(1,$id,PDO::PARAM_STR);
        $stmt->bindParam(2,$requestid,PDO::PARAM_STR);
        $stmt->execute();

    }

    return true;
}

}

a best practice would to use an ORM or any CRUD abstraction layer. and for performance check batch inserting. — Karoly Horvath
– Karoly Horvath, Commented Mar 20, 2012 at 20:07
@j08691 Serializing/Deserializing is expensive and messy. Store each value in a separate row in the respective table. — stan
– stan, Commented Mar 20, 2012 at 20:13
Executing multiple INSERTs at once by separating them with a semicolon increases the speed of the total INSERT operation by a lot, so I'd go for that in this case. — Philipp
– Philipp, Commented Mar 20, 2012 at 20:36
Move the prepare() out of the loop, you don't need to prepare a new stmt on each iteration, you only need to bind the params and execute. — MrCode
– MrCode, Commented Mar 20, 2012 at 20:46

edigu · Accepted Answer · 2012-03-23 20:04:46Z

1

You can insert multiple records with a single query like this:

INSERT INTO TABLENAME ( fieldOne, fieldTwo, fieldThree ) VALUES 
( 'Jack', 'Nickholson', 'M' ), 
( 'Audrey', 'Hepburn', 'F' ),
( 'Salvador', 'Dali', 'M' )
...

So, prepare your INSERT syntax iterating over your data array in a regular loop or using custom query builder than execute it once. Better practice is using an ORM library like @Karoly's said.

answered Mar 23, 2012 at 20:04

edigu

10.1k5 gold badges62 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

Inserting an array into database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related