php - Codeigniter Call to a member function row() on bool when using stored procedure in mysql

Question

I am trying to get a result set from a stored procedure using codeigniter in order to shorten the amount of call in my models.

MYSQL Stored Proc

CREATE DEFINER=`dbsdecks_mark`@`97.119.%` PROCEDURE `all_cards`()
BEGIN
  -- drop the temporary table for clean up
    DROP TEMPORARY TABLE IF EXISTS cardList;

    -- create temporary table holding unique product ids, card numbers, and group ids for one of each card in the game no reprints or duplicates
    CREATE TEMPORARY TABLE cardList
    Select DISTINCT 
   `Number` as cardNumber, 
        MIN(productId) as productId,
        MIN(groupId) as groupId
    from tcgplayer_card
    Where `Rarity` NOT IN ('None', 'Special Rare', 'Merit', 'Token') AND `Number` NOT IN ('0') 
    and regexp_like(`Number`,'^[A-Z0-9]{0,4}-[A-Z0-9]{0,3}$')
    GROUP BY `Number`
    ORDER BY `Number`;
    
    -- select the master list based off of that temporary table
    SELECT 
    cl.*,
    `tcg`.`Rarity` as rarity,
    `tcg`.`Description` as cardText,
    `tcg`.`CardType` as cardType,
    `tcg`.`Color` as color,
    `tcg`.`EnergyColorCost` as energyCost,
    `tcg`.`SpecialTrait` as specialTrait,
    `tcg`.`Power` as power,
    `tcg`.`ComboPower` as comboPower,
    `tcg`.`ComboEnergy` as comboEnergy,
    `tcg`.`Era` as era,
    `tcg`.`Character` as cardCharacter,
    tcg.url,
    tcg.imageUrl
    from cardList cl
    INNER JOIN tcgplayer_card tcg ON cl.productId = tcg.productId;
END

CI function

public function get_all_cards() {
    $query = $this->db->query("CALL ALL_CARDS");
    return $query->result();
}

By all accounts I am doing things correctly. Here is the error that I am getting thrown

PHP Fatal error: Uncaught Error: Call to a member function row() on bool in C:inetpubwwwrootapisystemlibrariesSessiondriversSession_database_driver.php:399

question from:https://stackoverflow.com/questions/65865436/codeigniter-call-to-a-member-function-row-on-bool-when-using-stored-procedure

Answer 1

The call to $this->db->query might return false if you have an error inside the stored procedure.
Compare this answer: Call to a member function execute() on boolean in

public function get_all_cards() {
   try {
     $query = $this->db->query("CALL ALL_CARDS");
     return $query->result();
   } catch ($err) {
     echo $err;
   }
}

Edit: I think the first Select Query inside the procedure returns an error, the exception occurs in the second Select Query