Welcome Guest, Not a member yet? Register   Sign In
What is the best way to Create a Temporary Table?
#1

[eluser]Unknown[/eluser]
Hello.

I'm using Codeigniter to create a small website on a subdomain. It will interogate a database on the main site, but I don't want to touch the master table, so I'd like to Create a Temporary Table.

I notice that pconnect in config.php defaults to TRUE, so I assume it is best to leave that as it is - with a persistent connection?

Where therefore is the best place to put the code to Create a Temporary Table, so as to avoid the 'Table already exists errors'?

Thanks for your help.

:coolsmile: UPDATE :coolsmile:

What I did originally was put a function in the model and then call it in the constructor, but this came back with a 'Table already exists error' because of Codeigniter's persistent connection.

I was wondering if there was somewhere to put this function so that it would be called once. I was thinking of a hook, but wasn't sure where to have it fire. I'd ideally have liked to have access to $this->db but as far as I can tell anything that fired early enough would not have $this->db.

So in the end I started my function in the model with

Code:
$sql = "DROP TABLE IF EXISTS `my_temp_table`;";
$query = $this->db->query($sql);

Now that I think about it, this is preferable, as dropping and recreating that table will ensure users will get an up to date table, rather than one which might become quite out of date, due to Codeigniter's persistent connection.

The message therefore is: if you need to Create a Temporary Table, make sure to drop it if it already exists.


#2

[eluser]dhazer[/eluser]
Trying to figure that out myself.
#3

[eluser]vinofilus[/eluser]
I found your suggestion to drop the table helpful, but I can't figure out why I need to.

My db config file says:
Code:
$db['default']['pconnect'] = FALSE;

and if I start the function in my model with:
Code:
$this->db->query("CREATE TEMPORARY TABLE Masterinfo AS (select S.first_name, ...
I get an error that says "Table 'Masterinfo' already exists"

Even if I change the table name, I get that error (with the changed name) on first access, not just the second time I run it. With pconnect off and making a TEMPORARY table, I'm not clear what the problem is.

But as you said, if I start my function with:
Code:
$this->db->query("DROP TABLE IF EXISTS Masterinfo");
$this->db->query("CREATE TEMPORARY TABLE Masterinfo AS (select S.first_name, S.las...

It runs fine.

So I suppose that's a solution, but I'm always nervous about fixes that don't make sense, because I don't know when or why they will come back to haunt me.




Theme © iAndrew 2016 - Forum software by © MyBB