list all tables in a database with MySQLi
I have looked around and still can’t find how to list all my tables in a database. is it possible with MySQLi? Thanks.
5 Answers 5
Is the most simple SQL statement for doing that. You can also take a look at INFORMATION_SCHEMA.TABLES if you want to have more details or do some filtering or such.
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'your_database'; Using PHP 5.5 or later, a simple solution is using PHP’s built-in array_column() function.
$link = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME); $listdbtables = array_column($link->query('SHOW TABLES')->fetch_all(),0); function get_tables() < $tableList = array(); $res = mysqli_query($this->conn,"SHOW TABLES"); while($cRow = mysqli_fetch_array($res)) < $tableList[] = $cRow[0]; >return $tableList; > $link = mysqli_connect("localhost", "domain_root", "##pwd", "domain_DBname"); $query = mysqli_query($link, "SHOW TABLES IN domain_DBname"); $numrows = mysqli_num_rows($query); echo "Amount of tables: ".$numrows." and their names:"; while ($row = mysqli_fetch_array($query)) The above solution :Using PHP 5.5 or later. » did not work for me so I created this one. if the solution is incorrect give me a shout
Please explain your answer and maybe it will be useful. At the moment I don’t know what I mean to learn from this piece of code
class database < public $connection; function __construct() < $this->connection = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME) or die('Database Connection Error: '.mysqli_connect_error()); > public function close_database() < return mysqli_close($this->connection); > public function query($query) < $query = mysqli_query($this->connection ,$query) or die($this->show_errors('Query Execution Error: '.mysqli_error($this->connection),'E')); return $query; > public function fetch_assoc($query) < $query = mysqli_fetch_assoc($query); return $query; >> $db = new database(); $query = $db->query("SHOW TABLES FROM DATABASENAME"); $db->fetch_assoc($query); mysql_tablename
Данный модуль устарел, начиная с версии PHP 5.5.0, и удалён в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API. Альтернативы для данной функции:
Описание
Возвращает имя таблицы из result .
Данная функция устарела. Вместо неё рекомендуется использование mysql_query() с SQL-запросом SHOW TABLES [FROM db_name] [LIKE ‘pattern’] .
Список параметров
Дескриптор результата типа resource , полученный из вызова mysql_list_tables() .
Целочисленный индекс (номер ряда/таблицы)
Возвращаемые значения
Имя таблицы в случае успешного выполнения или false в случае возникновения ошибки.
Используйте функцию mysql_tablename() для работы с результатом запроса, либо любую другую функцию, способную это делать, например, mysql_fetch_array() .
Примеры
Пример #1 Пример использования mysql_tablename()
mysql_connect ( «localhost» , «mysql_user» , «mysql_password» );
$result = mysql_list_tables ( «mydb» );
$num_rows = mysql_num_rows ( $result );
for ( $i = 0 ; $i < $num_rows ; $i ++) echo "Table: " , mysql_tablename ( $result , $i ), "\n" ;
>
Примечания
Замечание:
Для определения количества таблиц в результате запроса можно использовать функцию mysql_num_rows() .
Смотрите также
- mysql_list_tables() — Возвращает список таблиц базы данных MySQL
- mysql_field_table() — Возвращает название таблицы, которой принадлежит указанное поле
- mysql_db_name() — Возвращает название базы данных из вызова к mysql_list_dbs
User Contributed Notes 2 notes
Another way to check if a table exists:
if(mysql_num_rows(mysql_query(«SHOW TABLES LIKE ‘».$table.»‘»))==1) echo «Table exists»;
else echo «Table does not exist»;
A simple function to check for the existance of a table:
function TableExists($tablename, $db)
// Get a list of tables contained within the database.
$result = mysql_list_tables($db);
$rcount = mysql_num_rows($result);
// Check each in list for a match.
for ($i=0;$i <$rcount;$i++) <
if (mysql_tablename($result, $i)==$tablename) return true;
>
return false;
>
- MySQL
- mysql_affected_rows
- 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
Displaying all table names in php from MySQL database
Alright, so I’m fairly new to PHP and SQL/MySQL so any help is appreciated. I feel like I took the right approach. I searched php.net for «MySQL show all table names», it returned a deprecated method and suggested using a MySQL query on SHOW TABLES [FROM db_name] [LIKE ‘pattern’] I’m not sure what «pattern» means but, I searched for «SQL Wildcard» and got the «%» symbol. According to everything I found, this should work and output the table names at the end, but it does not. Any suggestions? Thanks in advance.
'; echo 'These are your tables:'; echo '
'; $link = mysql_connect("sql2.njit.edu", "username", "password"); mysql_select_db("db_name") or die(mysql_error()); $result = mysql_query('SHOW TABLES [FROM db_name] [LIKE '%']'); echo $result; > else echo 'You did not provide the proper authentication'; ?>8 Answers 8
The square brackets in your code are used in the mysql documentation to indicate groups of optional parameters. They should not be in the actual query.
The only command you actually need is:
If you want tables from a specific database, let’s say the database «books», then it would be
You only need the LIKE part if you want to find tables whose names match a certain pattern. e.g.,
show tables from books like '%book%';would show you the names of tables that have «book» somewhere in the name.
Furthermore, just running the «show tables» query will not produce any output that you can see. SQL answers the query and then passes it to PHP, but you need to tell PHP to echo it to the page.
Since it sounds like you’re very new to SQL, I’d recommend running the mysql client from the command line (or using phpmyadmin, if it’s installed on your system). That way you can see the results of various queries without having to go through PHP’s functions for sending queries and receiving results.
If you have to use PHP, here’s a very simple demonstration. Try this code after connecting to your database:
$result = mysql_query("show tables"); // run the query and assign the result to $result while($table = mysql_fetch_array($result)) < // go through each row that was returned in $result echo($table[0] . ""); // print the table that was returned on that row. >Get table names using SELECT statement in MySQL
To get the name of the tables from a specific database use:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database_name';Now, to answer the original question, use this query:
INSERT INTO table_name SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database_name';select * from information_schema.tables@MuriloGarcia information_schema is part of the SQL standard. Postgres has it as well. For SQLite, you look in sqlite_master
Fetching tables by name using LIKE: SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE ‘%keyword%’;
if we have multiple databases and we need to select all tables for a particular database we can use TABLE_SCHEMA to define database name as:
select table_name from information_schema.tables where TABLE_SCHEMA=’dbname’;
Besides using the INFORMATION_SCHEMA table, to use SHOW TABLES to insert into a table you would use the following
foreach ($tableNames as &$name < $query = "INSERT INTO metadata (table_name) VALUES ('".$name."')"; mysql_query($query); >?>Take a look at the table TABLES in the database information_schema . It contains information about the tables in your other databases. But if you’re on shared hosting, you probably don’t have access to it.
SELECT table_name FROM information_schema.tables WHERE table_schema = 'DATABASE'May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post.
For fetching the name of all tables:
SELECT table_name FROM information_schema.tables;If you need to fetch it for a specific database:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_db_name';+--------------------+ | table_name | +--------------------+ | myapp | | demodb | | cliquein | +--------------------+ 3 rows in set (0.00 sec)MySQL INFORMATION_SCHEMA.TABLES table contains data about both tables (not temporary but permanent ones) and views. The column TABLE_TYPE defines whether this is record for table or view (for tables TABLE_TYPE=’BASE TABLE’ and for views TABLE_TYPE=’VIEW’ ). So if you want to see from your schema (database) tables only there’s the following query :
SELECT * FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='myschema'