Combining two PHP variables for MySQL query

Question

I have a variable formvar that is incremented every time a user adds an additional field in an HTML form. This variable is posted to the PHP script for the purpose of looping through all of the added fields.

I am trying to combine two variables in the MySQL query to match what is in my HTML form. I would like the MySQL query to go upc0, upc1, etc until the for loop terminates.

for($i=0;$i<=$_POST[formvar];$i++)
{
mysql_select_db("bits", $con);
$sql="INSERT INTO report (UPC, Quantity, Comment)
VALUES ('$_POST[upc].$i','$_POST[quantity].$i','$_POST[comment].$i')";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
else echo "Records added successfully";
}

Sorry if this code is bad, I am new to web programming.

Thank you!

Answer 1

Apart the fact that including user variables ($_POST) in the query let you being exposed to SQL Injection Attacks ....

use '{$_POST["comment{$1}"]}'

EDIT:

if you want to take into account security too, then you have to sanitize your user provided data before use. So, in the mysql case you can simply assign sanitized variables and then use only them in the quesries ... You could do it also at the beginning of your scripts as a default, for the entire superglobals arrays..

i.e. use array_map on $_POST with mysql_real_escape_string as mapping function to define a new $SAFE_POST array to be used in your queries ...

What the actual methiod o implement is a case specific issue ... the idea given :)

$_GLOBALS['SAFE_POST'] = array_map('mysql_real_escape_string', $_POST);

Answer 2

Not sure if I understand the question well but this is what I think :

$sql="INSERT INTO report (UPC, Quantity, Comment) VALUES
           ('" . $_POST["upc".$i] .  "','" . $_POST["quantity" . $i] .  "','" . $_POST["comment" . $i] .  "')";

Note : this is a short version, you must add mysql_real_escape_string, etc, etc.

Also I supposed every variable could be string so I surrounded them by ''.

$_POST["name" . $i] let you loop throught POST variables starting with the name "name" followed by a number, this must be inserted into your for loop.

Answer 3

Ok, since each answer hinted at escaping (but did not give an example):

$sql = "INSERT INTO report (UPC, Quantity, Comment) VALUES
       ('" . mysql_real_escape_string($_POST["upc".$i]) .  "','" . 
       mysql_real_escape_string($_POST["quantity" . $i]) .  "','" . 
       mysql_real_escape_string($_POST["comment" . $i]) .  "')";

That should protect you from SQL Injection, and is one proper method of creating sql queries. The best method would be to use parametrized queries (There's a ton of information out there on it, so I'd suggest a good Google search would be better than me trying to explain it here)...

Answer 4

As recipes are so acclaimed I'm going to give my own, concerning the actual question:

<?php 
for ($i=0; $i<=$_POST['formvar']; ++$i) {
  mysql_select_db("bits", $con);
  $v = array_map(mysql_real_escape_string(array(_POST["upc{$i}"], $_POST["quantity{$i}"], $_POST["comment{$i}"])));
  $sql = "INSERT INTO report (UPC, Quantity, Comment) VALUES('"
       . implode("', '", $v)
       . "')";

  if (!mysql_query($sql,$con)) {
    trigger_error(html_entities('Error: ' . mysql_error()));
  }
}
?>

Answer 5

First things first. In your HTML, create Input-Fields like this:

<input type="foo" name="upc[]">
<input type="foo" name="quantity[]">
<input type="foo" name="comment[]">

Then in your PHP-Script you do it like this:

<?php
# Choose DB
mysql_select_db("bits", $con);

# Iterates the Form-Data
$data_arr = array();
foreach($_POST['upc'] as $k=>$v) {
  # Makes sure all needed data is available
  if(isset($_POST['quantity'][$k], $_POST['comment'][$k])) {
    $data_arr[] = array(
      'upc' => $v,
      'quantity' => $_POST['quantity'][$k],
      'comment' => $_POST['comment'][$k]
    );
  }
}

# Build mysql insert string
foreach($data_arr as $k=>$v) {
  # Escapes each field
  $v = array_map('mysql_real_escape_string', $v);
  # Maps array to value set
  $data_arr[$k] = '('. implode(',', $v). ')';
}

$sql = 'INSERT INTO report (UPC, Quantity, Comment) VALUES '. implode(', ', $data_arr);

# Perform mysql query
mysql_query($sql, $con) or die('Error: ' . mysql_error());

echo 'Records added successfully';

Wrote it on my iPad, i'm on an airplane... so untestet. Good luck. ;o)