Use php to check if a table exists

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Use php to check if a table exists

Post by Citizen »

I'm creating a php install file to setup a database.

How do I check to see if the table already exists?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

EXISTS is the key word you're looking for

http://dev.mysql.com/doc/refman/5.0/en/ ... table.html
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

There's probably a proper way to do this, but if you want a quick workaround why not run count the records in the table name you want to check for existance, if it returns > 1 then it's there. if it returns anything else drop the table (but don't die/exit on failure) and then (re) create it.

OK now I feel stupid, look up ^^^
User avatar
mattcooper
Forum Contributor
Posts: 210
Joined: Thu Mar 17, 2005 5:51 am
Location: London, UK

Post by mattcooper »

Here's one I developed today to do just that:

Code: Select all

//Verifies that a MySQL table exists
$adstable = "{$ezine_id}_adverts";
function fnTableExists($adstable) {
include "path/to/connection/script";
if (!$oConn = mysql_connect($host,$uname,$pword)) {
$bRetVal = FALSE;
} else {
$bRetVal = FALSE;
$result = mysql_list_tables('database_name', $oConn);
while ($row=mysql_fetch_array($result, MYSQL_NUM)) {
if ($row[0] ==  $adstable)
$bRetVal = TRUE;
// Code here for SQL query - insert a row into the edverts table for this ezine

break;
}
mysql_free_result($result);
//mysql_close($oConn);
}
return ($bRetVal);
echo $bRetVal;
}
fnTableExists("{$ezine_id}_adverts"); // Calls the function
That will do the trick for you!

Hope this helps...
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

So my code should look like this?

Code: Select all

CREATE TABLE [IF NOT EXISTS] `arcade_categories` (
`catid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`catname` VARCHAR( 250 ) NOT NULL ,
`displayorder` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `catid` ) ,
UNIQUE (
`catname` ,
`displayorder`
)
) TYPE = MYISAM ;
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Remove the brackets from 'IF NOT EXISTS'
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

Ok, final copy (if no errors):

Code: Select all

$sql = "CREATE TABLE IF NOT EXISTS `arcade_categories` (
`catid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`catname` VARCHAR( 250 ) NOT NULL ,
`displayorder` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `catid` ) ,
UNIQUE (
`catname` ,
`displayorder`
)
) TYPE = MYISAM ;";
$result=mysql_query($sql) or die(mysql_error());
Is that going to work?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Citizen wrote:Ok, final copy (if no errors):

Code: Select all

$sql = "CREATE TABLE IF NOT EXISTS `arcade_categories` (
`catid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`catname` VARCHAR( 250 ) NOT NULL ,
`displayorder` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `catid` ) ,
UNIQUE (
`catname` ,
`displayorder`
)
) TYPE = MYISAM ;";
$result=mysql_query($sql) or die(mysql_error());
Is that going to work?
Only one way to find out.... :lol: :lol:

EDIT: BTW/ worked for me :)
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

I ran into only one problem... if i refresh the install file and run it again, it doesnt return an error if the table exists. What did I do wrong?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Citizen wrote:I ran into only one problem... if i refresh the install file and run it again, it doesnt return an error if the table exists. What did I do wrong?
HUH? The whole point of the query to to only create a new table if the table Doesn't exist. You can always do DROP IF EXISTS too...
Post Reply