looping through multiple arrays and inserting into SQL

Question

I have a form that once submitted some of its result are stored in arrays, example:

(The form has multiple lines with the same input names)

<select name="product[]"> once submitted goes into $_GET['product']

if I do:

// Product ID's
foreach($_GET['product'] as $name => $prodvalue) {
print "$name : $prodvalue<br>";
}

the following is returned:

0 : 9

1 : 10

2 : 11

3 : 12

As well as the Product ID's I have 2 other form input structured the same way, so my question is how do I loop through each of the $_GET's ($_GET['product'], $_GET['linequantity'] and $_GET['lineprice']) to add each of them to multiple SQL table rows? Also there will be other records that need to be entered, but, these will be constant, so for instance, if 3 rows are to be added then the other records will be the same for each of the 3 rows.

Please help me, I'm goin' nuts!

B.

EDIT:

The table is called: order_lines

Value => Field

$_GET['product'] => product_id

$_GET['linequantity'] => unit_price

$_GET['lineprice'] => qty

$unh => unh

There are more, but, i can work it out from there.

We could give you a much more accurate answer if you gave some idea of what the table this is going into looks like. What columns are you populating? — Jeffrey Blake
– Jeffrey Blake, Commented Aug 14, 2010 at 15:15

Kwebble · Accepted Answer · 2010-08-14 15:46:46Z

1

If the keys of all form field arrays are equal then you can use the name of one to reference the other:

$values = array();
foreach ($_GET['product'] as $name => $value) {
    $values[] = array($value, $_GET['linequantity'][$name], $_GET['lineprice'][$name]);
}

This creates an array in which each element is an array with related fields:

answered Aug 14, 2010 at 15:46

Kwebble

2,07514 silver badges23 bronze badges

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

Comments

Jeffrey Blake · Accepted Answer · 2010-08-14 16:12:07Z

0

Assuming you have the same number of items in each collection, I would go with something like this:

$staticValue1 = $_GET['value1'];
$staticValue2 = $_GET['value2'];

foreach($_GET['product'] as $name => $prodvalue) {
  $name = my_escape($name);
  $prodvalue = my_escape($prodvalue);
  $linequantity = my_escape($_GET['linequantity'][$name]);
  $lineprice = my_escape($_GET['lineprice'][$name]);
  if (checkFormat($linequantity, $lineprince, $prodvalue, $name, $staticValue1, $staticValue2) {
    $query = "INSERT INTO <table> (qty, unit_price, product, name, static_value_1, static_value_2) VALUES ".
           "(".$linequantity.", '".$lineprice."', '".$prodvalue."', '".$name."', '".$staticValue1."', '".$staticValue2."')";
    $result = mysql_query($query); 
  }
}

function my_escape($input) {
  //filter chars that will cause problems in the database
  //  then return the filtered values; depends on db
  //  usually this means encoding quotes, at the very least

  return str_replace("'","/'",$input);
}

function checkFormat($linequantity, $lineprince, $prodvalue, $name, $staticValue1, $staticValue2) {
  //use a series of comparisons to return true or false
  //  based on whether or not ALL inputs match the expected range of values

  if (!(is_numeric($linequantity)) return false;

  ... //<-- more comparisons

  return true; // <-- reaching this means it did not fail on any checks
}

edited Aug 14, 2010 at 16:12

answered Aug 14, 2010 at 15:18

Jeffrey Blake

9,7396 gold badges46 silver badges67 bronze badges

8 Comments

Bift Over a year ago

Thanks, but, if i echo $query i get this: INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES (0, '1', '', '', 'Array', 'Array', '9')INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES (0, '1', '', '', 'Array', 'Array', '10')INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES (0, '1', '', '', 'Array', 'Array', '11')INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES (0, '1', '', '', 'Array', 'Array', '12')

Jeffrey Blake Over a year ago

You're getting multiple repetitions because it's echoing in a loop. So really, one query is

INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES (0, '1', '', '', 'Array', 'Array', '9')

, which is almost right. Looks like it's not quite getting the desired values for qty, unit_price, and possibly unh/o_id. I'll update to deal with the issue for qty/unit_price.

Jeffrey Blake Over a year ago

Updated. Should work now, except that it may still have no value for unh and o_id, since those are set elsewhere. The key is adding [$name] to the end of the $_GET[''] assignments.

Bift Over a year ago

sorry, still getting this: INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES ('3', '1', '8ea946deef1c4660ab2b8fb01753092f', '1', '65.00', '1', '9')INSERT INTO order_lines (c_id, d-id, unh, o_id, unit_price, qty, product_id) VALUES ('3', '1', '8ea946deef1c4660ab2b8fb01753092f', '1', '69.00', '1', '12') Which wont enter into the db...

Jeffrey Blake Over a year ago

Again, each INSERT ... instance is a separate $query from a separate pass through the loop. You can verify this by putting a separation character at the end of the line where you output it. Something like echo $query."\n<br\>\n";

|

Collectives™ on Stack Overflow

looping through multiple arrays and inserting into SQL

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related