Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
tstimple
Forum Commoner
Posts: 53 Joined: Wed Jan 21, 2004 10:12 pm
Post
by tstimple » Wed Feb 11, 2004 8:33 pm
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
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Wed Feb 11, 2004 8:39 pm
Use the SHOW TABLES LIKE 'foo' syntax/command ?
tstimple
Forum Commoner
Posts: 53 Joined: Wed Jan 21, 2004 10:12 pm
Post
by tstimple » Wed Feb 11, 2004 10:29 pm
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
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Thu Feb 12, 2004 3:05 pm
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 » Thu Feb 12, 2004 5:04 pm
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'){....
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Thu Feb 12, 2004 5:09 pm
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 » Thu Feb 12, 2004 5:17 pm
Thanks,
That's exactly what I was looking for.
--Tim