Creating temporary mysql tables

timbarnett

I have had some developers do a search engine for me where they supposedly produce temp tables to sort keywords, only the temp tables are not temporary! So I had to delete some 3000 tables from a database that was 3GB!

How do I make these tables temporary??? I tried to use the TEMPORARY command, but it creates a table doesn't exist error. Running MySQL 3.23.51-Max.

Here's the code that creates the temp (?) tables:

// create temp table if it doesn't exist
$temptable1="ostemp1".$osisid;
$temptable2="ostemp2".$osisid;
if (!$s)
{
$createTempTable1 = "CREATE TABLE IF NOT EXISTS ".$temptable1." ( photoid int(11) NOT NULL default '0',collectionid int(11) NOT NULL default '0',keyword varchar(255) NOT NULL default '',KEY keyword (keyword)) TYPE=MyISAM;";
$createTempTable2 = "CREATE TABLE IF NOT EXISTS ".$temptable2." ( photoid int(11) NOT NULL default '0',collectionid int(11) NOT NULL default '0',keyword varchar(255) NOT NULL default '',KEY keyword (keyword)) TYPE=MyISAM;";
DBquery($createTempTable1); // create the temptable1
DBquery($createTempTable2); // create the temptable1
DBquery("delete from ".$temptable1); // empty temptable
DBquery("delete from ".$temptable2); // empty temptable

Should I use DROP TABLE somewhere? At the end of the search when the data is transferred to the dispaly page or at the end of session? If so how do I do that?
Thanks, Tim

timbarnett

Worked it out the problem here and solved the temporary table problem:

First using _connect, temporary tables are dropped each time a php script ends, or another mysql connection is made or a close statement is met.

My code had all 3 problems! It is very modular and written by more than one person...

First the DBquery function closed and then reopened the mysql connection every time a query was made (this was probably loading the server too.)

I removed the close statement associated with this function and made the connection pconnect, so it wasn't closed each time the php script ended and moved to another module.

In the other modules, I changed all the connect statements to pconnect so it will ignore the connection call and will rewrite all those statements to use the DBquery functions.

I now have temporary tables working.