PHP: mysql_list_tables

User Contributed Notes
mysql_list_tables

daveheslop (dave heslop)
07-May-2005 06:45


Worth noting for beginners: using a row count to test for the existence of a table only works if the table actually contains data, otherwise the test will return false even if the table exists.

mbchandar at gmail dot com
29-Apr-2005 05:10


the alternative example is wrong. 

you can use the below lines



for listing tables

show tables from [database]



for listing table columns

show columns from [database].[tablename]

djneoform at gmail dot com
10-Apr-2005 03:00


just a note, after playing around with it for a while i noticed it's not "SELECT TABLES FROM"..  it's actually "SHOW TABLES FROM".



otherwise you get an SQL error.

paul AT amazingflash.com
07-Apr-2005 02:33


Hello, the alternative example in the mysql_list_tables function has a small but ultimately large error in it. The sql query is supposed to be "Show Tables from $dbname", but instead it says "Select Tables ...". Took me a while to figure it out, but when I figured it out, I knew that code should be edited. I can provide an example if you guys want me to start adding some notes for php.net. Thanks,



Paul

tim at top-cat dot com
12-Jan-2005 04:47


TABLE EXISTS: Performance test...



To test thebitman at attbi dot com's assertion that his method is the fastest way to check if a table exists - I ran the following code, just changing the function name between runs to test the speed of the two functions:



$t_start = array_sum(explode(' ', microtime()));



// Using STATUS method...

function mysql_table_exists($tablename) {

  $res = mysql_query("SHOW TABLE STATUS LIKE '$tablename'") or die(mysql_error());

  return mysql_num_rows($res) == 1;

  }



// Using A SELECT...

function mysql_table_exists2($table){

     $exists = mysql_query("SELECT 1 FROM $table LIMIT 0");

      if ($exists) return true;

     return false;

}



for ($a=0; $a<1000; $a++) {

  if (mysql_table_exists2('Products')) echo "Y";

  else echo 'N';

  }



$exectime = array_sum(explode(' ', microtime()))-$t_start;

echo "<br><p>Exec Time: $exectime</p>\r\n";



RESULTS:

  1st method (STATUS) runtime

      = 1.92s if the table exists

      = 1.85s if table doesn't exist



  2nd method (SELECT) runtime

      = 0.16s if the table exists

      = 0.32s if the table doesn't exist.



Ladies and gentlemen, we have a clear winner... Nice one thebitman !

zeubeubeu at free dot fr
12-May-2004 08:38


Just another way to verify the existence of a table based on the function below :



function mysql_table_exists($base, $table, $link)

{

    $exists = mysql_query('SHOW TABLES FROM `'.$base.'` LIKE \''.$table.'\'', $link);

    

    return mysql_num_rows($exists) == 1;

}



You can extend this function for multi-checks by changing the pattern of the LIKE if you store separate tables for archive like LIKE 'mails_200%' for example.

wbphfox at xs4all dot nl
09-Sep-2003 11:55


Here is a way to show al the tables and have the function to drop them...



<?php



echo "<p align=\"left\">";

//this is the connection file for the database....

$connectfile = "connect.php";

require $connectfile;



$dbname = 'DATABASE NAME';



$result = mysql_list_tables($dbname);



echo "<table width=\"75%\" border=\"0\">";

echo  "<tr bgcolor=\"#993333\"> ";

echo    "<td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"-1\" color=\"#FFFFFF\">Table name:</font></td>";

echo    "<td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"-1\" color=\"#FFFFFF\">Delete?</font></td>";

echo  "</tr>";

  

    if (!$result) {

        print "DB Error, could not list tables\n";

        print 'MySQL Error: ' . mysql_error();

        exit;

    }

    

    while ($row = mysql_fetch_row($result)) {

        echo "<tr bgcolor=\"#CCCCCC\">";

echo    "<td>";

           print "$row[0]\n";

echo    "</td>";



echo    "<td>";

echo    "<a href=\"$PHP_SELF?action=delete&table=";

         print "$row[0]\n";

echo    "\">Yes?</a>";



echo    "</td>";



echo "</tr>";

        

        

    }



    mysql_free_result($result);



