?
Solved

mySQL 'SHOW TABLES'  << HOW TO show the tables in DESCENDING order.

Posted on 2009-12-28
7
Medium Priority
?
871 Views
Last Modified: 2013-12-12
hello, i have a simple PHP script that displays all of the tables in the mySQL database.

By default it loads the tables in ASC order (a-z, 1-9)    ((with the script i have below in the snippet))

QUESTIONS .. how do i make it load the tables in DESCENDING order?

I tried this below.. but it did not work....

$sqlString = "SHOW TABLES order By DSC";

does anybody know how to do this?


//WORKING VERSION THAT LOADS THE TABLES IN ASCENDING ORDER BY DEFAULT
$sqlString = "SHOW TABLES";
 
$result = mysql_query($sqlString) ;
 
$cant = 0;
while ($tables = mysql_fetch_array($result)) {
	
     //printf("album$cant= %s %s",urlencode($tables[0]),"&");
	 printf("albumName%s=%s&", $cant, urlencode($tables[0]));
	 $cant++;
}
echo "cant=$cant";
?>

Open in new window

0
Comment
Question by:sam928
  • 3
  • 2
6 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26132078
I think you would have to sort an array of table names to get this in descending order.  I'll try it.
0
 
LVL 83

Accepted Solution

by:
leakim971 earned 1000 total points
ID: 26132120
Hello sam928,

Sort a PHP array : http://www.php.net/manual/en/function.sort.php

Regards.



$sqlString = "SHOW TABLES";
$result = mysql_query($sqlString) ;
$cant=0;
$a = array();
while($tables = mysql_fetch_array($result)) {
         $a[$cant] = urlencode($tables[0]);
         $cant++;
}
sort($a);
for($i=0;$i<sizeof($a);$i++) printf("albumName%s=%s&", $i, $a[i]);
echo "cant=$cant";
?>

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26132122
This worked for me.  You can probably adapt it pretty easily.  Best, ~Ray
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 83

Expert Comment

by:leakim971
ID: 26132153
Sorry Mr Paseur, I've not refreshed the page before posting.
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 26132166
@leakim971: No apology needed at all.  Looks like EE did not take my code snippet!

I'll try again...
<?php // RAY_show_tables_desc.php
// SPEW OUT INFORMATION FROM THE DATA BASE

// CONNECT TO THE DB
$db_host	= "??"; // PROBABLY localhost
$db_name	= "??";
$db_user	= "??";
$db_word	= "??";


// CONNECT TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
	$errmsg	= mysql_errno() . ' ' . mysql_error();
	echo "<br/>! db_connection ";
	echo "<br/> $errmsg <br/>";
}

// SELECT THE DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
	$errmsg	= mysql_errno() . ' ' . mysql_error();
	echo "<br/>! db_sel ";
	echo "<br/> $errmsg <br/>";
	die('NO DATA BASE?');
}


// GET AN ARRAY OF TABLE NAMES
function get_my_db_tables()
{
	global $db_connection, $my_dbt_prefix;
	$sql_t	= "SHOW TABLES";
	if (!$t = mysql_query("$sql_t", $db_connection)) { fatal_query_error($sql_t); }
	if (mysql_num_rows($t) == 0)
	{
		return FALSE;
	}
	while ($show_tables	= mysql_fetch_array($t))
	{
		$my_tables[]	= $show_tables[0];
	}
	return $my_tables;
}


// CRAP OUT AND DIE ON FATAL MySQL ERRORS
function fatal_query_error($qstring)
{
	$url	= $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
	$ref	= $_SERVER['HTTP_REFERER'];
	$errmsg	= mysql_errno() . ' ' . mysql_error();

	echo "<br /><br /><span class=\"loss\">FATAL MySQL Data Base Error</span> \n";
	echo "<br /><br />The URL for this page is:<br />$url \n";
	echo "<br /><br />The failing MySQL Query contained this:<br />$qstring \n";
	echo "<br /><br />The error message is:<br />$errmsg<br /><br />\n";
	echo "<br /><br />The referrer (if any) is:<br />$ref<br /><br />\n";

}
/* ************************************************************************** */

// FOR READABILITY
echo "<pre>\n";

// GET THE LIST OF TABLES
$my_tables = get_my_db_tables();

// SORT TABLES MAN PAGE: http://us3.php.net/manual/en/array.sorting.php
rsort($my_tables);

// ITERATE OVER THE LIST OF TABLES
foreach ($my_tables as $my_table)
{
	echo "\n$my_table";
}
?>

Open in new window

0
 
LVL 21

Expert Comment

by:K V
ID: 26137824
The mysql way:

select table_name from information_schema.tables where table_schema=database() order by table_name desc;
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question