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!

ircmaxell · Accepted Answer · 2010-08-30 13:42:56Z

2

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)...

answered Aug 30, 2010 at 13:42

ircmaxell

166k36 gold badges269 silver badges316 bronze badges

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

3 Comments

ircmaxell Over a year ago

@AlberT: It's not about LOC (Writing the smallest possible code base is not the goal). It's about writing a maintainable and secure system. Using these style queries, anyone can quickly read and see what's going on. A cursory glance will tell anyone reading that everything is properly escaped. And less LOC is not always better. There's always a trade-off between readability and maintainability and efficiency in writing. So I gladly accept some increase in LOC where appropriate for enhanced readability and maintainability...

anon Over a year ago

Really appreciate the help ircmaxell. I will familiarize myself with prevention of SQL injections before posting again in the future.

ircmaxell Over a year ago

@anon: Don't hesitate to post if you have questions (of course try to search first, since a lot has already been answered both here and else where). I'm just a stickler on certain subjects (like SQL Injection, since there's no excuse for it still occurring in this day in age). And considering how many people copy and paste code without reading or understanding, I'd rather see examples with proper techniques rather than just saying on the side "Oh, and you should probably escape them first too"... It's in essence the same as lead by example...

Jack Fuchs · Accepted Answer · 2010-08-30 14:14:08Z

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)

Dominique · Accepted Answer · 2010-08-30 13:24:40Z

0

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.

answered Aug 30, 2010 at 13:24

Dominique

9081 gold badge7 silver badges15 bronze badges

Comments

drAlberT · Accepted Answer · 2010-08-30 14:15:12Z

0

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()));
  }
}
?>

edited Aug 30, 2010 at 14:15

answered Aug 30, 2010 at 14:06

drAlberT

23.5k5 gold badges37 silver badges41 bronze badges

Collectives™ on Stack Overflow

Combining two PHP variables for MySQL query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related