//Delete

if($action=="delete")

{

$deleteIt=mysql_query("DROP TABLE $table"); 

if($deleteIt)

{

echo "The table \"";

echo "$table\" has been deleted with succes!<br>";

}

else

{

echo "An error has occured...please try again<br>";

}

}

  

?>

thebitman at attbi dot com
07-May-2003 03:49


okay everybody, the fastest, most accurate, safest method:



function mysql_table_exists($table, $link)

{

     $exists = mysql_query("SELECT 1 FROM `$table` LIMIT 0", $link);

      if ($exists) return true;

     return false;

}



Note the "LIMIT 0", I mean come on, people, can't get much faster than that! :)

As far as a query goes, this does absolutely nothing. But it has the ability to fail if the table doesnt exist, and that's all you need!

daevid at daevid dot com
17-Dec-2002 06:36


I was in need of a way to create a database, complete with tables from a .sql file. Well, since PHP/mySQL doesn't allow that it seems, the next best idea was to create an empty template database and 'clone & rename it'. Guess what? There is no mysql_clone_db() function or any SQL 'CREATE DATABASE USING TEMPLATEDB' command. grrr...



So, this is the hack solution I came up with:



$V2DB = "V2_SL".$CompanyID;



$result = mysql_create_db($V2DB, $linkI);

if (!$result) $errorstring .= "Error creating ".$V2DB." database<BR>\n".mysql_errno($linkI).": ".mysql_error($linkI)."<BR>\n"; 



mysql_select_db ($V2DB, $linkI) or die ("Could not select ".$V2DB." Database");



//You must have already created the "V2_Template" database. 

//This will make a clone of it, including data.



$tableResult = mysql_list_tables ("V2_Template");

while ($row = mysql_fetch_row($tableResult)) 

{

    $tsql = "CREATE TABLE ".$V2DB.".".$row[0]." AS SELECT * FROM V2_Template.".$row[0];

    echo $tsql."<BR>\n";

    $tresult = mysql_query($tsql,$linkI);

    if (!$tresult) $errorstring .= "Error creating ".$V2DB.".".$row[0]." table<BR>\n".mysql_errno($linkI).": ".mysql_error($linkI)."<BR>\n"; 

}

cdarklock at darklock dot com
06-Dec-2002 10:03


Actually, the initially posted SELECT COUNT(*) approach is flawless. SELECT COUNT(*) will provide one and only one row in response unless you can't select from the table at all. Even a brand new (empty) table responds with one row to tell you there are 0 records. 



While other approaches here are certainly functional, the major problem comes up when you want to do something like check a database to ensure that all the tables you need exist, as I needed to do earlier today. I wrote a function called tables_needed() that would take an array of table names -- $check -- and return either an array of tables that did not exist, or FALSE if they were all there. With mysql_list_tables(), I came up with this in the central block of code (after validating parameters, opening a connection, selecting a database, and doing what most people would call far too much error checking):



if($result=mysql_list_tables($dbase,$conn))

{   // $count is the number of tables in the database

    $count=mysql_num_rows($result); 

    for($x=0;$x<$count;$x++) 

    {

        $tables[$x]=mysql_tablename($result,$x);

    }

    mysql_free_result($result);

    // LOTS more comparisons here

    $exist=array_intersect($tables,$check);

    $notexist=array_diff($exist,$check);

    if(count($notexist)==0)

    {

        $notexist=FALSE;

    }

}



The problem with this approach is that performance degrades with the number of tables in the database. Using the "SELECT COUNT(*)" approach, performance only degrades with the number of tables you *care* about:



// $count is the number of tables you *need*

$count=count($check);

