Hudzilla.org - the homepage of Paul Hudson

9.10 Using temporary tables

This is NOT the latest copy of this book; click here for the latest version.

As I hope you have realised, using SQL is the fastest and easiest way to manipulate data - you can add information, order, and filter, all by knowing a few basic SQL commands. Very often, though, you need more - you might, for example, need to pass over data more than once to get the result you want, and doing that with PHP is clumsy and slow.

However, there is a solution to hand: MySQL allows you to create temporary tables to store data that work in precisely the same manner as normal tables except they are automatically deleted when your connection to the server is closed (or when your script ends). While "alive", temporary tables work in the same manner as a normal table - you can add and select data as you would do with any table. Furthermore, you can create a temporary tables with the same name as an existing, normal table - MySQL will hide the existing table, and create a temporary table in its place. As soon as the connection closes, the temporary table ceases to exist and the normal table comes back.

To create a temporary table, simply add the word "TEMPORARY" between "CREATE" and "TABLE", followed by the same definition you would otherwise have used, for example: CREATE TEMPORARY TABLE TempTable (ID INT).

Author's Note: The exact way of creating temporary tables varies between databases. For example, using Microsoft SQL Server you specify a table is temporary by prefixing its name with a # when creating it, for example: CREATE TABLE #mytable.

Temporary tables are only visible to the connection that created them, meaning that if you create a temporary table "foo", hiding a permanent table "foo", everyone else will see the real table, whereas you will see your temporary version. Note that as soon as you drop your temporary foo, your permanent table will automatically become visible again.

Author's Note: apart from allowing you much more control over your querying, temporary tables also allow you to create handy scratch tables where you can test out queries on non-live tables without affecting everyone else

<< 9.9.1 Complex joins		9.11 Adjusting the priority queue >>
Table of Contents

Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.

My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!

A PHP User - 07 Sep 2008

MYSQL accepts :

create temporary table <TABLENAME>

as ( <SELECT STATEMENT > )

so create table foo as (select ID,FirstName from bar);

works just fine.

A PHP User - 07 Sep 2008

Temporary tables are very useful to avoid using very complex joins and sorting data in the same query if you are new to MySQL.

Just SELECT INSERT into your temp table and then query it. You can easily sort using a regular query.

Beware of duplicate results when inserting the data in the temp table, though.

Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.