PHP write more than 1 row to MySQL database at the same time

Question

I have the following code to write an entry to a database:

<?php
$database = 'abc';
$dbhost = 'abc.com';
$dbuser = 'abc';
$dbpass = 'abc';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$key = 'abc';

$checkKey = $_GET['key'];

if ($key === $checkKey) {

if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$a = mysql_real_escape_string($_GET['a']);
$b = mysql_real_escape_string($_GET['b']);
$c = mysql_real_escape_string($_GET['c']);
$c = $c / 1000;
$d = mysql_real_escape_string($_GET['d']);

$sql = 'INSERT INTO x '.
       '(a,b, c, d) '.
       'VALUES (' .$a. ',' .$b. ',FROM_UNIXTIME(' .$c. '),' .$d. ')';

mysql_select_db($database);

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error() . ': ' . $sql);
}
echo "Entered data successfully\n";
mysql_close($conn);
} else {
  die('Key not valid!');
}
?>

This works fine but it's quite slow, since for every row I want to add, I have to call the script once. Would it be considerable faster to for example collect 1000 rows and then call the URL with this 1000 rows once and store each row in the database? If yes, how can I achieve that?

You probably know that inserting multiple rows with `MySQL` is kinda easy (once you have the data, see http://www.electrictoolbox.com/mysql-insert-multiple-records/ ). So I guess your problem is to `send` and `read` multiple rows to your script? — Antonio Ragagnin, May 12 '14 at 11:44
@sanketh Actually OP need to send and recieve an array of elements. So it is not just a `MySQL` problem, IMHO. — Antonio Ragagnin, May 12 '14 at 11:44
Also, consider using `POST` instead of `GET`. Becouse you can't send much data in a `GET` url (I don't think you can send 1000 elements in a `GET`, but you can in a `POST`). — Antonio Ragagnin, May 12 '14 at 11:45
Send all your arguments as in a comma-separated format (or whatever format you want) and then parse the strings, construct the query and run it. — Sanketh, May 12 '14 at 11:47
You can also send arrays with a form, see http://stackoverflow.com/questions/18897490/passing-array-data-from-an-html-form-to-php-array-variables — Antonio Ragagnin, May 12 '14 at 11:48
@FischerLudrian: Take a look at http://stackoverflow.com/questions/46585/when-do-you-use-post-and-when-do-you-use-get too, why you should avoid GET requests for data changing operations. — VMai, May 12 '14 at 11:55

neelsg · Answer 1 · 2014-05-12T11:53:40.580

You can insert multiple lines using a single insert statement using the following format:

INSERT INTO table (column1, column2) VALUES
(value1, value2), (value1, value2), (value1, value2)

You can send multiple rows with an html post as follows:

<input type="text" name="field1[1]" />
<input type="text" name="field2[1]" />

<input type="text" name="field1[2]" />
<input type="text" name="field2[2]" />

<input type="text" name="field1[3]" />
<input type="text" name="field2[3]" />

$_POST['field1'] and $_POST['field2'] will, in this case, each be an array containing 3 values.

Note that there is a limit to how many fields can be sent in a single request (I think it is 1000)

Thanks, but how can I do that with a POST request? – Fischer Ludrian May 12 '14 at 12:18 — Fischer Ludrian, May 12 '14 at 12:18

PHP write more than 1 row to MySQL database at the same time

1 Answers1