mysql: how to check if table exists?

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
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

mysql: how to check if table exists?

Post by qads »

hi,
i been at it for a while now, i need to find out if a table exists or not, so far i have tried with

Code: Select all

<?php
$sql = mysql_num_rows(mysql_query("select ID from table limit 1"));
if($sql != 1)
{
die("table not found, would youlike to make one?");
}
?>
but this ouputs a error whichi dont need :?.

also tried

Code: Select all

<?php
function check_table()
{
$table = $this->stat_table;
$result = mysql_query("EXISTS TABLE $table");
echo $result;//nooutput at all here :/
if($result != 1)
{
echo $this->mysql->error("[red]$table was not found in the database[/font]<b>Would like to make it now?</b><br /><a href="$_SERVER[PHP_SELF]?maketable=1" style="color:blue">Yes!</a>");
}?>
that doest work either. :?

any ideas?
(i am making a class for site stats :D)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

mysql_query("show tables from `db`");
or [php_man]mysql_list_tables[/php_man]
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

I would use mysql_list_tables and loop through the array looking for your table to see if it exists. Not sure if there is an easier function.

http://au2.php.net/manual/en/function.m ... tables.php

Doh, Dan beat me.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

Code: Select all

$sql = mysql_query("select ID from table limit 1") or die(MySQL_Error());
this will check to see if the table exists.

are you wanting to check to see if it exists, and if it doesn't create it though?

if so :

Code: Select all

$sql = "create table IF NOT EXIST mytablename";
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

thanks guys :D
if anyone else needs a example then here it is->

Code: Select all

<?php
function check_table()
{
$table = $this->stat_table;
$db = $this->mysql->DB_NAME;
$result = mysql_list_tables($db);
while($row = mysql_fetch_array($result))
{
$tables[] = $row[0];
}
if(!in_array($table, $tables) and !isset($_GET['maketable']))
{
echo $this->mysql->error("[red]$table was not found in the database[/font]<b>Would like to make it now?</b><br /><a href="$_SERVER[PHP_SELF]?$_SERVER[QUERY_STRING]&maketable=1" style="color:blue">Yes!</a>");
}
if(isset($_GET['maketable']) AND !in_array($table, $tables))
{
$query = mysql_query("CREATE TABLE `$table` (
  `ID` int(11) NOT NULL auto_increment,
  `browser` text NOT NULL,
  `ip` varchar(20) NOT NULL default '0',
  `redirect` text NOT NULL,
  `time_stamp` int(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM")or die(mysql_error());
}
}
?>
Post Reply