php Pass variable as string to be used as variable in the next function?

Question

I am trying to send a variable as string in SQL statement to my DB function, which should then use that string as variable and do the processing.

Its multiple insert script (as array) needing the last insert ID, here's a shorten code:

     $insertID = "$"."insertID";
     $sql = array("first INSERT Script", "INSERT into blah(`ID`, `Name`)
             VALUE ('$insertID', '$name')", "Third script"); 

     $result = dbInsert($sql);

     if ($result){
        // do something
     }

Here's the insert function, I am experimenting if this way would work:

    function dbInsert($sql){
        $con = dbConnect(); 
        try {
        // begin a transaction
        mysqli_begin_transaction($con);

        foreach ($sql as $value) {
          if ($con-> query($value)) {
                $insertID = $con-> insert_id;
            } else {
            trigger_error(mysqli_error($con));
            }
        }
        // if no error, commit. 
        mysqli_commit($con);
    } catch (Exception $e) {
        // if any error, catch the exception and rollback 
        mysqli_rollback($con);
    }
       /* close connection and return the result */
        $con->close();
        return $insertID;
    }

Please note the first script doesn't require any last insert Id, but subsequent ones do. I know I can do individual inserts and skip this whole way, but it would be good if I just send one array of SQLs and function does the insert and sends back the last insert ID (to verify).

When I echo (& die) the SQL statement in Function dbInsert has "$result" (value of $insertID), but its reading it as string and not as the variable, which holds the last insert ID. I have tried few combination but to no avail.

Current:

   INSERT INTO `tblstudent` (`userid`, `scholarnumber`, `firstname`, `middlename`, `lastname`, `datecreated`)
   VALUES ($result, '35566', 'Joe', '', 'Blog', CURRENT_TIMESTAMP);

Should be:

   INSERT INTO `tblstudent` (`userid`, `scholarnumber`, `firstname`, `middlename`, `lastname`, `datecreated`)
   VALUES ('418', '35566', 'Joe', '', 'Blog', CURRENT_TIMESTAMP);

I hope I am making sense and someone would be able to point me in the right direction. Much appreciate your time and help, thank you!

It turns out, I was going about it the wrong way. All I needed to do was pass on "LAST_INSERT_ID()" instead of passing variable etc. Thank you for all the help! Someone might benefit from this, hence updating the post.

Answer 1

To turn a string variable name into the actual variable, you will need to use eval, and to reformat the string that you are sending to dbInsert. At present, you're using double quotes, and the variables are being interpolated by PHP before you send them. You need to send $insertID as text, so it should be in single quotes. Compare these two:

$name = 'Percy';

$sql = array(
    'INSERT into blah(`ID`, `Name`) VALUE (\'$insertID\', \'' . $name . '\')', 
    "INSERT into blah(`ID`, `Name`) VALUE ('$insertID', '$name')"); 

dbInsert($sql);

function dbInsert( $arr ){
    foreach ($arr as $a) {
        echo "$a\n";
    }
    ...

Output:

INSERT into blah(`ID`, `Name`) VALUE ('$insertID', 'Percy')
INSERT into blah(`ID`, `Name`) VALUE ('', 'Percy')

So, use single quotes. Then, to interpolate the value, do the following:

eval("\$a = \"$a\";");

Here's a full example:

    $name = 'Percy';
    $sql = array(
        'INSERT into blah(`ID`, `Name`) VALUE (\'$insertID\', \'' . $name . '\')', 
        "INSERT into blah(`ID`, `Name`) VALUE ('$insertID', '$name')"
    ); 

    dbInsert($sql);

    function dbInsert( $arr ){

        $insertID = '12345';
        foreach ($arr as $a) {
            echo "String before: $a\n";
            eval("\$a = \"$a\";");
            echo "String eval'd: $a\n";
        }
    }

Output:

String before: INSERT into blah(`ID`, `Name`) VALUE ('$insertID', 'Percy')
String eval'd: INSERT into blah(`ID`, `Name`) VALUE ('12345', 'Percy')
String before: INSERT into blah(`ID`, `Name`) VALUE ('', 'Percy')
String eval'd: INSERT into blah(`ID`, `Name`) VALUE ('', 'Percy')

Once you have the value of $insertID in your script, you can then run the eval to turn $insertID into a variable, which will then be interpolated.