Incorrect integer value: 'SELECT LAST_INSERT_ID()[0]' for column 'po_trans_id' at row 1

Question

trying to insert multiple input and get the last id of other table and insert in into this table for foreign key.

tried to remove from the loop and tried to use foor loop

if($result){

     $j = 0;

    foreach($_POST as $val){
      $po_trans_id        = "SELECT LAST_INSERT_ID()[$j]";
      $po_qty             = $_POST['po_qty'][$j];
      $po_unit            = $_POST['po_unit'][$j];
      $po_description     = $_POST['po_description'][$j];
      $po_unit_price      = $_POST['po_unit_price'][$j];
      $po_total_amount    = $_POST['po_total_amount'][$j];
      $payment_terms      = $_POST['paymentTerms'][$j];
      $user               = $_SESSION["username"][$j];
  $query = "INSERT INTO request_po (po_trans_id,po_qty,po_unit,po_description,po_unit_price,po_total_amount,totalPrice,user) VALUES ('$po_trans_id' , '$po_qty' , '$po_unit' , '$po_description' , '$po_unit_price' , '$po_total_amount' , '$totalPrice' , '$user')";
      $j++;
      $result = mysqli_multi_query($link, $query) or die(mysqli_error($link));
  }

id like to insert my last id to other table for relational database.

hi, this is for dev purpose only and trying to insert multiple row in a single click while getting the last id of the other table — Joff Pascual, Jun 10 '19 at 21:25
Uhhh, everything is for "dev purpose". This is one of the recommendation how you can fix your code. It is a bug in your code! — Dharman, Jun 10 '19 at 21:28

tadman · Answer 1 · 2019-06-11T15:45:55.670

1

LAST INSERT_ID() is valid SQL. LAST_INSERT_ID()[0] is not, that's PHP notation and has no place in SQL.

What you want is available as insert_id through mysqli itself. You must ensure that each command completed correctly before proceeding or you will potentially create a mess in your database that's difficult to unwind.

To fix this, keep in mind the following:

DO NOT use mysqli_multi_query. This command does not support placeholders and cannot be secured properly.

What you want is to convert this to proper mysqli with prepared statements:

<?php
if ($result) {
  $j = 0;
  $count = count($_POST['po_qty']);

  // Use insert_id property
  $po_trans_id = $link->insert_id;

  $stmt = $link->prepare("INSERT INTO request_po (po_trans_id,po_qty,po_unit,po_description,po_unit_price,po_total_amount,totalPrice,user) VALUES (? , ?, ?, ?, ?, ?, ?, ?)");

  for ($j = 0; $j < $count; $j++) {
    $stmt->bind_param('sssssss',
      $po_trans_id,
      $_POST['po_qty'][$j],
      $_POST['po_unit'][$j],
      $_POST['po_description'][$j],
      $_POST['po_unit_price'][$j],
      $_POST['po_total_amount'][$j],
      $_POST['paymentTerms'][$j],
      $_SESSION["username"][$j]
    );
    $stmt->execute();
  }
}
?>

Where that statement is prepared once and run many times. If you enable exceptions then you can avoid the or die(...) anti-pattern as well.

edited Jun 11 '19 at 15:45

answered Jun 10 '19 at 21:29

tadman

208,517
23
234
262

hi tadman, here's the error "Fatal error: Uncaught Error: Call to a member function execute() on bool in /opt/lampp/htdocs/myHome-IMS/PO-add.php:47 Stack trace: #0 {main} thrown in /opt/lampp/htdocs/myHome-IMS/PO-add.php on line 47" – Joff Pascual Jun 10 '19 at 21:59
1

i notice there's a spurios single quote at the end of the SQL text, right before the close paren. so the `prepare` failed with a syntax error, and returned FALSE. then the code tries to call `execute`of FALSE (a boolean object, rather than a MySQL statement; – spencer7593 Jun 10 '19 at 22:21
@spencer7593 Good catch. I was trying to convert those to placeholders and missed that. – tadman Jun 10 '19 at 22:36
hi @tadman, after executing your code here's the error "mysqli_stmt::execute() expects exactly 0 parameters, 9 given in /opt/lampp/htdocs/myHome-IMS/PO-add.php on line 55" – Joff Pascual Jun 11 '19 at 13:49
Was in a bit of a rush to adapt that code and made a mistake, using the more PDO style by accident, so I've corrected this with the right code. This is all covered [in the manual](https://www.php.net/manual/en/book.mysqli.php) as well. – tadman Jun 11 '19 at 15:46
Hi, it has no error and my alert says its already inserted in database, and I've check the table and I think the data is not inserted. – Joff Pascual Jun 12 '19 at 00:22
hey! its already inserting but only in one row... I've inserted multiple data. – Joff Pascual Jun 12 '19 at 00:34
You may need to do additional debugging to get this to fully work, or frame whatever new problems you have in a new question. – tadman Jun 12 '19 at 16:09

Incorrect integer value: 'SELECT LAST_INSERT_ID()[0]' for column 'po_trans_id' at row 1

1 Answers1