How do I enter multiple line items into mysql database using php

Question

I'm trying to create an RMA form. The idea is to have the ability to enter multiple rows with on one page. The problem I'm having is that if I have the form with more than one row it only submits the top row of data. I looked around and read that I should be using a foreach() and implode() function but I can't seem to get it to work.

This is my entire PHP code that currently WORKS with a single row. If someone could please help me get multiple rows in as well that'd be great. Also I don't have much experience with php/mysql so if you could please list an example instead of saying "use x,y,z functions" because that hasn't helped me so far.

All assisstance is greatly appreciated. Thanks

<html><head><title> TEST - RMA</title></head>
<body>
<h1>RMA Screen</h1>
<h2>Order Results</h2>
<?php
  $controller_serial = $_POST['controller'];
  $sample_holder_serial = $_POST['sampleholder'];
  $first_name = $_POST['firstname'];
  $last_name = $_POST['lastname'];
  $randomnumber = $_POST['RMAnum'];


  $link = mysql_connect('localhost', 'user', 'abcd'); 
  if (!$link) 
  {
    die('Could not connect: ' . mysql_error()); 
  }
  echo 'Connected successfully';

  mysql_select_db(rma);

  $randomnumber = mt_rand(1,100);
  while( $fetch = mysql_fetch_array( 
    mysql_query("SELECT `RMAnum` FROM `rma` 
                 WHERE `RMAnum` = $randomnumber") ) ) {
    $randomnumber = mt_rand(1,10);

  }

  mysql_query("INSERT INTO `rma` 
            (`controller_serial`, `sample_holder_serial`
            , `first_name`, `last_name`, `RMAnum`)
            VALUES('$controller_serial','$sample_holder_serial'
            , '$first_name', '$last_name', '$randomnumber')") 
    or die(mysql_error());
  mysql_close($link);


  echo 'Thanks for submitting the form.<br />';
  echo 'RMA NUMBER: ' . $randomnumber . '<br />';
  echo 'Controller Serial Number: ' . $controller_serial . '<br />';
  echo 'Sample Holder Serial Number: ' . $sample_holder_serial . '<br />';
  echo 'First Name: ' . $first_name . '<br />';
  echo 'Last Name: ' . $last_name . '<br />';
?>

</body></html>

Your database is at risk of SQL injection with the code you posted. — hakre, Jun 12 '11 at 18:09

score 2 · Answer 1 · answered Jun 12 '11 at 18:19

You need to treat your form(s) as an array. It can be as simple as appending a number to the end of each form field and then fetching that number when you post the form.

To keep it as simple and old school as possible until you understand the concept of arrays, lets use the number suffix method:

<input type="text" name="controller_serial_1" value="foobar" />

....

<input type="text" name="controller_serial_2" value="otherfoobar" />

.. and so on.

Create a hidden field max_records

<input type="hidden" name="max_records" value="2" />

Then in your PHP:

for($i=1; $i<$_POST['max_records']; $i++)
{
    mysql_query("INSERT INTO `rma` (`controller_serial`, `sample_holder_serial`, `first_name`, `last_name`, `RMAnum`) VALUES($_POST['controller_serial_'].$i,$_POST['sample_holder_serial_'].$i, $_POST['first_name_'].$i, $_POST['last_name_'].$i, $_POST['randomnumber_'].$i)");
}

Once you can grasp this concept, the next step for optimized code would be to make each of those fields arrays so you can do a foreach().

@fahad Add validation for empty form rows, and @johan's mysql_real_escape_string() from below, and we've got a winner. — Mitchell McKenna, Jun 13 '11 at 00:31

score 1 · Answer 2 · answered Jun 12 '11 at 18:21

Your code only works for one row because it's only getting information for one row. First step is to get the information for the multiple rows you want to insert:

$controller_serials = array("controller1", "controller2", "controller3"); // etc... an array of your controllers
$sample_holder_serials = array("sampleholder1", "sampleholder3", "sampleholder3"); // etc.. an array of sampleholders
$first_names; // array of firstnames in the same format
$last_name; // array of lastnames
$randomnumber; // array of randomnumbers

(Or you can put that all into one big multi dimensional array if you want.)

Then, write a foreach:

foreach ($controller_serials as $key => $value){
    mysql_query("INSERT INTO `rma` (`controller_serial`, `sample_holder_serial`, `first_name`, `last_name`, `RMAnum`) VALUES('{$value}','{$sample_holder_serials[$key]}', '$first_names[$key]', '$last_names[$key]', '$randomnumbers[$key]')");

}

This should work on a basic level, but it's vulnerable to sql injection. Sanitize your inputs and you're good.

EDIT: Also, I'm not sure where you're going with the random numbers. If you're going to use a random number from 1-100 to determine where you're entering your values into your database, you should know that you'll be overwriting your own entries sometimes. Why not just simply insert a new row?

score 0 · Answer 3 · answered Jun 12 '11 at 18:11

0

You need to submit your form elements as an array and then iterate through them, inserting new rows as you go.

answered Jun 12 '11 at 18:11

Jay Sidri

6,271
3
43
62

score 0 · Answer 4 · answered Jun 12 '11 at 18:20

0

The problem lies with the echo statements, they should be inside the while() loop, as because you would only be left over with a single value if you end the loop. Rather you should echo the values each step of the loop.

answered Jun 12 '11 at 18:20

Sujit Agarwal

12,348
11
48
79

score 0 · Answer 5 · edited May 23 '17 at 10:34

You are at risk from SQL-injection:

Change this code

enter image description here coding horror

  $controller_serial = $_POST['controller'];
  $sample_holder_serial = $_POST['sampleholder'];
  $first_name = $_POST['firstname'];
  $last_name = $_POST['lastname'];
  $randomnumber = $_POST['RMAnum'];
  .....
  mysql_query("SELECT RMAnum FROM rma WHERE RMAnum = '$randomnumber' ")

Into this safe code:

  $controller_serial = mysql_real_escape_string($_POST['controller']);
  $sample_holder_serial = mysql_real_escape_string($_POST['sampleholder']);
  $first_name = mysql_real_escape_string($_POST['firstname']);
  $last_name = mysql_real_escape_string($_POST['lastname']);
  $randomnumber = mysql_real_escape_string($_POST['RMAnum']);
  .....
  mysql_query("SELECT RMAnum FROM rma WHERE RMAnum = '$randomnumber' ")  
  // single quotes are vital, dont forget them       ^             ^

You need to put single quotes ' around all $vars in the query text or mysql_real_escape_string() will not work!

See this question for more info: How does the SQL injection from the "Bobby Tables" XKCD comic work?

How do I enter multiple line items into mysql database using php

5 Answers5