mysql_list_tables

(PHP 4, PHP 5)

mysql_list_tables — List tables in a MySQL database

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

SQL Query: SHOW TABLES FROM sometable

Description

resource mysql_list_tables ( string $database [, resource $link_identifier = NULL ] )

Retrieves a list of table names from a MySQL database.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead.

Parameters

database: The name of the database
link_identifier: The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

A result pointer resource on success or FALSE on failure.

Use the mysql_tablename() function to traverse this result pointer, or any function for result tables, such as mysql_fetch_array().

Changelog

Version	Description
4.3.7	This function became deprecated.

Examples

Example #1 mysql_list_tables() alternative example


<?php
$dbname = 'mysql_dbname';

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

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
}

mysql_free_result($result);
?>

Notes

Note:
For backward compatibility, the following deprecated alias may be used: mysql_listtables()

Коментарии

Jun 17

Автор: coffee at hayekheaven dot net


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

}

2002-06-17 20:48:37

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Oct 02

Автор: NewToPHP_Guy at Victoria dot NOSPAM dot com


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

2002-10-02 17:06:35

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Oct 29

Автор: Анонимус@


<?

/*

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

?>

2002-10-29 19:42:01

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Dec 06

Автор: cdarklock at darklock dot com


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.

2002-12-06 10:03:03

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Dec 17

Автор: daevid at daevid dot com


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

}

2002-12-17 06:36:55

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

May 07

Автор: thebitman at attbi dot com


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!

2003-05-07 04:49:37

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Sep 09

Автор: wbphfox at xs4all dot nl


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

}

}

  

?>

2003-09-09 12:55:04

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

May 07

Автор: daveheslop (dave heslop)


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.

2005-05-07 19:45:32

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Dec 15

Автор: Anonymous


Getting the database status:

<?

// Get database status by DtTvB

// Connect first

mysql_connect   ('*********', '*********', '********');

mysql_select_db ('*********');



// Get the list of tables

$sql  = 'SHOW TABLES FROM *********';

if (!$result = mysql_query($sql)) { die ('Error getting table list (' . $sql . ' :: ' . mysql_error() . ')'); }



// Make the list of tables an array

$tablerow = array();

while ($row = mysql_fetch_array($result)) { $tablerow[] = $row; }



// Define variables...

$total_tables       = count($tablerow);

$statrow            = array();

$total_rows         = 0;

$total_rows_average = 0;

$sizeo              = 0;



// Get the status of each table

for ($i = 0; $i < count($tablerow); $i++) {

    // Query the status...

    $sql = "SHOW TABLE STATUS LIKE '{$tablerow[$i][0]}';";

    if (!$result = mysql_query($sql)) { die ('Error getting table status (' . $sql . ' :: ' . mysql_error() . ')'); }

    // Get the status array of this table

    $table_info = mysql_fetch_array($result);

    // Add them to the total results

    $total_rows         += $table_info[3];

    $total_rows_average += $table_info[4];

    $sizeo              += $table_info[5];

}



// Function to calculate size of the file

function c2s($bs) {

         if ($bs < 964)     { return round($bs)           . " Bytes"; }

    else if ($bs < 1000000) { return round($bs/1024,2)    . " KB"   ; }

    else                    { return round($bs/1048576,2) . " MB"   ; }

}



// Echo the result!!!!!!!!!

echo "{$total_rows} rows in {$total_tables} tables";

echo "<br>Average size in each row: " . c2s($total_rows_average/$total_tables);

echo "<br>Average size in each table: " . c2s($sizeo/$total_tables);

echo "<br>Database size: " . c2s($sizeo);



// Close the connection

mysql_close();

?>

2005-12-15 08:38:42

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Jan 29

Автор: mrkvomail at centrum dot cz


You can also do this with function mysql_query(). It's better because mysql_list_tables is old function and you can stop showing errors.



function mysql_table_exists($dbLink, $database, $tableName)

{

   $tables = array();

   $tablesResult = mysql_query("SHOW TABLES FROM $database;", $dbLink);

   while ($row = mysql_fetch_row($tablesResult)) $tables[] = $row[0];

    if (!$result) {

    }

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

}

2006-01-29 13:48:21

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Dec 08

Автор: kroczu at interia dot pl


<?

// here is a much more elegant method to check if a table exists ( no error generate)



if( mysql_num_rows( mysql_query("SHOW TABLES LIKE '".$table."'")))

{

 //...

}



?>

2006-12-08 05:01:52

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

Feb 28

Автор: bimal at sanjaal dot com


A better alternative to mysql_list_tables() would be the following mysql_tables() function.



<?php

/**

* Better alternative to mysql_list_tables (deprecated)

*/

function mysql_tables($database='')

{

    $tables = array();

    $list_tables_sql = "SHOW TABLES FROM {$database};";

    $result = mysql_query($list_tables_sql);

    if($result)

    while($table = mysql_fetch_row($result))

    {

        $tables[] = $table[0];

    }

    return $tables;

}



# Usage example

$tables = mysql_tables($database_local);

?>

2013-02-28 11:22:21

http://php5.kiev.ua/manual/ru/function.mysql-list-tables.html

PHP5

Для web разработчика

Nov 21
Функция mysql_list_tables() - List tables in a MySQL database

mysql_list_tables

Description

Parameters

Return Values

Changelog

Examples

Notes

See Also

Коментарии

PHP5

Для web разработчика

Nov 21Функция mysql_list_tables() - List tables in a MySQL database

mysql_list_tables

Description

Parameters

Return Values

Changelog

Examples

Notes

See Also

Коментарии

Nov 21
Функция mysql_list_tables() - List tables in a MySQL database