php mysql insert X number of multiple rows with 1 query

Question

i want to do insert as many rows as the number in $add_rows = $_POST['add-rows'] to a table. for what i'm working now, i need 32 rows. how do i go about making the SQL VALUES part a loop so it enters 32 rows??

this is my form

<form>
<input name="add-name" id="add-name" type="text" value="">
<input name="add-start" id="add-start" type="text" value="">
<input name="add-end" id="add-end" type="text" value="">
<input name="add-rows" id="add-rows" type="text" value="">
<input name="add-submit" id="add-submit" type="submit" value="Add" />
</form>

below are the values i'll be posting from the form, the rest of values can be setas NULL

$add_name = $_POST['add-name'];
$add_start = $_POST['add-start'];
$add_end = $_POST['add-end'];
$add_rows = $_POST['add-rows']; //in this case, the value is 32

this is the query... i'd need to enter 32 VALUES.

mysql_query("INSERT INTO table position, name, start, end, code, one, two, three, four, five, six) 
VALUES (NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL)")
or die(mysql_error());

As a side note: Don't use the mysql_* functions anymore and consider switching to MySQLi oder PDO instead. — Alexander M. Turek
– Alexander M. Turek, Commented Mar 12, 2013 at 16:56
You have a syntax error in your SQL statement (missing parenthesis after the table name) — Chris L
– Chris L, Commented Mar 12, 2013 at 17:08

Alexander M. Turek · Accepted Answer · 2013-03-13 13:02:29Z

1

This should give you 32 rows.

$sql = "INSERT INTO table (position, name, start, end, code, one, two, three, four, five, six) 
VALUES (NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL)"
     . str_repeat(", (NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL)", $add_rows - 1)
mysql_query($sql);

edited Mar 13, 2013 at 13:02

answered Mar 12, 2013 at 17:02

Alexander M. Turek

5762 silver badges8 bronze badges

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

1 Comment

Azukah Over a year ago

thanks. it worked but for some reason it's inserting an extra blank row..?

Acuao · Accepted Answer · 2013-03-12 17:03:10Z

You can try this

mysql_query("INSERT INTO table position, name, start, end, code, one, two, three, four, five, six) 
VALUES (NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(NULL, '$add_name', '$add_start', '$add_end', NULL, NULL, NULL, NULL, NULL, NULL, NULL)")
or die(mysql_error());

here there's only 4 lines, but you cant do it for 32 lines.

you can construct also the request in a loop (to set different variables values) Don't forget the comma bewteen each value (but there's no comma at the end of the last line).

Chris L · Accepted Answer · 2013-03-12 17:10:59Z

0

Use PDO along with a prepared statement:

$dbh = new PDO('mysql:host=localhost;dbname=example', $user, $pass);
$stmt = $dbh->prepare("INSERT INTO table (position, name, start, end, code, one, two, three, four, five, six) VALUES (NULL, :add_name, :add_start, :add_end, NULL, NULL, NULL, NULL, NULL, NULL, NULL)");
$stmt->bindParam(':add_name', $add_name);
$stmt->bindParam(':add_start', $add_start);
$stmt->bindParam(':add_end', $add_end);

for ($i = 0; $i < $add_rows; $i++) {
    $stmt->execute();
}

This has the added benefit of hardening your query against SQL injection attacks. Your question and other answers (at the time of writing) are using unescaped $_POST values directly in the query.

edited Mar 12, 2013 at 17:10

answered Mar 12, 2013 at 17:04

Chris L

6161 gold badge5 silver badges11 bronze badges

1 Comment

Azukah Over a year ago

i have issues using PDO. i'd like to try it but everytime i use it, something breaks. thanks, though.

Black Soul · Accepted Answer · 2013-03-12 17:22:23Z

0

The column names need to be between parenthesis. I think you need to see the syntax:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

I think this is a question & answers site not a give me this syntax site. @Azukah Have a look at this syntax for learning. And in case you just want the syntax, check other answers.

:)

edited Mar 12, 2013 at 17:22

answered Mar 12, 2013 at 17:11

Black Soul

314 bronze badges

1 Comment

Azukah Over a year ago

sorry about the syntax. i messed it up when retyping. will double check b4 posting. thanks!

Collectives™ on Stack Overflow

php mysql insert X number of multiple rows with 1 query

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related