?
Solved

How do I check if a table exists?

Posted on 2003-02-26
14
Medium Priority
?
399 Views
Last Modified: 2006-11-17
Hi,
In my php code I want to check if tbl_prs_customer exists in my database.
Please help?!

Hinke
0
Comment
Question by:hinkeltje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
14 Comments
 

Author Comment

by:hinkeltje
ID: 8024274
By the way, it is a mySQL database.
0
 
LVL 2

Accepted Solution

by:
laurly earned 100 total points
ID: 8024407
try this

mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("show tables");

while ($row = mysql_fetch_array($result)) {
       if $row[0] = "tbl_prs_customer"
          {
     echo "your table exists"

          }

}

mysql_free_result($result);
0
 
LVL 15

Expert Comment

by:VGR
ID: 8024952
yes

or use a query like (not tested) :
$result=mysql_query("DESCRIBE tablename;") or die("doesn't exist"); // of other action if possible
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Assisted Solution

by:fbordin
fbordin earned 100 total points
ID: 8025577
If this is a database and not a table that you want to test on then you should  use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

I guess you have to  do something like laurly but with Database instead of tables

0
 
LVL 7

Expert Comment

by:Big_Red_Dog
ID: 8038784
But remember, you do not need to check if it exists when creating a table by using the special "IF NOT EXISTS" when creating the table.

CREATE TABLE IF NOT EXISTS my_table_name ...
0
 

Expert Comment

by:philcartmell
ID: 8070291
This is a function to check to see if a table exists:

function mysql_table_exist($tableName)
{
$query = "SELECT COUNT(*) FROM $tableName";
       $result = mysql_query($query);
$num_rows = @mysql_num_rows($result);
if($num_rows)
{
return TRUE;
}
else
{

return FALSE;
}

}
0
 

Expert Comment

by:philcartmell
ID: 8070299
Hi - use the below instead - I've optimised it a little:

function mysql_table_exists($dbLink, $database, $tableName)
{
$tables = array();
$tablesResult = mysql_list_tables($database, $dbLink);
while ($row = mysql_fetch_row($tablesResult)) $tables[] = $row[0];
return(in_array($tableName, $tables));
}
0
 
LVL 15

Expert Comment

by:VGR
ID: 8070305
groumph ®

it's as good as using a standard mysql_query() with "SHOW TABLES";
0
 

Assisted Solution

by:philcartmell
philcartmell earned 100 total points
ID: 8070313
i presume mysql_list_tables is essentially a php wrapper around show tables. - may as well use the function thats their - why reinvent the wheel ;)
0
 
LVL 15

Assisted Solution

by:VGR
VGR earned 100 total points
ID: 8070331
yes and no
yes, it's that
no, because it becomes ***less*** portable
for instance, Oracle or DB2 "drivers in PHP" (wrappers) may very well not have *_liust_table() function implemented, whereas "SHOW TABLES;" in pure SQL will always work :D
0
 

Expert Comment

by:philcartmell
ID: 8070341
true. but in this occasion i seriously doubt he'll be porting to oracle lol
0
 
LVL 7

Expert Comment

by:Big_Red_Dog
ID: 8547272
Did any of this help you?  If so, please award a grade to close this question.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question