temporary tables

tomhilton

Hi,

I am using the following PHP functions to generate table names for my temporary tables:
$temptable=md5(uniqid(rand(),1)

Problem is that about 5% of the time, it generates mysql error messages because of the table name.

"You have an error in your SQL syntax near '7e7dc94bf8882321d25a9469c8ef71a5" is an example of the last one. Does mysql have restrictions on table names beginning with a number instead of alpha character? I don't understand what is causing the syntax error in the example above.

Does someone have suggestions on ways to generate temporary table names quickly without fear of duplicate names.

Lars_Berg

Does mysql have restrictions on table names beginning with a number instead of alpha character?

Yes.

Why not use

create temporary table

[deleted]

You don't have to generate names, the temporary tables exist 'per connection':

"This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name."

http://www.mysql.com/doc/en/CREATE_TABLE.html

tomhilton

Hi again,

Thanks for your help, I can't find in the mysql manual the specs for creating a temporary table without giving it a name. The name parameter isn't shown as an optional parameter. I tried to create one without naming it and below is the input and output.

mysql_query("CREATE TEMPORARY TABLE (id INT(11), committee_id INT(11), fname VARCHAR(40), lname VARCHAR(40), index id(id))");

MYSQL ERROR:1064 You have an error in your SQL syntax near '(id INT(11), committee_id INT(11), fname VARCHAR(40), lname VARCHAR(40), index i' at line 1

What is the query command to create a temporary table without naming it, and how do you pull information from it later

(ie; SELECT * FROM TEMPORARY TABLE ?)

also, I have some pages that create more than one temporary table, what then?

Frag

CREATE TEMPORARY TABLE temptable (etc...

You just give it any name that doesn't exsist already in your database. If 2 people are using the system at the same time MySQL knows whos temptable is whos.

Name it whatever you like. As long as that table doesn't exsist in your database already.

Frag

tomhilton

Thanks again, very simple, I don't know why I was thinking they had to be unique.