How can I iterate through unique variable names when inserting values into Oracle?

Question

In my PHP code, I have variables named/defined like this:

$comment_1 = $_POST["A_comment"];
$comment_2 = $_POST["B_comment"];
$comment_3 = $_POST["C_comment"];

I'm attempting to insert the values from these variables into Oracle using an iterative approach to provide the number being used at the end of each variable name, as shown here:

for($i=1;$i<4;$i++) {
    $sql2 = "INSERT INTO i_avail(IA_COMMENTS) VALUES('$comment_$i')";
    $stid2 = oci_parse($connect, $sql2);
    $r = oci_execute($stid2);
}

Instead of inserting the expected value from the three $POST references (text only), three numbers are inserted into the table (numbers 1,2 and 3). I'm guessing that this is occurring because $comment can't be found, and uses $i instead for the value. In other words, it's not processing the variable name as I had hoped.

How can I configure the variable name in my INSERT statement so that it recognizes my variable names as "$comment_1", "$comment_2" and "$comment_3"? Do I need to use different quotes, or escape something?

Answer 1

$comment_$i in your code is not the correct way of initializing a variable that's why it shows wrong value. This should do the trick. Dynamic variable generation is the term used for this.

for($i=1;$i<=4;$i++) {
    $variable = ${"comment_" . $i};
    $sql2 = "INSERT INTO i_avail(IA_COMMENTS) VALUES('$variable')";
}

Answer 2

Use bind variables for security (to avoid SQL injection attacks) and for performance and scalability.

Also don't commit more than necessary.

Try:

$numvalues = 4;

$comment_1 = "A_comment";
$comment_2 = "B_comment";
$comment_3 = "C_comment";
$comment_4 = "D_comment";

$sql = "INSERT INTO i_avail (ia_comments) VALUES(:bv)";
$s = oci_parse($c, $sql);
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

for ($i=1; $i<=$numvalues; $i++) {

    $variable = ${"comment_" . $i};

    $r = oci_bind_by_name($s, ':bv', $variable);
    if (!$r) {
        $m = oci_error($s);
        trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);
    }

    // Only commit once after all data has been inserted
    $commitmode = $i == $numvalues ? OCI_COMMIT_ON_SUCCESS : OCI_NO_AUTO_COMMIT;

    $r = oci_execute($s, $commitmode);
    if (!$r) {
        $m = oci_error($s);
        trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
    }

}