By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,928 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,928 IT Pros & Developers. It's quick & easy.

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

P: n/a
$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

Mar 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ph**************@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”);

Mar 16 '06 #2

P: n/a

noone wrote:
ph**************@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

Mar 16 '06 #3

P: n/a
<ph**************@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.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/...es-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.
Mar 16 '06 #4

P: n/a

Bill Karwin wrote:
<ph**************@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.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/...es-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

Mar 16 '06 #5

P: n/a
"noone" <no***@nowhere.com> wrote in message
news:78********************************@www.firstd basource.com...
$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/functio...ist-tables.php

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

Regards,
Bill K.
Mar 16 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.