My PHP-coded JOIN fails My SQL JOIN is fine as it works from the SQL command line - but while my PHP code will return one or more records from a single table, it fails when I JOIN with:

Uncaught Error: Call to a member function get_result() on array

My environment: Windows 8.1.6 10.4.24-MariaDB Apache/2.4.53 (Win64) libmysql - mysqlnd 8.1.6 OpenSSL/1.1.1n PHP/8.1.6

The following query works from the sql command line. It is the one I would like PHP to resolve for me:

SELECT productoptionsr.contentnamekey,content.contentname LEFT JOIN content ON productoptionsr.contentnamekey=content.contentnamekey FROM productoptionsr WHERE businessnamekey='8c9007ab5e9942a80b19887057511d7b1f3c5bbea8672dc03f4fd8291f6c18ab' AND ponamekey='c91bafa8aebfb760547c761110441790f263d3c23721a5034b4d26e97b231668' ORDER BY seq;

My PHP code (a library that I put together in part using code from php.net help text)

function DBExecute($DB, $sql)
{ // A wrapper to prepare, execute a query and return $stmt
  //////////////////////////////////////////////////////////////////////////////
  global $sql_request;
  $sql_request=$sql;
  //////////////////////////////////////////////////////////////////////////////
  try { $stmt = $DB->prepare($sql); }
  catch (Exception $e)
  { // Handle the error, something went wrong
    error_log($e->getMessage());
    return DBState($DB, [ "errno"=>$e->getCode(), "error"=>$e->getMessage() ]);
  }

  try { $stmt->execute(); }
  catch (Exception $e)
  { // Handle the error, something went wrong
    error_log($e->getMessage());
    return DBState($DB, [ "errno"=>$e->getCode(), "error"=>$e->getMessage() ]);
  }
  return $stmt;
}

function DBSelect($DB, $table, $selection, $conditions, $limit=0)
{ // A simple wrapper to prepare, select and return records
  global $receipt, $sql_request;
  if($limit>0)
  { $conditions="$conditions LIMIT $limit"; }
  $sql="SELECT $selection FROM $table WHERE $conditions";
  $sql_request=$sql;
  //////////////////////////////////////////////////////////////////////////////
  $result=[];
  try
  { $stmt=DBExecute($DB, $sql);

    if (!($res = $stmt->get_result()))
    { Abort("DBSelectData get_result() failed: (" . $stmt->errno . ") " . $stmt->error ." $sql"); }
    //////////////////////////////////////////////////////////////////////////////
    for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
        $res->data_seek($row_no);
        $result[]=$res->fetch_assoc();
    }
    //////////////////////////////////////////////////////////////////////////////
    // Reverse our reverse array to put it into correct order
    if(isset($result))
    {$result=array_reverse($result);}
    $stmt->close();
    return $result;
  } catch (Exception $e)
  {
    // Handle the error, something went wrong
    error_log($e->getMessage());
    return DBState($DB, [ "errno"=>$e->getCode(), "error"=>$e->getMessage() ]);
  }
  return true;
}

function DBState($DB=false, $stmt=false)
{ // Return state of play, give precedence to prepare/execute errno
  //////////////////////////////////////////////////////////////////////////////
  global $sql_request;
  //////////////////////////////////////////////////////////////////////////////
  if($stmt&&$stmt["errno"]>0)
  { return [ "affected_rows"=>0, "insert_id"=>0, "errno"=>$stmt["errno"], "error"=>$stmt["error"], "query"=>"$sql_request", "A"=>mt_rand(2,32767) ]; }

  return [ "affected_rows"=>mysqli_affected_rows($DB), "insert_id"=>mysqli_insert_id($DB), "errno"=>$DB->errno, "error"=>$DB->error, "query"=>"$sql_request", "B"=>mt_rand(2,32767) ];
}

function DBConnect($DBHOST, $DBUSER, $DBPASS, $DBINSTANCE)
{ // Connect to the db and return a pointer

  // Enable DB exceptions
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

  // Connect
  $DB=new mysqli("$DBHOST", "$DBUSER", "$DBPASS", "$DBINSTANCE");
  if($DB->connect_errno)
  {
    $report["DBHOST"]="$DBHOST";
    $report["DBUSER"]="$DBUSER";
    if(mb_strlen("$DBPASS")>4&&"$DBPASS"!=="false")
    { $report["DBUSER"]="true"; } else { $report["DBUSER"]="false"; }
    $report["DBINSTANCE"]="$DBHOST";
    $report["errno"]=$DB->connect_errno;
    $report["error"]="Connect error: ".$DB->connect_error;
    Abort( json_encode( $report, JSON_PRETTY_PRINT) );
  }
  // Set char set
  mysqli_set_charset($DB, 'utf8mb4');
  return $DB;
}

function DBClose($DB)
{ // Disconnect from db
  $result=DBState($DB, [ "errno"=>0, "error"=>"" ]);
  $DB->close();
  return $result;
}

DBSelect($DB, "$TABLE[PRODUCTOPTIONSR]",
    "PRODUCTOPTIONSR.contentnamekey, CONTENT.contentname INNER JOIN CONTENT ON PRODUCTOPTIONSR.contentnamekey=CONTENT.contentnamekey",
    "businessnamekey='8c9007ab5e9942a80b19887057511d7b1f3c5bbea8672dc03f4fd8291f6c18ab' AND ponamekey='c91bafa8aebfb760547c761110441790f263d3c23721a5034b4d26e97b231668' ORDER BY seq");

CodePudding user response：

Your code is very messy due to all the superfluous error checking, which has led you to your current problem. You are catching the error and then returning an array from DBState which then bubbles up to DBExecute. Thus, somewhere there you have a suppressed error which causes your code to execute get_result on an array. Fix your return types and remove all this error checking.

Here's your code after a slight tyding up:

<?php

/**
 * A wrapper to prepare, execute a query and return $stmt
 */
function DBExecute($DB, $sql): mysqli_stmt
{
    $stmt = $DB->prepare($sql);
    $stmt->execute();
    return $stmt;
}

/**
 * A simple wrapper to prepare, select and return records
 */
function DBSelect($DB, $table, $selection, $conditions, $limit = 0): array
{
    if ($limit > 0) {
        $conditions = "$conditions LIMIT $limit";
    }
    $sql = "SELECT $selection FROM $table WHERE $conditions";

    $stmt = DBExecute($DB, $sql);

    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

/**
 * Connect to the db and return a pointer
 */
function DBConnect($DBHOST, $DBUSER, $DBPASS, $DBINSTANCE): mysqli
{
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    $DB = new mysqli("$DBHOST", "$DBUSER", "$DBPASS", "$DBINSTANCE");
    $DB->set_charset('utf8mb4');
    return $DB;
}

It's far from perfect, but at least now the actual error will show up in your error logs. It's still missing parameter type specifications.

Lesson learned: be very careful with try-catch. Use it only if you are sure that you actually need it.