having trouble creating a temporary table

[earlparkerii]

I'm having some problems with creating a temporary table with PHP. The code is:

$query_create_temp = "CREATE TEMPORARY TABLE IF NOT EXISTS temp
(
quantity smallint(4) not null default '0.00',
part_no varchar(20) not null default ' ',
part_description varchar(60) not null default ' ',
price_1_9 float(6,2) not null default '0.00'
)";

mysql_query($query_create_temp) or die("Could not run query_create_temp. ". mysql_error());

I've observed the following:

1. I can remove the word 'temporary' from the code above and the table is created. I can add, update and delete records in said table.

2. I can create a temporary table in SQL (using SQLyog) and perform add, update and delete functions, so it would seem temporary table permissions aren't a problem.

The interesting thing is that when the table creation code executes that no error message is returned. Further, I can add a record and no error message is returned. However, when I try to update a record I get "Table 'database.temp' doesn't exist". Remember, all of this code works fine until the word 'temporary' is added.

Does anyone have any ideas?

[ahundiak]

Are you trying to update it from a different page? Temp table are connection based and you cannot pass a connection from one page to another.

[earlparkerii]

Thank you for replying- you may have hit the nail on the head. I've been doing more reading and was starting to wonder if the problem was connection related.

Currently the contents of 'update_item.php' is sent to 'change_item.php', where the table is actually updated, then a redirect returns the user to the 'update_item.php'. This is likely where the connection is going away.

However, if I move the table update code to 'update_item.php' and simply reload the page, would the connection be lost anyway?

[Drakla]

God, that's so freaky. I had a temp table creation problem not ten minutes ago. I found out that in MySQL 4 and above you have to have the create temporary privelege granted. Clearly I didn't

I used Mysqlcc to turn it on in the user administration section, but you can probably just knock up a query if you don't have that.

[earlparkerii]

Here we are, five time zones apart, with the same problem. How small the world has become.

While I'll certainly check into it, I suspect that my problem isn't permissions related. I can create the table easily in SQL and perform all the functions one would normally perform on a table. The problem only occurs when I get into PHP. By the way this is on a remote server and I doubt I'll be able to change permissions.

Feel free to argue if I'm missing something.