CODEWITHSUNDEEP
phpmysql
Azukah
Azukah

Reputation: 227

php mysql insert X number of multiple rows with 1 query

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());

Upvotes: 0

Views: 817

Answers (4)

Alexander M. Turek
Alexander M. Turek

Reputation: 576

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

Upvotes: 1

Black Soul
Black Soul

Reputation: 31

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.

:)

Upvotes: 0

Chris L
Chris L

Reputation: 616

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.

Upvotes: 0

Acuao
Acuao

Reputation: 691

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

Upvotes: 0

Related Questions