0

Ok so i need to insert multiple rows into a database using MySql from one form, each row is a result. The was i started doing it is the following:

INSERT INTO results (1,2,3,4,5,6,7) VALUES (1,2,3,4,5,6,7), (8,9,10,11,12,13,14)

And so on, my question is:

I don't know how many rows of results a user will want to insert ( a maximum of 15 will apply). Is their a more practical way of achieving the same result? Or am I better off sticking to the way i originally planned?

JustStarting
  • 9
  • 2
  • You can have a look at this post http://stackoverflow.com/questions/19680494/insert-multiple-rows-with-pdo-prepared-statements/21653119#21653119 and read my answer – Justin Iurman Jun 14 '14 at 21:32
  • 1
    @JustinIurman Thanks, just read it. Cheers for the quick response! – JustStarting Jun 14 '14 at 21:36
  • You're welcome. Anyway, the link i gave you is about inserting data with **one** query. Otherwise, you can use a loop with multiple inserts – Justin Iurman Jun 14 '14 at 21:41
  • @JustinIurman Im only inserting one row of results just each row will have different information that needs to be inserted into the database. But it will definitely help me out. Thank you! – JustStarting Jun 14 '14 at 21:48

1 Answers1

0

1°) Use prepared statement (like in Java):

(For safe code, always use prepared statement and avoid generate SQL)

$stmt =  $db->stmt_init();
$stmt->prepare("INSERT INTO foo_table (id, firstname, lastname) VALUES(?, ?, ?)");
foreach($myarray as $row)
{
    $stmt->bind_param('iss', $row['id'], $row['firstname'], $row['lastname']);
    $stmt->execute();
}
$stmt->close();

To understant "iss" param in bind_param: http://uk3.php.net/manual/fr/mysqli-stmt.bind-param.php

2°) Generate batch insert:

Batch inserts with PHP

Community
  • 1
  • 1
Destroyica
  • 4,147
  • 3
  • 33
  • 50