Insert unknown number of rows into MySQL using PHP

Question

I am trying to insert an unknown number of rows into MySQL using PHP. This is how it should work:

1. Javascript parses HTML DOM to create a multi-dimensional array based on a css class. The array will have a certain number of rows(or sub-arrays) corresponding to the number of elements that have that class. (This could be any integer 0 or greater... obviously).

2. Then, on a JavaScript event, the array is sent to a PHP script.

3. The PHP script will INSERT data from the array into MySQL.

My problem is that I don't know how to tell my PHP script how many values are in the array. And I don't know how to write the mysql_query() without knowing the number of values (or rows) that should be inserted.

Answer 1

You can insert more than one row at a time to MySQL:

INSERT INTO table1 (column1, column2, ...) VALUES (value_col1, value_col2), (value2_col1, value2_col2), ...;

In PHP, you can build your query by looping through rows and adding them to the SQL string:

$sql = "INSERT INTO table1 (col1, col2) VALUES ";
foreach($rows as $i=>$row) {
    if ($i>0) {
        $sql .= sprintf(",(%s,%s)", $row["col1_value"], $row["col2_value"]);
    } else {
        $sql .= sprintf("(%s,%s)", $row["col1_value"], $row["col2_value"]);
    }
}
mysql_query($sql);

You have to be sure to properly escape your values depending upon what you're actually inserting.

Answer 2

Why don't you prepare a two dimensional array while searching with the css class identifier like this?

//This is jquery code - you can write javascript to do the same
$(`.class`).each(function(i,e){resultsArray.push($(this).val());}); 

This will save you from the headache of traversing a multidimensional array in the backend and you can simply do a count() in you PHP code and the following query preparation.

Query preparation
Assuming you have a two dimensional array you can use a bulk insert query like this:-

INSERT INTO tablename (a,b)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

And prepare the query dynamically using PHP like this -

$counter = 0;
$valuesPart = NULL;
foreach($_POST as $each)
{
  if($counter > 0)
     $appendComma = ",";
  else
     $appendComma ="";
  $valuesPart .= $appendComma."(".$each['key1'].",".$each['key2'].")";
  $counter++;
}

if(!empty($valuesPart))
    $mysql_query = "INSERT INTO tablename (a,b)  VALUES ".$valuesPart;

So, you don't need to know how many results are to be actually inserted.

If you stay with the multidimensional array, you will probably need to code or search for a code to traverse the multidimensional array which will probably involve recursion and a lot of complex code. There will be many chances of errors and it will be a slower (may be little but a finite amount which is not necessary).

Answer 3

So I assume the array is getting to PHP successfully, through $_POST or whatever? If you aren't sure then do a var_dump or echo_r so we can see.

EDIT - wow I put explode where I meant implode several times. fixed.

Assuming that it is, and that each 'sub' array is an associative array in form

[0]
     'id' => 1
     'name' => 'Billy'
     'DOB'  => .....
[1]
    etc.

And the code to build a single query inserting all rows, like this INSERT INTO table ('f1','f2',f3') VALUES ('v11', 'v22', 'v33'), ('v21', 'v22', 'v23'), ......

 $escapeAndQuote = function($x) {return "'".mysql_real_escape_string($x)."'";};
 $rowwise = function($x) {return '('. implode(', ', array_map($escapeAndQuote, $x)) .')'; 

 $fieldString = $rowwise(array_keys($arr[0]));
 $valString = implode(', ', array_map($rowwise, $arr));

 $sql = "INSERT INTO table $fieldString VALUES $valString";
 mysql_query($sql, $conn);

Answer 4

Use a foreach loop to cycle through the array.

//  Example:
    foreach($submitted_array as $insert_array)
    {
         //php and mysql insert query here
    }

Perhaps prepared statements would assist you in your endeavors. Essentially you will declare a generic insert statement and then "bind" values to each input. Read more on PHP PDO Prepared Statements.