Using temporary tables

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.

 

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Adjusting the priority queue >>

Previous chapter: Complex joins

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.