Why SHOW TABLES Isn't Returning Data in Your PHP Script
If you are expecting a list of database tables from your PHP script but only see the literal text "These are your tables:" followed by nothing, the issue lies in how the query results are being accessed. The code snippet provided attempts to use the deprecated mysql_* extension and constructs a query string that is syntactically incorrect for MySQL.
The core problem starts with the query syntax itself. In the provided code:
$result = mysql_query('SHOW TABLES [FROM db_name] [LIKE '%']');
MySQL does not understand the bracketed placeholders [FROM db_name] or [LIKE '%']. Those are likely artifacts of a search result template rather than valid SQL keywords. The correct syntax for displaying all tables in a specific database is simply SHOW TABLES;. If you want to filter them, you append LIKE 'pattern', but the brackets must be removed entirely.
Furthermore, even with the corrected query string, mysql_query() does not return a list of strings directly. It returns a MySQLResult resource (or boolean false on failure) that acts as a cursor over the result set. You cannot simply echo this resource to see the data; you must iterate through it using functions like mysql_fetch_assoc(). When the code executes echo $result; on an invalid query or a resource object, PHP outputs the string representation of the error or the object type, which often looks like empty text if the error handling is silent or if the connection details are placeholders.
Correcting the Query Syntax and Result Handling
To fix this immediately, you must remove the deprecated mysql_* functions. This extension has been removed in PHP 7.0 and later versions, meaning any code running on modern servers will fail to connect at all. You need to migrate to either MySQLi or PDO. Assuming a migration to MySQLi for simplicity, here is how the logic should be structured:
$host = "sql2.njit.edu";
$username = "username";
$password = "password";
$dbname = "db_name";
// Establish connection using mysqli
$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Corrected query: No brackets, no placeholders in the string
$sql = "SHOW TABLES";
$result = $conn->query($sql);
echo 'These are your tables:<br>';
// Check if the query was successful
if ($result && $result->num_rows > 0) {
// Loop through the result set
while($row = $result->fetch_assoc()) {
// The table name is stored in the 'Tables_in_dbname' column
echo htmlspecialchars($row['Tables_in_db_name']) . "<br>";
}
} else {
echo "No tables found or query failed.";
}
The critical change here is the while loop. MySQL returns rows where the column name depends on the database name (e.g., Tables_in_db_name). If you try to access $row['Table'], it will fail because that column name doesn't exist in this specific result structure. You must use $row['Tables_in_db_name'].
Security Implications of Hardcoded Credentials
While debugging the query logic is essential, the authentication method in your original snippet presents a significant security vulnerability known as hardcoded credentials. Checking $_REQUEST["username"] against a static string like "coke" inside your application code exposes your database access directly to anyone who can inspect the source or guess the parameters. If this script is accessible via a URL, an attacker can simply change the URL parameters to execute any SQL command available to that user account.
In a production environment, you should never hardcode usernames and passwords in source files. Instead, store these credentials in an environment-specific configuration file (like .env) or retrieve them from a secure secrets manager. Additionally, always use prepared statements when interacting with database data to prevent SQL injection attacks, even if you are just displaying tables.
Modernizing the Stack
The transition from mysql_* to PDO offers better security features and consistency across different databases. If you are building a new application or refactoring an old one, consider using Laravel. The framework handles database connections, migrations, and query building with robust safety defaults that prevent many of these common pitfalls automatically. You can explore further resources on modern PHP development practices at https://laravelcompany.com to see how structured configurations simplify database management compared to raw connection strings.
By correcting the SQL syntax, implementing proper result fetching, and securing your connection parameters, you will be able to reliably list your database tables without encountering empty outputs or security risks.
