Adding checkbox array values into several rows in MySQL database

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.

Have you tried anything yet? Do you have any code? Because the short answer is, yes you can collect all your data and loop through it to insert into a db. But we're not going to write the code for you. We'll guide you to your answer through use of your code. — the_pete
– the_pete, Commented Sep 8, 2014 at 19:15
yes it is possible, does that answer the question @janlindso ? — meda
– meda, Commented Sep 8, 2014 at 19:16
use multiple inserts, or use msyql's extended insert syntax insert ... values (set#1), (set#2), ..., (set#n). The choice is up to you. — Marc B
– Marc B, Commented Sep 8, 2014 at 19:17
@the_pete Yes, I did it with for each loop, but I thought it would be an easier/better way to do it? If you read it again, it is more than a yes/no question. I'm asking for a suggestion for a solution, not complete code. — janlindso
– janlindso, Commented Sep 8, 2014 at 19:22

Chique · Accepted Answer · 2014-09-08 20:29:30Z

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

answered Sep 8, 2014 at 20:29

Chique

8541 gold badge6 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Adding checkbox array values into several rows in MySQL 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