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.
For people that are using PDO statements
$query = $db->prepare('show tables');
$query->execute();
while($rows = $query->fetch(PDO::FETCH_ASSOC)){
var_dump($rows);
}
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.
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
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.
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.
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.
//list_tables means database all table
$tables = $this->db->list_tables();
foreach ($tables as $table)
{
echo $table;
}
Source: Stackoverflow.com