Page 1 of 1

IF EXISTS other table

Posted: Wed Feb 11, 2004 8:33 pm
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

Posted: Wed Feb 11, 2004 8:39 pm
by markl999
Use the SHOW TABLES LIKE 'foo' syntax/command ?

Posted: Wed Feb 11, 2004 10:29 pm
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

Posted: Thu Feb 12, 2004 3:05 pm
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.

Posted: Thu Feb 12, 2004 5:04 pm
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'){....

Posted: Thu Feb 12, 2004 5:09 pm
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';
}
?>

Posted: Thu Feb 12, 2004 5:17 pm
by tstimple
Thanks,

That's exactly what I was looking for.

--Tim