msqli stmt-> bind error'无法通过引用传递参数2

Question

$arr = array (
        array ('item'=>'Salt', 'on_hand'=>2, 'cost'=>3.29, 'format'=>'box'),
        array ('item'=>'Pepper', 'on_hand'=>1, 'cost'=>2.19, 'format'=>'bag'),
        array ('item'=>'Cinnamon', 'on_hand'=>1, 'cost'=>1.55, 'format'=>'shaker'),
        );
  $sql = "INSERT INTO item_list VALUES (?, ?, ?, ?, ?)";
  if (!($stmt=$db->prepare($sql))) {
    echo "<br />Prepare failed: (".$db->errno.") ".$db->error; }
  foreach($arr as $key) {
    $item = $key['item'];
    $on_hand = $key['on_hand'];                    // format 1. $item or format 2. $key[;item']
    $cost = $key['cost'];                          // echo works with both formats...
    $format = $key['format'];                      // stmt->bind neither format works...
    echo "<br>$item $on_hand $cost $format<br>";   // first var is auto-increment *NULL*
    if (!$stmt->bind_param("isids", null, $key["item"], $key["on_hand"], $key["cost"], $key["format"])) {
        echo "<br />Binding failed: (".$stmt->errno.") ".$stmt->error; }
    if(!$stmt->execute()) {
      echo "<br />Execute failed: (".$stmt->errno.") ".$stmt->error; }
  }

Constanly get this error - Fatal error: Cannot pass parameter 2 by reference in ... Nothing seems to work - can it be something with my server settings? I have seen this exact 'stmt->bind_param() format on this forum and the person said it worked... PS: need to get it from the db also so if you can show me how to get it back, it would be great Best forum on the web - up to date answers that actually work (so far) It has been of great help to me... Thnx a million Also tried stmt->bind_param() outside of loop... with format #1

Answer 1

After hours of searching and testing, and testing, and testing, and testing... finally figured out how to use REFLECTION. Hope this helps someone else out there!
PS: The table's 1st row item is set to AutoIncrement and not included in the 'prepare' binding template.

  // Make sure error reporting on full - last setting of this func may affect all others using mysqli.
  // To be safe always call mysqli_report(MYSQLI_REPORT_OFF) at the end of the script.
  mysqli_report(MYSQLI_REPORT_ALL);

  // Multi dimensional associative array that will be reflected
  $ref_arr = array (  // Note the 'sids' mimics the stmt->bind_param() syntax
        array ('sids', 'item'=>'Salt', 'on_hand'=>2, 'cost'=>3.29, 'format'=>'box'),
        array ('sids', 'item'=>'Pepper', 'on_hand'=>1, 'cost'=>2.19, 'format'=>'bag'),
        array ('sids', 'item'=>'Cinnamon', 'on_hand'=>1, 'cost'=>1.55, 'format'=>'shaker'),
        );

  // Insert all the items/rows using Prepared Statement & Reflection
  if (!($res = $db->prepare("INSERT INTO item_list SET  item=?, cnt=?, cost=?, format=?"))) {
    echo "<br/>Prepare failed: (".$db->errno.") ".$db->error;
    return; }
  $ref = new ReflectionClass('mysqli_stmt');  // Instantiate the reflection class
  $method = $ref->getMethod("bind_param");  // Set the binding method
  foreach($ref_arr as $arr) { // Loop through the multi dimensional associative array
    $method->invokeArgs($res,$arr);
    $res->execute();
  }