Page 1 of 1

Use php to check if a table exists

Posted: Thu May 11, 2006 1:07 pm
by Citizen
I'm creating a php install file to setup a database.

How do I check to see if the table already exists?

Posted: Thu May 11, 2006 1:13 pm
by hawleyjr
EXISTS is the key word you're looking for

http://dev.mysql.com/doc/refman/5.0/en/ ... table.html

Posted: Thu May 11, 2006 1:13 pm
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 ^^^

Posted: Thu May 11, 2006 1:14 pm
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...

Posted: Thu May 11, 2006 1:16 pm
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 ;

Posted: Thu May 11, 2006 1:19 pm
by hawleyjr
Remove the brackets from 'IF NOT EXISTS'

Posted: Thu May 11, 2006 1:23 pm
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?

Posted: Thu May 11, 2006 1:28 pm
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 :)

Posted: Thu May 11, 2006 1:32 pm
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?

Posted: Thu May 11, 2006 1:34 pm
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...