Displaying All Table Names in PHP from MySQL Database

Displaying all table names in php from MySQL database

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:

show tables;

If you want tables from a specific database, let's say the database "books", then it would be

show tables from books;

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] . "<BR>");    // print the table that was returned on that row.
}

Show all tables within given MySQL database

First make up your mind, either use mysqli procedural or object orientated. Not a combination of both because its confusing. To avoid that all together use pdo instead.

Now properly connect to the database, you can select the database when connecting to it automatically:

const DB_DATABASE = 'paperlessdb';

$conn = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

// Check connection
if ($conn->connect_error) {
    die("Connection with the database failed: </br>" . $conn->connect_error);
}

if($result = $conn->query('SHOW TABLES')){
  while($row = $conn->fetch_array($result)){
    $tables[] = $row[0];
  }
}

print_r($tables);

How to display all table names from particular mysql database in php

All it is displaying 1 for every entry, I don't know why

fetch() returns a boolean, therefore your result of 1. It will return true every time it finds a row.

You need to use bind_result() to bind the result to a variable that you can reference for each row.

$stmt->prepare("SELECT table_name FROM information_schema.tables where table_schema=?")
$stmt->bind_param('s', 'YOUR_DB_NAME');
$stmt->execute();
$stmt->bind_result($table);
$stmt->store_result();
while($stmt->fetch()) {
    echo $table;
}
$stmt->close();

How to display all table names in a database?

The $row is coming as an array, so you have to fetch it as

echo $row['Tables_in_test']

How to list all tables in MySQL database which match a keyword and ended with year?

You can try something like this,

SELECT table_name, table_type, ENGINE
       FROM information_schema.tables
       WHERE table_schema = 'your schema name' AND table_name REGEXP '[[:digit:]]$'AND table_name LIKE 'bl_pelanggan%'
       ORDER BY table_name;

list all tables in a database with MySQLi

There are many ways.

SHOW TABLES

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

List all the tables in your MySQL database using PHP

Use db in your connection

 mysqli_connect($host, $user, $pass, $dbname);

And use query like this

 $sql = "SHOW TABLES";

---

Related Topics