Issue working with temporary table

Question

I enter the following into phpMyAdmin:

CREATE TEMPORARY TABLE test_table (
 item1 VARCHAR(50) NOT NULL
 , item2 DECIMAL(12,2) NOT NULL DEFAULT 0.00
 , item3 DECIMAL(7,2) NOT NULL DEFAULT 0.00
 , item4 INT UNSIGNED NOT NULL DEFAULT 0);

The table is successfully created.

Then I enter the following statement also into phpMyAdmin:

INSERT INTO test_table
 (item1, item2, item3, item4)
 VALUES
 ('lentils', 99.00, 82, 8);

And I receive; "#1146 - Table 'mydatabase.test_table' doesn't exist" Any clues as to what is wrong here?

Answer 1

Temporary tables only exist for the current PhpMyAdmin query as it closes your DB session once the query has been executed. If you are executing these two queries separately then you will get this error as the MySQL database will drop the temporary table right after the first query has finished executing.

What is the reason for this?

The reason is so you can create functions or procedures that create these temporary tables without clogging up your database as they will be automatically removed once the function or procedure has finished its execution.

The solution would be to execute everything in one query or just use a normal table.