I have the following array which my PHP created based on user input…
array (size=4)
0 => string 'apples' (length=6)
1 => string 'oranges' (length=7)
2 => string 'bananas' (length=7)
3 => string 'coconuts' (length=8)Now I need to insert this data into my database.
Is there a streamlined way to do this all at once?
If I did it, I would create FOREACH loop and do 4 separate INSERT statements.
It seems like there is a better way…
INSERT INTO table_name (col_name) VALUES ('apples'),('oranges'),('bananas'),('coconuts');
This looks like an acceptable source:
A bigger question is, “Do I need to worry about doing this?”
This applies to a Bad Words function I wrote. In it, I not only replace any bad words, but I capture all bad words from the user’s post and place them into an array. From there, I want to permanently log them int he database, so I have a record of offenders and can take action on people that consistently have potty mouths! 
Hopefully my Bad Words function grows cobwebs. And when it is needed, I would hope that an offending post might only have a few curse words in it.
That beings aid, do I need to get fancy and do just one INSERT, or is a simple FOREACH loop with multiple INSERTs good enough?
(Note: The hope is that my client’s website becomes very popular - like SItePoint - and will have hundreds (or thousands) of people posting comments throughout the day!)
When possible a single trip to the database is the most scalable and optimized approach for data entry. Though to often than not it is not nearly as practical as ideal. However, in your case I wouldn’t see why not. Especially if you’re not using any type of abstraction layer that doesn’t support this feature.
So are you saying I should take the extra time to program a single INSERT?
If so, I guess I have to manually create the SQL-string in the format you mentioned above?
Since I use Prepared Statements exclusively - for security reasons - I’m not sure if this is even possible…
Yes and Yes
Yes, it is more than possible.
Prepared Statements were made for that scenario: create once, execute many times.
$stmt = $pdo->prepare('INSERT INTO table_name (col_name) VALUES (?);');
$stmt->bindParam(1, $fruit, PDO::PARAM_STR);
foreach ($fruits as $fruit) {
$stmt->execute();
}
if you’re not restricted to an interface/api than that is a terrible way to do things considering it will result in n * 2 trips to the database server where n is equal to the number of fruits. On the other hand using a single insert query will only ever result in two trips.
that’s not entirely correct. the statement is only parsed once and in the loop only the data are send over.
the roundtrips may only pose an issue if your DB is on an external server, i.e. where you would have to make an actual TCP request to do something. on localhost that is completely neglect-able.
How would I do this using procedural code?
My INSERT has a couple of columns…
$q2 = "INSERT INTO log_bad_word (member_id, word, created_on)
VALUES (?, ?, NOW())";
$stmt2 = mysqli_prepare($dbc, $q2);
mysqli_stmt_bind_param($stmt2, 'is', $memberID, $word);
// mysqli_stmt_execute($stmt2);
// Not sure what to do here
foreach ($total_matches as $match) {
mysqli_stmt_execute($stmt2);
}
if (mysqli_stmt_affected_rows($stmt2)==1){
// Insert Succeeded.
}else{
// Insert Failed.
}
I would have to look up which OOP code matches which procedural wrapper, so I usually do not use the wrappers to begin with …
then you have to populate each of the bound parameters in the foreach() explicitly.
You’re right but 1 + n is *likely to be SIGNIFICANTLY more than 2. A *little foresight like this can drastically reduce performance degradation when application(s) scale. Also Ic an’t remember the last time I worked on an application where the database wasn’t on a separate server. I would think most production systems are though I’m not a system admin.
So based on the problem I described, and the constraint of needing to use Prepared Statements, how would you recommend I do things?
For now, I just flipped the code above, and stuck my INSERT Prepared Statement inside of a FOREACH loop. It works, but is someone swears a lot, or I have tons of people swearing, then I will keep my database busy!! 
I’m not sure if it is such a big deal for something like this, but I am always trying to learn how to program things more efficiently!
Honestly, it is going to take some thought because I haven’t used PDO directly in a long time.
PDO and Prepared Statements are different, right?
So does that mean this can’t be done with Prepared Statements?
Prepared statements can be used in PDO for bulk inserts, below is an example:
try {
$sql="
INSERT INTO
ue_game_alliance_rank_rights
(
rank
, `right`
)
VALUES
";
$insertQuery = array();
$insertData = array();
foreach ($rights_status['add'] AS $row ) {
$insertQuery[] = '(?,?)';
$insertData[] = $rank;
$insertData[] = $row['id'];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $this->db->prepare($sql);
$stmt->execute($insertData);
}
}
catch (PDOException $e) {
echo "Something went wrong!<br />Query with error: $sql <br />Reason given: {$e->getMessage()}");
return false;
}
In this example the value for one field is the same for every record inserted, the value for the other field is coming from an array
A slight rewrite of that would also work with mysqli_ calls (either procedural or OOP).
Yes
No
See SpacePhoenix example.
I just meant that I’ve been using frameworks and CMSs that that provide an interface for interacting with the database abstracting away the raw pdo api.