How to detect if a MySQL db table exists in MySQL 4.1+

$sql = "SELECT IF((SHOW TABLES LIKE '$subselectTableName'), count(*),
NULL) AS numRows FROM $subselectTableName";

I am trying to write a SQL statement that will tell me if a table
exists or not, this is because PHP will throw Fatal Errors and halt all
programming if the table does not exist that I attempt to query.

How do you write an elegant MySQL 4.1+ statement to detect if a table
exists or not?

Thanx
Phil

Re: How to detect if a MySQL db table exists in MySQL 4.1+

wrote in message
news:1142542142.682445.31650@i40g2000cwc.googlegroups.com...
> I am trying to write a SQL statement that will tell me if a table
> exists or not, this is because PHP will throw Fatal Errors and halt all
> programming if the table does not exist that I attempt to query.

That's true -- if your PHP code invokes the die() function on error.
You should do something different when you detect an error.

$link = mysql_connect(. . .);
if (!mysql_query($sql, $link)) {
if (mysql_errno($link) == 1146) { // 1146 is ER_NO_SUCH_TABLE
// do something to handle the problem
}
}

See errno 1146 and others at
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server .html

> How do you write an elegant MySQL 4.1+ statement to detect if a table
> exists or not?

SHOW TABLES LIKE 'name' should be all you need. If the query result
produces no rows, then the table doesn't exist.

But this really shouldn't be the solution. The best solution is to write
code that queries tables that do exist. Failing that, handle the error with
something other than die().

Regards,
Bill K.

Re: How to detect if a MySQL db table exists in MySQL 4.1+

phillip.s.powell@gmail.com wrote:

> $sql = "SELECT IF((SHOW TABLES LIKE '$subselectTableName'), count(*),
> NULL) AS numRows FROM $subselectTableName";

> I am trying to write a SQL statement that will tell me if a table
> exists or not, this is because PHP will throw Fatal Errors and halt all
> programming if the table does not exist that I attempt to query.

> How do you write an elegant MySQL 4.1+ statement to detect if a table
> exists or not?

> Thanx
> Phil


This is why you "DESIGN" a database and application - don't just throw it
together...

What happens if it doesn't exist? do you create it?

from: http://builder.com.com/5100-6371-1045433.html

fnTableExists()
The fnTableExists() function determines whether a particular table exists
within a specified database on your MySQL server. Again, you need to have
a server name, user name and password.
function fnTableExists($TableName) {
//Verifies that a MySQL table exists
if (!$oConn = @mysql_connect(“mysql.kaufman.net”,
“guest”, “secretword”)) {
$bRetVal = FALSE;
} else {
$bRetVal = FALSE;
$result = mysql_list_tables('MyDatabase', $oConn);
while ($row=mysql_fetch_array($result, MYSQL_NUM)) {
if ($row[0] == $TableName)
$bRetVal = TRUE;
break;
}
mysql_free_result($result);
mysql_close($oConn);
}
return ($bRetVal);
}

Usage
This function also accepts one parameter—the name of the table
you're verifying within the specified database on your MySQL server. It
also returns a Boolean value indicating whether the table exists.
$bRetVal = fnTableExists(“Users”);

Re: How to detect if a MySQL db table exists in MySQL 4.1+

noone wrote:
> phillip.s.powell@gmail.com wrote:
>
> > $sql = "SELECT IF((SHOW TABLES LIKE '$subselectTableName'), count(*),
> > NULL) AS numRows FROM $subselectTableName";
>
> > I am trying to write a SQL statement that will tell me if a table
> > exists or not, this is because PHP will throw Fatal Errors and halt all
> > programming if the table does not exist that I attempt to query.
>
> > How do you write an elegant MySQL 4.1+ statement to detect if a table
> > exists or not?
>
> > Thanx
> > Phil
>
>
> This is why you "DESIGN" a database and application - don't just throw it
> together...
>
> What happens if it doesn't exist? do you create it?
>
What if you don't have privileges to create it? What if you're not
given the privilege to create it and you have to have something with N
rows in order for your function to parse the number of rows?

What if there is no DBA and it's just you and the team of people
designing this and you're tasked with making it bulletproof - without a
DBA!

Phil

Re: How to detect if a MySQL db table exists in MySQL 4.1+

"noone" wrote in message
news:782df0e21b5ca09686187304e893beb2$1@www.firstdbasource.c om...
> $result = mysql_list_tables('MyDatabase', $oConn);

Aha -- this PHP function is important thing to notice.

Here's the docs on it:
http://www.php.net/manual/en/function.mysql-list-tables.php

It also shows a PHP code example of querying simply using SHOW TABLES.

Regards,
Bill K.

Re: How to detect if a MySQL db table exists in MySQL 4.1+

Bill Karwin wrote:
> wrote in message
> news:1142542142.682445.31650@i40g2000cwc.googlegroups.com...
> > I am trying to write a SQL statement that will tell me if a table
> > exists or not, this is because PHP will throw Fatal Errors and halt all
> > programming if the table does not exist that I attempt to query.
>
> That's true -- if your PHP code invokes the die() function on error.
> You should do something different when you detect an error.
>
> $link = mysql_connect(. . .);
> if (!mysql_query($sql, $link)) {
> if (mysql_errno($link) == 1146) { // 1146 is ER_NO_SUCH_TABLE
> // do something to handle the problem
> }
> }
>
> See errno 1146 and others at
> http://dev.mysql.com/doc/refman/5.0/en/error-messages-server .html
>
> > How do you write an elegant MySQL 4.1+ statement to detect if a table
> > exists or not?
>
> SHOW TABLES LIKE 'name' should be all you need. If the query result
> produces no rows, then the table doesn't exist.
>
> But this really shouldn't be the solution. The best solution is to write
> code that queries tables that do exist. Failing that, handle the error with
> something other than die().
>
> Regards,
> Bill K.

Thanx, I figured it out, using a PHP solution to be able to detect if
it exists or not, using a new method in my DBConnection object called
list_tables() that returns an array of tables using the
mysql_list_tables($db, $dbconnection) function.

if ($subselectTableName) {
// USE INHERITED $dbAP->dbConnObj METHOD list_tables($dbDefaultName)
if (@in_array($subselectTableName,
$dbAP->dbConnObj->list_tables($dbDefaultName))) {
$query = new MySQLQuery("SELECT count(*) AS numRows FROM
$subselectTableName LIMIT 1", $dbAP->getDBConn());
$rowResult =@ $query->getResult();
}
}

This will insure that either the table exists and runs the query, or it
doesn't and does not run the query, and no errors as a result!

Thanx!
Phil