I am trying to display all table names in my database. This is my code:
<?php
define('DB_HOST', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'nucleus');
$db=new mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD,DB_NAME);
$query = $db->prepare('SHOW TABLES FROM `nucleus`');
$query->execute();
while($rows = $query->fetch()){
echo " $rows";
}
?>
All it is displaying 1 for every entry, I don't know why. Any solution for this?
To get the name of all tables use:
SELECT table_name FROM information_schema.tables;
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>';
For more details see: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
So, anyone looking for possible answer, here it is:
<?php
$query = $db->prepare('SHOW TABLES FROM `databasename`');
$query->execute();
$query->bind_result($tables);
while( $query->fetch()){
echo " $tables";
}
?>
$rows is an array, not a string. echo $rows['TABLES'];... or output $rows and see what the index is.SHOW TABLE STATUS
if the connection already made, no need to mention the db name
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();
I did a complete solution working, there are two ways to get SQL results, I preferred precedence to get results, $ROW will receive array data to show:
<?php
$connection=conectadb();
$sql = 'SHOW TABLES FROM pmsai';
$result = $connection->query($sql);
if (!$result) {
echo "DB Error, could not list tables\n";
echo 'MySQL Error: ' . mysqli_error();
exit;
}
while ($row = $result->fetch_array()) {
echo "<p>Table: {$row[0]}</p>";
}
mysqli_close($connection);
?>
You can do this in 2 ways:
# Select a DB
SELECT db_name
# See Selected DB
SELECT DATABASE();
# See list of tables
SHOW TABLES;
See table list without switching selected Table
SHOW TABLES FROM database_name;
