what is the error of this stored procedure

Question

hi i m using this stored procedure

DELIMITER $$
 DROP PROCEDURE IF EXISTS get_all_user_selected_children_of_preference$$

CREATE PROCEDURE get_all_user_selected_children_of_preference(
   IN entity_id INT,
   IN user_type INT,
   IN parent_id INT
)

BEGIN

     DECLARE profilepreferenceid INT;
     DECLARE inpreferenceid INT;
     DECLARE preference_parent_id INT;
     DECLARE prefvalue VARCHAR(50);
     DECLARE no_more_prefrences INT;
     DECLARE element_type INT;
     DECLARE preference_type INT;

     DECLARE cur CURSOR for  select ProfilePreferenceID,PreferenceParentID,PreferenceID,ProfilePreferenceValueAlias,ElementType,PreferenceType from xxxxx  WHERE PreferenceParentID=parent_id AND EntityID=entity_id AND UserType=user_type ;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_prefrences=1;

     CREATE TEMPORARY TABLE IF NOT EXISTS temp_user_selected_preference_children(

          ProfilePreferenceID int(11) NOT NULL AUTO_INCREMENT,
          PreferenceIDTmp INT(11),
          PreferenceParentIDTmp INT(11),
          ProfilePreferenceValueTmp varchar(255),
          userProfileIdTmp INT(11),
          elementTypeTmp INT (11),
          PreferenceTypeTmp INT (11),
          PRIMARY KEY (ProfilePreferenceID)     
     );

          SET no_more_prefrences=0;
          OPEN cur;

             dept_loop:WHILE(no_more_prefrences=0) DO

                     FETCH cur INTO profilepreferenceid,preference_parent_id,inpreferenceid,prefvalue,element_type,preference_type;
                     IF no_more_prefrences=1 THEN
                        LEAVE dept_loop;
                     END IF;

                                     INSERT  INTO temp_user_selected_preference_children(ProfilePreferenceID,PreferenceIDTmp,PreferenceParentIDTmp,ProfilePreferenceValueTmp,userProfileIdTmp,elementTypeTmp,PreferenceTypeTmp)
                                        VALUES  (profilepreferenceid,inpreferenceid,preference_parent_id,prefvalue,entity_id,element_type,preference_type);

                                     CALL get_all_user_selected_children_of_preference(entity_id,user_type,inpreferenceid);

             END WHILE dept_loop;

          CLOSE cur;    

END$$
DELIMITER ;

i run it in phpmyamin , it run successfully .

then i am calling it from codeigniter

public function get_user_selected_all_sub_preferencs_of_preference($preference_id,$user_type,$profile_id)
{
    $this->db->query("SET max_sp_recursion_depth=1000");
    $this->db->query("CALL get_all_user_selected_children_of_preference(".$profile_id.",".$user_type.",".$preference_id.")");
    $data= $this->db->query("SELECT * FROM temp_user_selected_preference_children");
    $dara_arr=$data->result_array();

    $this->db->query("DELETE FROM temp_user_selected_preference_children WHERE userProfileIdTmp=".$profile_id."");

    if(is_array($dara_arr)&&count(array_filter($dara_arr))>0){
        return $dara_arr;
    }
    else
    {
        return FALSE;
    }
}   

then it gives the error

A Database Error Occurred
Error Number: 1054

Unknown column 'PreferenceTypeTmp' in 'field list'

CALL get_all_user_selected_children_of_preference(65,2,1)

Filename: C:\xampp\htdocs\elephanti2\system\database\DB_driver.php

Line Number: 330

can not find what is this .

in mysql console , i describe table

please help . thanks in advance

Answer 1

As stated in the manual page on CREATE TABLE Syntax:

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

Therefore, the DESCRIBE command that you ran from the MySQL command-line client refers to a different table to that which is in use by CodeIgniter.

Since the stored procedure uses CREATE TEMPORARY TABLE IF NOT EXISTS, it only creates a new table if one of the same name does not already exist on that connection: perhaps CodeIgniter previously created a table of the same name but with different columns?

You might have meant to first drop any existing table and then perform a simple create:

DROP TEMPORARY TABLE IF EXISTS temp_user_selected_preference_children;
CREATE TEMPORARY TABLE temp_user_selected_preference_children (
-- etc.