for($x=0;$x<$count;$x++) 

{

    if(mysql_query("SELECT COUNT(*) FROM ".$check[$x],$conn)==FALSE)

    {

        $notexist[count($notexist)]=$check[$x];

    }

}

if(count($notexist)==0) 

{

    $notexist=FALSE;

}



While the increase in speed here means virtually nothing to the average user who has a database-driven backend on his personal web site to handle a guestbook and forum that might get a couple hundred hits a week, it means EVERYTHING to the professional who has to handle tens of millions of hits a day... where a single extra millisecond on the query turns into more than a full day of processing time. Developing good habits when they don't matter keeps you from having bad habits when they *do* matter.

29-Oct-2002 07:42


<?

/*

   Function that returns whole size of a given MySQL database

   Returns false if no db by that name is found

*/



  function getdbsize($tdb) {

    $db_host='localhost';

    $db_usr='USER';

    $db_pwd='XXXXXXXX';

    $db = mysql_connect($db_host, $db_usr, $db_pwd) or die ("Error connecting to MySQL Server!\n");

    mysql_select_db($tdb, $db);



    $sql_result = "SHOW TABLE STATUS FROM " .$tdb;

    $result = mysql_query($sql_result);

    mysql_close($db);



    if($result) {

        $size = 0;

        while ($data = mysql_fetch_array($result)) {

             $size = $size + $data["Data_length"] + $data["Index_length"];

        }

        return $size;

    }

    else {

        return FALSE;

    }

  }



?>



<?

/*

   Implementation example

*/



  $tmp = getdbsize("DATABASE_NAME");

  if (!$tmp) { echo "ERROR!"; }

  else { echo $tmp; }

?>

mail at thomas-hoerner dot de
04-Oct-2002 11:24


You can also use mysql_fetch_object if you consider a specialty: The name of the object-var is



Tables_in_xxxxx



where xxxxx is the name of the database.



i.e. use 



$result = mysql_list_tables($dbname);

$varname="Tables_in_".$dbname;

while ($row = mysql_fetch_object($result)) {

   echo $row->$varname; 

};

NewToPHP_Guy at Victoria dot NOSPAM dot com
02-Oct-2002 04:06


The example by PHP-Guy to determine if a table exists is interesting and useful (thanx), except for one tiny detail.  The function 'mysql_list_tables()' returns table names in lower case even when tables are created with mixed case.  To get around this problem, add the 'strtolower()' function in the last line as follows:



return(in_array(strtolower($tableName), $tables));

coffee at hayekheaven dot net
17-Jun-2002 07:48


Even though php guy's solution is probably the fastest here's another one just for the heck of it...

I use this function to check whether a table exists. If not it's created.



mysql_connect("server","usr","pwd")

    or die("Couldn't connect!");

mysql_select_db("mydb");



$tbl_exists = mysql_query("DESCRIBE sometable");

if (!$tbl_exists) {

mysql_query("CREATE TABLE sometable (id int(4) not null primary key,

somevalue varchar(50) not null)");

}

the_php_guy at e-clecticity dot net
09-Jun-2002 03:23


I was also surprised to find there's no function that lets you test for the existence of a table. 



There's a few problems with the solution offered above. For one thing, it's perfectly possible (and legal) for a table to have no records - a condition that would not be caught by this function.



Here's a better way to do it: 



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

}

inerte at NO_SPAM_hotmail dot com
13-Dec-2001 11:19


Sometimes mysql tables can get a little overhead due to heavy usage.<br>Here's a quick snippet that you can run once in a while to keep things in shape:





$db = 'database_name';





$tables = mysql_list_tables($db);





while (list($table_name) = mysql_fetch_array($tables)) {


    $sql = "OPTIMIZE TABLE $table_name";


    mysql_query($sql) or exit(mysql_error());


}

mysql_list_tables

Description

Parameters

Return Values

Examples

Notes

See Also