we use " SHOW TABLES FROM " to list tables from database but there is no way to list table in order (ORDER BY dont work with tables) is ther any other way to arrange it ?? can we do this with php :if yes please give me hint to do it thank you
2 Answers
You can use INFORMATION_SCHEMA:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
ORDER BY table_name ASC
Reference: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
3 Comments
ravi
BUT the order i am getting is like this :
ravi
class_1 class_10 class_11 class_12 class_2 class_3 class_4 class_5 class_6 class_7 class_8 class_9 which is not in asc order
George Cummins
That is ascending order, alphabetically. If you want to order based on the numbers appended to the end of the tables, you would need to split the table names into substrings, and order based on those substrings.
Another option that doesn't rely on INFORMATION_SCHEMA:
Set up an array to store table options:
$tableArr = array();
Get the tables from the desired database:
$tableQuery = mysql_query("SHOW TABLES FROM databasse");
Add each table to your array:
while($row = mysql_fetch_array($tableQuery)) {
$tableArr[] = $row[0];
}
Now sort the array using asort (alphabetical) or arsort (reverse alphabetical):
asort($tableArr);
Now you can list the tables in alphabetical order by simply looping through the array:
foreach($tableArr as $table) {
echo "<li>$table</li>";
}
