CODEWITHSUNDEEP
phpmysqlsql
EGHDK
EGHDK

Reputation: 18120

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.

<?php
if ($_REQUEST["username"]=="coke"&&$_REQUEST["password"]=="pepsi"){
echo 'You have successfully logged in.';
echo '<br />';
echo 'These are your tables:';
echo '<br />';

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

I get no errors. The output is exactly what's echoed, but no table names.

Upvotes: 13

Views: 144556

Answers (8)

Mizanur Rahaman
Mizanur Rahaman

Reputation: 19

//list_tables means database all table 

$tables = $this->db->list_tables();
foreach ($tables as $table)
{
  echo $table;
}

Upvotes: 0

Joshua Martell
Joshua Martell

Reputation: 7202

Queries should look like :

SHOW TABLES

SHOW TABLES FROM mydatabase

SHOW TABLES FROM mydatabase LIKE "tab%"

Things from the MySQL documentation in square brackets [] are optional.

Upvotes: 1

braumer
braumer

Reputation: 85

Sure you can query your Database with SHOW TABLES and then loop through all the records but that is extra code lines and work.

PHP has a built in function to list all tables into an array for you :

mysql_list_tables - you can find more information about it at The PHP API page

Upvotes: 0

Sharpless512
Sharpless512

Reputation: 3222

For people that are using PDO statements

$query = $db->prepare('show tables');
$query->execute();

while($rows = $query->fetch(PDO::FETCH_ASSOC)){
     var_dump($rows);
}

Upvotes: 14

octern
octern

Reputation: 4868

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

Upvotes: 48

user622367
user622367

Reputation:

The brackets that are commonly used in the mysql documentation for examples should be ommitted in a 'real' query.

It also doesn't appear that you're echoing the result of the mysql query anywhere. mysql_query returns a mysql resource on success. The php manual page also includes instructions on how to load the mysql result resource into an array for echoing and other manipulation.

Upvotes: 1

ahmetunal
ahmetunal

Reputation: 3950

SHOW TABLES

will show all the tables in your db. If you want to filter the names you use LIKE and wildcard %

SHOW TABLES FROM my_database LIKE '%user%'

will give you all tables that's name include 'user', for example

users
user_pictures
mac_users

etc.

Upvotes: 2

Al_
Al_

Reputation: 2509

you need to assign the mysql_query to a variable (eg $result), then display this variable as you would a normal result from the database.

Upvotes: 0

Related Questions