CODEWITHSUNDEEP
phpfor-loop
ak85
ak85

Reputation: 4264

Use value of array in for loop outside for loop

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?

Upvotes: 0

Views: 258

Answers (2)

Richard Harrison
Richard Harrison

Reputation: 19393

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

Upvotes: 2

UltraInstinct
UltraInstinct

Reputation: 44444

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.

Upvotes: 1

Related Questions