[RESOLVED] INSERT INTO - concatenate a variable in a loop

[jeepin81]

Hello and Thanks in advance!

I have a loop that inserts multiple comments into my database. Problem being, I cannot concatenate my variable in my insert script that resides in my while loop.

Reason for multiple comment boxes in I can only display 79 characters on a green screen.

here is my code

$NVNDR = $_POST[Vendor];
$NITEM = $_POST[Item];
$NITEM2 = $_POST[Item]; //protect against END OF FILE interuption
$COMT1 = $_POST[Comments1];
$COMT2 = $_POST[Comments2];
$COMT3 = $_POST[Comments3];
$COMT4 = $_POST[Comments4];


echo $COMT1;
echo $COMT2;
echo $COMT3;
echo $COMT4;



  // create loop to insert multiple comments
  $COMCOUNT = 1;

  while($COMCOUNT < 5){
  $sqlINSERT = "INSERT INTO FILES.NICOMMT(NTVNDR, NTITEM, NTSEQ, NTCOMT)
  				  VALUES('$NVNDR', '$NITEM2', '$COMCOUNT', '$COMT"."$COMCOUNT')";  //<MY PROBLEM



$stmt = db2_prepare($i5XX2, $sqlINSERT) 
                    or die("Prepare error: " . db2_stmt_errormsg());
$result = db2_exec($i5XX2, $sqlINSERT) 
                    or die("Execute error: " . db2_stmt_errormsg());

$COMCOUNT++;
}	

I can get it to insert 1-4 so I know my count is work - I Just cannot appead that count to the end of my $COMT variable so it inserts the 4 comment variables???

This is probably an easy solution but I am having troubles.

Thanks Again,
j

[jeepin81]

a little clarification.

If i put $COMT1 into the INSERT script it will post the 1st comment. I need the number "1" to increase as it loops through so it will post all 4 comments into the table?

Thanks

[laserlight]

If i put $COMT1 into the INSERT script it will post the 1st comment. I need the number "1" to increase as it loops through so it will post all 4 comments into the table?

Oh, now I understand. The simplest solution is to change $COMT1, ..., $COMT4 to an array. Another solution is to use variable variables. I recommend the array solution.

[jeepin81]

Thanks for your reply - I appreciate it! At the same time I am also a little confused - how would an array help me within my while loop?

here is what I tried with no luck

  // create loop to insert multiple comments
  $COMCOUNT = 1;
  $RAY = array("$COMT1" => $_POST[Comments1],
  	            "$COMT2" => $_POST[Comments2],
				"$COMT3" => $_POST[Comments3],
				"$COMT4" => $_POST[Comments4]);

  while($COMCOUNT < 5){



  $sqlINSERT = "INSERT INTO FILES.NICOMMT(NTVNDR, NTITEM, NTSEQ, NTCOMT)
  				  VALUES('$NVNDR', '$NITEM2', '$COMCOUNT', '$RAY')";  



$stmt = db2_prepare($i5XX2, $sqlINSERT) 
                    or die("Prepare error: " . db2_stmt_errormsg());
$result = db2_exec($i5XX2, $sqlINSERT) 
                    or die("Execute error: " . db2_stmt_errormsg());

$COMCOUNT++;
}	

Sorry for the confusion - thanks for the help.

J

[laserlight]

Honestly, your database setup is wrong to begin with. It is wrong to decide to have four records when you only need one just because you "can only display 79 characters on a green screen". The user interface should never be the primary factor in deciding the database schema. A far better solution would be to concatenate the comments into one, and then separate them again for display.

Anyway, what you want to do is something like this:

$COMT[] = $_POST['Comments1'];
$COMT[] = $_POST['Comments2'];
$COMT[] = $_POST['Comments3'];
$COMT[] = $_POST['Comments4'];

// ...

for ($i = 0; $i < 4; ++$i) {
    $sql = "INSERT INTO FILES.NICOMMT(NTVNDR, NTITEM, NTSEQ, NTCOMT)
        VALUES('$NVNDR', '$NITEM2', '$i', '{$COMT[$i]}')";
    $stmt = db2_prepare($i5XX2, $sql)
        or die("Prepare error: " . db2_stmt_errormsg());
    $result = db2_exec($i5XX2, $sql)
        or die("Execute error: " . db2_stmt_errormsg());
}

But there are other problems besides the one with the database design:

• You failed to check that incoming variables exist before using them.

• You failed to correctly quote your strings. For example, $POST[Comments1] should be $POST['Comments1'].

• You failed to bind values to the prepared statement, but instead built the statement directly. This means that you are vulnerable to SQL injection.

• Your use of fully capitalised variable names is contrary to almost universal practice in PHP. Furthermore, $i5XX2 is a terrible name for a database connection handle.

[jeepin81]

Thank You for your help - you truly are a PHP Witch!

As far as your comments in regards to my poor programming - I will take them in stride - As I do appreciate them. I do indeed check that the field was submitted, I do indeed protect against SQL injections, $i5XX2 is merely for forum sake as is the rest of my code displayed on this forum. I didn't want to be redundant in asking a question - I wanted to get to the point. Instead, I'll supply my redundancy in my explanation. With the 79 characters - I agree but the RPG programmer I am working with does not. On an as/400 "green screen" session there is a 79 character length of display on the screen.

Again, Thank You for instilling your wisdom upon me - you as far as I know are near brilliant!

Thanks Again and Best,
j

[laserlight]

jeepin81 wrote:

As far as your comments in regards to my poor programming - I will take them in stride - As I do appreciate them.

Sorry if I sounded harsh, but I felt that you needed to know about them, just in case you didn't.

jeepin81 wrote:

With the 79 characters - I agree but the RPG programmer I am working with does not. On an as/400 "green screen" session there is a 79 character length of display on the screen.

Yes, yes, but what happens if you suddenly have to work with a similiar user interface but with a 69 character length of display on the screen? Are you going to change your database even though the data is essentially the same?

The trick here is to separate the modeling of your data as it is in the database with the presentation of your data in the user interface. For example, you could use a variant of [man]wordwrap/man to insert newline characters at the 79th character position. If the user interface requirements change, you merely change the code that handles the presentation, and leave the database intact. In fact, this means that you can support different user interfaces simultaneously with the same database.