在代码初始化器中处理存储过程输出参数 [英] Handle stored procedure output parameter in codeigniter

问题描述

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

I have a stored procedure in mysql with a output parameter and i need to get the result to my php which is used codeigniter

注册模式

  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

推荐答案

使用 CodeIgniter交易

use CodeIgniter Transactions

$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);

这篇关于在代码初始化器中处理存储过程输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！