IF EXISTS other table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tstimple
Forum Commoner
Posts: 53
Joined: Wed Jan 21, 2004 10:12 pm

IF EXISTS other table

Post by tstimple »

Lets say I have two mysql tables:
CurrentTable
NewTable

After NewTable has been created I have a script that DROPs CurrentTable
and then RENAMEs NewTable TO CurrentTable

Is there a way to check to be sure that NewTable EXISTS before I DROP CurrentTable?

The IF EXISTS TABLE syntax seems to only check if the table you want to drop exists (not check if some OTHER table exists)

I am a newbie, so if i read this wrong, can someone please tell me how to adjust the command to do what I need.

Thanks,
--Tim
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Use the SHOW TABLES LIKE 'foo' syntax/command ?
tstimple
Forum Commoner
Posts: 53
Joined: Wed Jan 21, 2004 10:12 pm

Post by tstimple »

The CREATE TABLE, DROP TABLE, and RENAME TABLE are all part of the same script (php page).
I don't think SHOW TABLE will help, will it?

--Tim
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Well if SHOW TABLES LIKE 'whatever' returns a result then you knwo the table exists and you can go ahead and do your create, drop rename etc.. if no results are returned then the table doesn't exist and you can do whatever you want to do in that situation.
tstimple
Forum Commoner
Posts: 53
Joined: Wed Jan 21, 2004 10:12 pm

Post by tstimple »

Will the SHOW TABLE return "false" if the table doesn't exist?
How would I code a line in my script to check for that so that the script will either continue (if true) or halt (if false)?

Can I put in something like

if(!SHOW TABLE LIKE 'NewTable'){....
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Here's a snippet that might help clarify it ..

Code: Select all

<?php
$db = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('test');
$sql = "SHOW TABLES LIKE 'foo'";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res)){
  echo 'Table exists';
} else {
  echo 'Table doesn''t exist';
}
?>
tstimple
Forum Commoner
Posts: 53
Joined: Wed Jan 21, 2004 10:12 pm

Post by tstimple »

Thanks,

That's exactly what I was looking for.

--Tim
Post Reply