473,372 Members | 1,062 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

Mar 16 '06 #1
5 3923
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

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
<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

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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Phil Powell | last post by:
create table if not exists nnet_produkt_varegruppe ( nnet_produkt_varegruppe_id int not null auto_increment, primary key(nnet_produkt_varegruppe_id), nnet_produkt_varegruppe_navn varchar(255) not...
4
by: Dariusz | last post by:
I am a beginner in PHP and MySQL, and am working through a book and various online tutorials on PHP and MySQL and now stuck - installed everything on "localhost" and it all works fine. My question...
3
by: dave | last post by:
Hello there, I am at my wit's end ! I have used the following script succesfully to upload an image to my web space. But what I really want to be able to do is to update an existing record in a...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
8
by: Daz | last post by:
Hi everyone. I was faced with the choice of whether my problem is indeed a PHP problem or a MySQL. I have decided it's a PHP problem as I don't experience the same problem when I execute the...
14
by: Ben | last post by:
I don't know whether anyone can help, but I have an odd problem. I have a PSP (Spyce) script that makes many calls to populate a database. They all work without any problem except for one...
1
by: paulq182 | last post by:
PLEASE HELP ME WITH MY CODE?? import java.sql.*; import java.io.*; class min_filmdb_rel_mysql { public static void main (String args ) throws SQLException, IOException {
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
1
ssnaik84
by: ssnaik84 | last post by:
Hi Guys, Last year I got a chance to work with R&D team, which was working on DB scripts conversion.. Though there is migration tool available, it converts only tables and constraints.. Rest of...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.