Check to see if table exists...

drakkarnoir
drakkarnoir used Ask the Experts™
on
How do I in php check to see if a table exists? Return 1 if it does, return 0 if it does not.

The tablenames are independent of the user, so my username var is $username, and I want to check if a table with the name $username_table exists...so let's say the username is "Bob"

Check to see if "Bob_table" exists, if the username is "Mark" check to see if "Mark_table" exists.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
assuming that u r talking abt mysql  :)
when u r selecting the database, u can do this

$value = 0;
$selectdb = mysql_select_db("$Database", $connection) or $value = 1;


Author

Commented:
And how would I implement the username part?

Author

Commented:
And it's not a database...it's a table hehe
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Commented:
if(!$result = mysql_query(select * from ".$username."_table limit 1))
{
  echo mysql_error();
  //table probably doesn't exist ;)
  exit;
}

Commented:
duerra,

  There are many ways to check whether a table exists.  The proper way would be to list all tables (SHOW TABLES IN DATABASE_NAME) and loop through the results so that you would not need to rely on PHP's error handling (even with limit 0 if the table did not exist it would error which is why the query should be called with @mysql_query).  You can look at http://us3.php.net/manual/en/function.mysql-list-tables.php (Documentation for the PHP function mysql_list_tables) for more ways to determine if a table exists.


drakkarnoir,  The solution provided by duerra is correct, award points to him/her.  This was only posted for others that read this thread once it is closed.

Regards,
Kevin

Commented:
y2kwacko,
I actually tried coming up with a script that would pull the table names from a query, but couldn't come up with one that worked properly.  When I did a query in php for mysql_query("show tables"), I didn't get an error, but table names weren't returned, either.  

You are correct, though - relying on php's error handling to do such a task is tacky at best.  I'll keep looking to see if I can find another way.

Commented:
Ahh.  Good reference!

There it is, right in the php manual.  How cheap do  I feel now =P

<?php
    $dbname = 'mysql_dbname';

    if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
        print 'Could not connect to mysql';
        exit;
    }

    $result = mysql_list_tables($dbname);
   
    if (!$result) {
        print "DB Error, could not list tables\n";
        print 'MySQL Error: ' . mysql_error();
        exit;
    }
   
    while ($row = mysql_fetch_row($result)) {
        print "Table: $row[0]\n";
    }

    mysql_free_result($result);
?>

Commented:
Just to be nitpicky, it sounds fishy that each user gets his own table.  Maybe you would be better off having a table of users, and then add a userID column to the table containing user-specific data?  This would scale better when you get large numbers of users, and is preferable from a database normalization standpoint.

Then, to see if a user exists you just do:

<?php
$result = mysql_query("SELECT * FROM user, user_data WHERE user.id=user_data.userID AND user.name='bob'");
if (mysql_num_rows($result) == 0) {
    // user doesn't exist
} else {
    // do something with the user data
}
?>

So when adding new users you don't need to create a table, just insert a row into the user table.
Hey try this

$exists = mysql_query("SELECT 1 FROM $var_table LIMIT 0", $db_connect);

if ($exists)
{
  //table exists
}
else
{
   //table does not exist
}

//the query will fail if your table name assigned in the variable $var_table does not exists in the database

Commented:
By the way, one of the best reasons to go with the 2-table approach I mentioned above is, reporting purposes.  You can run aggregate queries for all user data if they're all in a single user_data table.  If each user's data is in its own table, you'll have a very tough time running reports, finding averages, etc.

Especially if you decide that you want to add another column of user_data.  If you have 500 users, that's 500 "ALTER TABLE joe_table ...  ALTER TABLE bob_table..." statements!!!

Author

Commented:
Thanks all, you guys got me through this problem together, rock on.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial