MySQL 'SHOW TABLES' doesn't show temporary tables?

superwormy

I have a function call which creates a temporary table in MySQL, and then a second function call which operates on that temporary table.

I was hoping to do something like this in the second function call to ensure that the table was in fact actually created:

if (mysql_num_rows(mysql_query("SHOW TABLES LIKE 'my temp table name'")))
// do something
else
//error

However, apparently the MySQL SHOW TABLES statement doesn't show temporary tables. Is there another query that I can use to show temporary MySQL tables so I can do this check? Thanks.

mrhappiness

why can't you be sure that the table exists?
you have to create it in the very same script

if you need to check try DESC and check for an error?

Sxooter

superwormy wrote:
I have a function call which creates a temporary table in MySQL, and then a second function call which operates on that temporary table.

I was hoping to do something like this in the second function call to ensure that the table was in fact actually created:

Why not check the error code returned by the create statement being run. That should tell you if it got created or not.

superwormy

Thanks, but I think you're missing the point. The situation is that this temporary table is created by a function call, and then a second function call must access it.

But I might make the first function call in one part of the script, and a fellow developer might make the other function call in another part of the script ( a seperate include()'d file ) I don't have access to the returned value, nor do I have any way of guarenteeing that the other guy has successfully created/not dropped the table.

And this isn't a one-shot, page gets generated in 0.1 seconds and then its done kinda deal, its a back-end cron script that runs for about an hour... the individual function calls need the ability to check and ensure that this table still exsits at call-time.

So my question still stands, is there anyway to ask MySQL if this temporary table exists or not?

maxpup979

Im guessing that the show tables command isnt going to do it, and I have no idea how else, unless you are passing things between the two of you.

from the manual--
SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.

But Im no MySQL expert...what would happen if you tried to just do a create temporary table with the same tablename? should fail If the table exists, right?

And welcome back Sxooter...😉

Sxooter

maxpup979 wrote:
Im guessing that the show tables command isnt going to do it, and I have no idea how else, unless you are passing things between the two of you.

from the manual--
SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.

But Im no MySQL expert...what would happen if you tried to just do a create temporary table with the same tablename? should fail If the table exists, right?

And welcome back Sxooter...😉

I find it intereting that people bash the postgresql documentation, but honestly, I find it much easier to use to find this kind of thing in. Now, if you've never used a database before, maybe the mysql manual is easier for a beginner to use, but generally, if you know what you're looking for, you often get lost in it.

Oh, and nice to see you too, maxpup979!