Page 1 of 1

mysql: how to check if table exists?

Posted: Sun Dec 14, 2003 8:40 pm
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)

Posted: Sun Dec 14, 2003 8:56 pm
by Weirdan
mysql_query("show tables from `db`");
or [php_man]mysql_list_tables[/php_man]

Posted: Sun Dec 14, 2003 9:01 pm
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.

Posted: Sun Dec 14, 2003 10:35 pm
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";

Posted: Mon Dec 15, 2003 3:36 am
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());
}
}
?>