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

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 electrictoolbox.com/mysql-insert-multiple-records ). So I guess your problem is to send and read multiple rows to your script? — Antonio Ragagnin
– Antonio Ragagnin, Commented May 12, 2014 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
– Antonio Ragagnin, Commented May 12, 2014 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
– Antonio Ragagnin, Commented May 12, 2014 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
– Sanketh, Commented May 12, 2014 at 11:47

neelsg · Accepted Answer · 2014-05-12 11:53:40Z

0

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)

edited May 12, 2014 at 11:53

answered May 12, 2014 at 11:45

neelsg

4,8425 gold badges38 silver badges58 bronze badges

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

1 Comment

Fischer Ludrian Over a year ago

Thanks, but how can I do that with a POST request?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related