can temporary tables be used in PHP?

From my understanding each time a a new screen is displayed in CI3, a new session is created. In my app I keep a session variable structure to keep data throughout the app. But since temporary files get erased at the end of a session, can I assume that the temp file will be erased at the end of the session (the same user remains on the application)? There is no way to keep a handle to the temp file handle in the session variable? I know its name but I am not sure that will help once it is erased.
Do I not understand how temp file in mysql work?

proof that an old dog can learn new tricks

What is the temporary files?
How do you create these?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Here is the code that I run under CI3. Is CI 3 still supported?
        $this->db->query('DROP TABLE IF EXISTS BC_PR_list_temp');
        $sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(
                  taxyear  VARCHAR(10),
                  employee_email VARCHAR(80),
                  employee  VARCHAR(80),       
                  qualified_dollars int NOT NULL,
                  title varchar(80),
                  role varchar(80),
                  num_BC int NOT NULL,
                  num_PR int NOT NULL,
                  tipe varchar(4),
                  item varchar(80),
                  dollarsPerBC int NOT NULL,
                  dollarsPerPR int NOT NULL,
                  business_component_string varchar(120),
                  project_string varchar(120),
                  CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
                            )";

        $iRc=$this->db->query($sql);
        if ( ! $iRc)
        {
            $error = $this->db->error(); // Has keys 'code' and 'message'
        }
        if (!$this->db->table_exists('BC_PR_list_temp'))
        {
            $error = $this->db->error(); // Has keys 'code' and 'message'
        }
I am checking to see if the temp file sql call fails. It does not.  Then I check to see if the file was created. It was not. So then I copied the sql ONLY and pasted it directly in mysqlWorkBench and executed it I do not get an error so I assume it worked.

So the same code that fails to create a temp file under CI3, works under mysqlWorkBench. To see if it really worked I then run "table_exists('BC_PR_list_temp')" in mysqlWorkbench. But running that fails with "table_exists is not valid at this position."

So I don't know if it was created.

proof that an old dog can learn new tricks

I was just wondering, does CI3 support mySql 8?

---

I was just wondering, does CI3 support mySql 8.023 or RDS in general?

proof that an old dog can learn new tricks

Something is wrong with CI3. I can run this from mysqlWorkBench and it runs fine. But if I run it from CI3, the table is not created.
CREATE TEMPORARY TABLE BC_PR_list_temp(
                  id INT AUTO_INCREMENT primary key NOT NULL,
                  taxyear  VARCHAR(10),
                  employee_email VARCHAR(80),
                  employee  VARCHAR(80),       
                  qualified_dollars int NOT NULL DEFAULT 0,
                  title varchar(80),
                  role varchar(80),
                  num_BC int NOT NULL DEFAULT 0,
                  num_PR int NOT NULL DEFAULT 0,
                  tipe varchar(4),
                  item varchar(80),
                  dollarsPerBC int NOT NULL DEFAULT 0,
                  dollarsPerPR int NOT NULL DEFAULT 0,
                  business_component_string varchar(120),
                  project_string varchar(120),
                  CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
                            )

Is there a log somewhere?

---

Something is wrong with CI3 and temporary tables on RDS.

 I can run this code below from mysqlWorkBench and it runs fine. But if I run it from CI3, the table is not created.
CREATE TEMPORARY TABLE BC_PR_list_temp(
                  id INT AUTO_INCREMENT primary key NOT NULL,
                  taxyear  VARCHAR(10),
                  employee_email VARCHAR(80),
                  employee  VARCHAR(80),       
                  qualified_dollars int NOT NULL DEFAULT 0,
                  title varchar(80),
                  role varchar(80),
                  num_BC int NOT NULL DEFAULT 0,
                  num_PR int NOT NULL DEFAULT 0,
                  tipe varchar(4),
                  item varchar(80),
                  dollarsPerBC int NOT NULL DEFAULT 0,
                  dollarsPerPR int NOT NULL DEFAULT 0,
                  business_component_string varchar(120),
                  project_string varchar(120),
                  CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
                            )

Is there a log somewhere?
I am starting to think that the issue is due to a temp table not existing across different sessions. But what is a session in php? The code is in a few functions in my model. Is each function a different session?

How about upgrading to CI 3.1.12? How can I do that?

proof that an old dog can learn new tricks

I am very disappointed in the lack of responses. I spent 4 or 5 years writing this code and now it seems  that there is a bug  in CI3 dealing with TEMP files and not a single response. I had never heard of temporary files until I was told of their existence up here. Based my architecture on them. Now I discover in load testing that they don't work. But clearly there is either a problem with Temp files across "sessions" or a general CI bug in creating them. I can create one fine in mysqlWorkBench which makes it pretty clear that the issue is with CI3.

proof that an old dog can learn new tricks

If you want response in a small period of time you should hire someone with the knowledge on how to fix the issues....
It's a forum were people help each other, not a free helpdesk to solve your issue in a small period of time.

Now for the question:
Do you have an example of the code you are running now inside CI3?

Also for debugging (hint: at the end of the page): https://codeigniter.com/userguide3/datab...eries.html

I have spent bundles of $ hiring "someone". The is no one other than the CI staff who can answer this. The AWS support people (who I already pay) have no freakin' idea. This is a CodeIgniter specific issue. And yes, I use that same error setup as in your link. And yes, CI doesn't generate an error. The code is above. First I check for an error and there is none. Then I check to see if the table was created, it was not. Same code works on mysqlWorkbench.

proof that an old dog can learn new tricks

Your temporary table is created but as the database connection closes automatically, the table is deleted. That’s why you don’t get ‘false’ creating the temp table. Temp tables are nice to use in queries or stored procedures but I would personally never use them during a user session.

Thanks. I am not sure why the connection would be closed automatically? Moving forward, I switched back to TRYING to use the TEMPORARY tables, because of the particular architecture. 
This is the next statement after the CREATE. I am getting an error at line one. I think it has to do with the autoincrement field? 

PHP Code:

$sql = "INSERT IGNORE INTO BC_PR_list_temp 0, srat.taxyear , srat.employee_email , srat.employee , srat.qualified_dollars 
            
,e.employee_title as title,e.role as role,e.num_BC ,e.num_PR,'BC','',srat.qualified_dollars/e.num_BC as dollarsPerBC,
                srat.qualified_dollars/e.num_PR as dollarsPerPR, e.business_component_string,e.project_string
                              
                FROM survey_results_activities_temp srat, employees e      
                WHERE campaign= ? AND email= ? AND e.employee_email=srat.employee_email AND srat.employee_email IN (SELECT srat.employee_email FROM employees WHERE FIND_IN_SET(?, e.business_component_string))";
   
 This the current create
$sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(
                  id int KEY AUTO_INCREMENT,
                  taxyear  VARCHAR(10) DEFAULT '',
                  employee_email VARCHAR(80) DEFAULT '',
                  employee  VARCHAR(80) DEFAULT '',        
                  qualified_dollars int NOT NULL, 
                  title varchar(80) DEFAULT '',
                  role varchar(80) DEFAULT '',
                  num_BC int NOT NULL,
                  num_PR int NOT NULL,
                  tipe varchar(4) DEFAULT '',
                  item varchar(80) DEFAULT '',
                  dollarsPerBC int NOT NULL,
                  dollarsPerPR int NOT NULL,
                  business_component_string varchar(120) DEFAULT '',
                  project_string varchar(120) DEFAULT '',
                  CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
                            )"; 


proof that an old dog can learn new tricks