How to select id to use it in insert query with multiple data?

Question

I have this query,

$cellId = $db->insert_multiple_data("INSERT INTO tbl_attendance",
        array('tbl_worksheets_id','tbl_students_id','status','class_dt'),
        $values_array);

Where $values_array is,

$values_array[] = array($id1,$id2,$status,$datetime);

It is in the for loop.

Here is now my insert_multiple_data(),

public function insert_multiple_data($sql,$fields,$values_array){
    $set = '';
    $str = "";
    $comma_separated2 = implode(",", $fields);
    foreach($values_array as $values){
        $comma_separated = implode("','", $values);
        $comma_separated = "'".$comma_separated."'";
        $str = $str."(".$comma_separated."),";
    }
    $sql = $sql."(".$comma_separated2.") VALUES ".rtrim($str, ",");
    $stm = $this->conn->prepare($sql);
    $stm->execute();
    return $this->conn->lastInsertId();
}

So these codes above are working. What I need is to make the status an id, how can I select the id of the status(string) from the database? then insert with the query above?

score 1 · Accepted Answer · edited May 23 '17 at 12:16

See this answer here: MySQL LAST_INSERT_ID() used with multiple records INSERT statement

It is not possible, because MySQL was designed to be able to work with replication. And if multiple IDs can be returned from a long insert then that could make the process non-deterministic. (So it always returns the first only)

Another thing: your code is not safe (from SQL injection attacks). It is using prepared statements, but without using the parameters. The values are just inserted into the string. Either use prepared statements with parameters, or do escaping with mysqli::real_escape_string. (Escaping is only safe if both the DB connection and the PHP environment is set to the same encoding, e.g. UTF-8.)

How to select id to use it in insert query with multiple data?

1 Answers1