Insert multiple rows with SQL/PHP

Question

I have an input field where the user can specify a number between 1-22. Now let's say they enter 14. Then I want to insert 14 rows. What the best way to do this? With either SQL or PHP.

$current_number_of_ranks = 7;
$number_of_ranks = 14;

INSERT INTO ranks (c_id, name) VALUES (67, '(new rank)')

EDIT: Also, lets say theres currently 7 rows with c_id 67 and they enter the number 9. I only wanna insert 2 rows.

EDIT: This is just an example. I got everything working except figuring out how to insert multiple rows. All I need help with is how to insert multiple rows.

they are gonna insert 14 rows? of what? random data?, or are you gonna ask the user what does he wants to insert? — jsedano
– jsedano, Commented Jul 5, 2013 at 23:11
Then you need to also SELECT prior INSERTING to know how many rows already exist for that given ID. — Prix
– Prix, Commented Jul 5, 2013 at 23:30

OcuS · Accepted Answer · 2013-07-16 09:48:13Z

2

You should use MySQL multiple insert:

$number_of_ranks = 14;

$value = implode(',', array_fill(0, $number_of_ranks, "(67, '(new rank)')"));
$query = 'INSERT INTO ranks (c_id, name) VALUES ' . $value;
// then execute the query

the result of $query will be:

"INSERT INTO ranks (c_id, name) VALUES (67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)'),(67, '(new rank)')"

edited Jul 16, 2013 at 9:48

OcuS

5,3203 gold badges38 silver badges46 bronze badges

answered Jul 5, 2013 at 23:20

Picoss

2,07713 silver badges14 bronze badges

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

Comments

Sterling Archer · Accepted Answer · 2013-07-05 23:13:44Z

0

With the assumption that you have a good reason for random inserts..

You could place the query into a loop

$number_of_ranks = 14;
for ($i=0; $i<$number_of_ranks; $i++) {
    mysqli_query("INSERT INTO ranks (c_id, name) VALUES (67, '(new rank)')");
    //use normal db class if you have written one of course
}

answered Jul 5, 2013 at 23:13

Sterling Archer

22.5k19 gold badges86 silver badges121 bronze badges

1 Comment

Andrei Bârsan Over a year ago

This is not the optimal way to do it. Performing any type of query in a loop is usually frowned upon due to performance reasons.

Collectives™ on Stack Overflow

Insert multiple rows with SQL/PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related