Insert multiple rows using a prepared statement

Question

I am trying to use the prepared statement query to insert multiple rows in to a table. I need to retrieve 2 values from a second table and insert them along with 3 parameters.

MySQL only sees the sub query as a single value not the 2 retrieved so asks for a further parameter to be added as only four are present.

$stmt = $mysqli->prepare("INSERT INTO tbl_permit_daily_sign_off (permit_id, shift, employee_id, sign_off_date, type)
                              VALUES ((SELECT tbl_permit_project.permit_id, tbl_shifts.shift_name FROM tbl_permit_project
                              INNER JOIN tbl_shifts ON tbl_permit_project.project_id = tbl_shifts.project_id
                            WHERE tbl_shifts.end_time <= NOW()), ?, ?, ?)");
$stmt->bind_param("iss",  $permit_employee = 0, $today, $type = "auto");
$stmt->execute();
$stmt->close();

$type = 'auto' as a parameter is irregular as far as PHP goes. That variable doesn't matter, just pass 'auto' directly. — tadman
– tadman, Commented Aug 2, 2018 at 20:52

O. Jones · Accepted Answer · 2018-08-02 19:57:57Z

1

You need the INSERT ... SELECT form of insert, not the INSERT ... VALUES() form.

INSERT INTO tbl_permit_daily_sign_off 
            (permit_id, shift, employee_id, sign_off_date, type)
     SELECT tbl_permit_project.permit_id, tbl_shifts.shift_name  
      FROM tbl_permit_project
INNER JOIN tbl_shifts ON tbl_permit_project.project_id = tbl_shifts.project_id
     WHERE tbl_shifts.end_time <= NOW()), ?, ?, ?)

How does this work?

1) you design a SELECT query giving the columns you want to use in your INSERT query. Then you test and troubleshoot it.

2) You put INSERT INTO tbl (col, col, col...) right before your SELECT.

Your INSERT chooses the columns (permit_id, shift, employee_id, sign_off_date, type) but your SELECT only mentions two of those columns. You need the SELECT to mention all five.

answered Aug 2, 2018 at 19:57

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert multiple rows using a prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related