0

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:\inetpub\wwwroot\api\system\libraries\Session\drivers\Session_database_driver.php:399

Mark Hill
  • 1,769
  • 2
  • 18
  • 33
  • It seems rather unusual that you are getting a session related error. If you don't call your get_all_cards() does this error "go away"? – TimBrownlaw Jan 23 '21 at 23:22
  • @TimBrownlaw yes – Mark Hill Jan 23 '21 at 23:43
  • what are your CI and php versions? – Vickel Jan 23 '21 at 23:58
  • @Vickel my CI version is 3.10 and my php version is 7.4 – Mark Hill Jan 24 '21 at 20:21
  • 1
    there were some session issues reported with earlier versions of CI 3.x, I'm not sure if they were resolved with 3.1.10, the latest version is 3.1.11, update to this latest version and see if the error persist – Vickel Jan 24 '21 at 20:24
  • you also could have an error in your query. If $query is FALSE, which is a boolean, it will trigger the error. So you need to check an eventual error in your query – Vickel Jan 24 '21 at 20:29
  • @Vickel there are no errors in the query. I achieve a result set when it is executed. So I'm going to guess that it is probably a CI issue. – Mark Hill Jan 24 '21 at 20:30
  • what if you set in your config: `$config['sess_driver'] = 'files';`, does the error show? – Vickel Jan 24 '21 at 20:42
  • @Vickel that worked and I don't know why – Mark Hill Jan 26 '21 at 03:19
  • It worked, because you stopped using database driver, which looks like having caused the error. I (unsuccessfully) tried to reproduce your error, using database driver, but it works on my machine (CI3.1.11, php 7.3.20 x 64, MySQL 5.7.17 x86 on Apache 2.4.43). Could you please show your config for sessions, the database scheme and your server setup, thanks – Vickel Jan 26 '21 at 12:35
  • I also saw, that you might have a syntax error, but I'm not sure. Try: `$this->db->query("CALL ALL_CARDS()");` – Vickel Jan 26 '21 at 12:45

1 Answers1

0

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