将复选框数组值添加到MySQL数据库中的多行中

Question

I'm making a promotion tool for WordPress where I'm sending out a newsletter to several recipients. To keep track, the selected recipients will be added to a MySQL database.

Each row in the SQL has id, newsletter_id, recipient_id and promocode (random generated).

My checkbox array is called recipients[], and I need to insert one row each recipient in the database. Can I somehow make a dynamic SQL query where I insert all in one query, or do I need a loop or something to accomplish this? This is my code so far with a for each.

$checkboxes = isset($_POST['recipients']) ? $_POST['recipients'] : array();
foreach(checkboxes as $recipient) {
    $wpdb->query("INSERT INTO " .$wpdb->prefix ."send(newsletter_id, recipient_id, promocode) VALUES(" .$newsletter_id .", " .$recipient .", " .$promocode .");");
}

Are there any better ways to do this? Any suggestions for a solution would be really helpful.

Answer 1

First we would prepare the set of values as MarcB mentioned. Then we would execute the SQL Query only once outside the loop. Hence will be the fastest approach and fastest loading time.

Try this out

$checkboxes = isset($_POST['recipients']) ? $_POST['recipients'] : array();
$sql = array(); 
foreach($checkboxes as $recipient) {
    $sql[] = "(" .$newsletter_id .", " .$recipient .", " .$promocode .")";
}
mysql_query("INSERT INTO " .$wpdb->prefix ."send(newsletter_id, recipient_id, promocode) VALUES ".implode(",", $sql).";");