mysql_list_tables

List tables in a MySQL database (PHP 4, PHP 5, PECL mysql:1.0)
resource mysql_list_tables ( string database [, resource link_identifier] )

Example 1451. mysql_list_tables() alternative example

copy to clipboard

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

Related Examples ( Source code ) » mysql_list_tables

Examples ( Source code ) » List all tables in a database

Examples ( Source code ) » List Database, Table, and Field

Examples ( Source code ) » Get all tables in a database

Examples ( Source code ) » Listing Tables in a Database

Code Examples / Notes » mysql_list_tables

mail

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

mrkvomail

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

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.

newtophp_guy

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

thebitman

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

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 \n".mysql_errno($linkI).": ".mysql_error($linkI)." \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." \n"; $tresult = mysql_query($tsql,$linkI); if (!$tresult) $errorstring .= "Error creating ".$V2DB.".".$row[0]." table \n".mysql_errno($linkI).": ".mysql_error($linkI)." \n"; }

wbphfox

Here is a way to show al the tables and have the function to drop them... <?php echo ""; //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>Table name:</td>"; echo "<td>Delete?</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! "; } else { echo "An error has occured...please try again "; } } ?>

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 " Average size in each row: " . c2s($total_rows_average/$total_tables); echo " Average size in each table: " . c2s($sizeo/$total_tables); echo " Database size: " . c2s($sizeo); // Close the connection mysql_close(); ?>

sindijs

Get next auto_increment value: <? mysql_connect("localhost", "login", "passwd") or die("Cannot connect: " . mysql_error()); mysql_select_db("db_name"); $res = mysql_query("SHOW TABLE STATUS LIKE 'table_name' ") or die(mysql_error()); $row_res = mysql_fetch_array($res); echo $row_res[Auto_increment]; ?>

coffee

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

cdarklock

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.

arturclemente

<?php // Search any world in entire database set_time_limit(0); $database = 'embratur'; $word = 'tempo'; echo "Search for word: ".$word." "; $connect = mysql_connect('localhost','root','123456'); mysql_selectdb( $database, $connect ); $j=0; $tables = mysql_list_tables( $database ); while( $line = mysql_fetch_row( $tables ) ) { $fields = mysql_list_fields( $database, $line[0], $connect ); $columns = mysql_num_fields( $fields ); for ($i = 0; $i < $columns; $i++) { $nomField = mysql_field_name( $fields, $i ); $sql = "SELECT ".$nomField." FROM " . $line[0] . " WHERE " . $nomField . " LIKE '%".$word."' LIMIT 1"; $query = mysql_query( $sql ); if( mysql_num_rows( $query ) > 0) { $result = mysql_fetch_array( $query ); echo "Table: ".$line[0]." "; echo "Field: ".$nomField." "; echo "SQL: ".$sql." "; echo "Result: " . str_replace( $word, "". $word . "", $result[0] ) . ""; echo " "; $j++; } } } echo " "; echo "Results: ".$j; ?>

kroczu

<? // 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."'"))) { //... } ?>

30-oct-2002 01: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; } ?>

Change Language

mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_set_charset
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query