Use value of array in for loop outside for loop

Question

I have the below code. I have a form setup that has 10 input fields set up, 5 for column1 and 5 for column2.

try
{
    $count = count($_POST['column1']);
    $sql = 'INSERT INTO tablename (column1, column2) VALUES ';

    for ($i=0; $i< $count; $i++) 
    {
        $sql2 = '(' .$_POST['column1'][$i] . ', ' . $_POST['column2'][$i] . ')';
        if ($i <$count - 1) {
           $sql2 .= ',';
        }
        echo "$sql2 'indside'<br>";
    }
    echo "$sql2  'outside'<br>";
    $sql3 = "$sql $sql2";
    $pdo->exec($sql3);
}
catch (PDOException $e)
{
  $output = 'Error: ' . $e->getMessage();
  exit();
}

If I input 1,2,3,4,5,6,7,8,9,10 into my 10 input fields and hit submit I get

(1, 6), 'indside'
(2, 7), 'indside'
(3, 8), 'indside'
(4, 9), 'indside'
(5, 10) 'indside'
(5, 10) 'outside'

And 5 is inserted into column 1 and 10 is inserted into column 2 of my database.

What I really want is 5 seperate rows inserted so 1,6 in row 1, 2,7 in row 2 etc. But I can't work out how to take the value of $sql2 when it is in the for loop. Is this possible? Or is their another approach I can take to acheive this?

Richard Harrison · Accepted Answer · 2012-07-09 13:19:30Z

2

This looks like a simple bug;

the line

    $sql2 = '(' .$_POST['column1'][$i] . ', ' . $_POST['column2'][$i] . ')';

is resetting $sql2 - when it should be appending to it, i.e.

    $sql2 .= '(' .$_POST['column1'][$i] . ', ' . $_POST['column2'][$i] . ')';

The result is:

INSERT INTO tablename (column1, column2) VALUES  (1, 6),(2, 7),(3, 8),(4, 9),(5, 10)

entire fixed fragment including fixes SQL injection as noted by @Thrustmaster

try
{
    $count = count($_POST['column1']);
    $sql = 'INSERT INTO tablename (column1, column2) VALUES ';
    $sql2 = '';
    for ($i=0; $i< $count; $i++) 
    {
        if ($sql2 != '') $sql2 .= ",";

        $sql2 = '(' .mysql_real_escape_string($_POST['column1'][$i])
                   . ', ' . mysql_real_escape_string($_POST['column2'][$i]) . ')';
        echo "$sql2 'indside'<br>";
    }
    echo "$sql2  'outside'<br>";
    $sql3 = "$sql $sql2";
    $pdo->exec($sql3);
}
catch (PDOException $e)
{
  $output = 'Error: ' . $e->getMessage();
  exit();
}

edited Jul 9, 2012 at 13:19

answered Jul 8, 2012 at 10:52

Richard Harrison

19.5k4 gold badges43 silver badges67 bronze badges

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

2 Comments

ak85 Over a year ago

Thanks that . did the trick. I had seen it around before but hadn't seen it documented. I got this to work with some slight changes to your code above. I replaced your if statement with the one I had. replaced the $sql2 = below it to $sql2 .= as you suggested and put a space between the . in .mysql_real_escape_string($_POST['column1']

Richard Harrison Over a year ago

my if statement had a bug - it should have been if (sql2 != "")... anyway good news and well done.

UltraInstinct · Accepted Answer · 2012-07-08 10:52:15Z

1

This is because in every iteration of the loop, the variable $sql2 is overwritten. You need to "append" to the value after previous iteration.

Replace:

$sql2 = '(' .$_POST['column1'][$i] . ', ' . $_POST['column2'][$i] . ')';

with:

$sql2 .= '(' .$_POST['column1'][$i] . ', ' . $_POST['column2'][$i] . ')';
//    ^ Append to the string.

And do not forget to initialize $sql2=''; before the for loop.

The way it is now, you are open to SQL Injection attacks. Please use prepared statements for your mysql queries. If not, atleast use mysql_real_escape_string($_POST['column1'][$i]) before building the query.

answered Jul 8, 2012 at 10:52

UltraInstinct

44.6k12 gold badges85 silver badges108 bronze badges

1 Comment

ak85 Over a year ago

Thanks. I have used prepared statements before when it has just been a single entry but I can't work out how to do it in this situation where I have a loop. Do you have any ideas here or should this be posted as a new question?

Collectives™ on Stack Overflow

Use value of array in for loop outside for loop

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related