php - 在 codeigniter 中处理存储过程输出参数

Question

我在 mysql 中有一个带有输出参数的存储过程，我需要将结果发送到我使用 codeigniter 的 php

注册模式

  public function registerUser($data){

            $sql = "call register_new_user('" . $data['uemail'] . "','" . $data['fname'] . "','".$data['lname'] . "','" . $data['pword']."',
            '" . $data['states'] . "',".$data['chargifyId'].");";

            if ($query = $this->db->query($sql)) {

            return  $this->db->query("Select @last_id");               

            } else {
                return false;
            }

    }

这是存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `register_new_user`(IN `uemail` VARCHAR(100), IN `fname` VARCHAR(45),
IN `lname` VARCHAR(45), IN `pword` VARCHAR(100), IN `states` VARCHAR(20), IN `chargifyid` INT, OUT `last_id` INT)
    NO SQL
BEGIN

if not exists (SELECT * from gi_user where 
(gi_user_email = uemail)) THEN

INSERT INTO gi_user(gi_user_email, gi_user_fname, 
gi_user_lname, gi_user_password,gi_user_status, 
gi_account_id, chargifyId) 
VALUES (uemail,fname,lname,pword,states,1,chargifyid);

SET last_id = LAST_INSERT_ID( ) ;

END IF;


END

Answer 1

使用CodeIgniter 交易

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();

例子:

$SQL = "call UPDATE("
                . $ID.","
                . $this->db->escape($data["NAME"])."," // input
                . $this->db->escape($data["DESCRIPTION"])."," // input
                . " @out1" ."," // output
                . " @out2" //output
                .");";


        $this->db->trans_start();
            $this->db->query($SQL); // not need to get output
            $query = $this->db->query("SELECT @out1 as row_1,  @out2 as row_2");
        $this->db->trans_complete();

        $result = array();

        if($query->num_rows() > 0)
            $result = $query->result_array();

        print_r($result);