Sorting/Limiting Table List MySQL and PHP

Question

I want to make select and print out all of the tables I have (I got that so far), and then limit it using `` and then ordering them by table name, and have 10 results per page.

How would I go about doing that? I know how to do it getting data from tables, but I don't know how to do it using just tables.

I have this so far:

function list_tables($type){

$sql = "SHOW TABLES FROM example";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result)){
    $table_name = $row[0];
    echo $table_name; //edited out a lot to keep it simple
    //I'm just printing out a lot of data based on table name anyway
}
mysql_free_result($result);
}

So far, it only prints out all of the table names (+ extra info I print for table names) all in the same page and it's getting the the point where it takes forever to scroll. I'd like to limit it to about 10-20 posts per page instead of a few hundred posts on one page.

Thanks in advanced if anyone can help me. Much appreciated.

Try this: http://stackoverflow.com/questions/11635769/how-to-limit-show-tables-query — u_mulder, Nov 17 '14 at 09:08
Do you want to do pagination? if so then [checkout this tutorial](http://www.freezecoders.com/2014/01/simple-pagination-using-php-mysql.html) — Fas M, Nov 17 '14 at 09:15
I like that tutorial, however I have something similar to that already for data inside of tables, I just need to be able to do do it with tables themselves. As in like, list all of the tables in a database. I sat here for hours trying to figure it out and I can't seem to do it lol. — Axiom, Nov 17 '14 at 09:21

score 0 · Answer 1 · answered Nov 17 '14 at 09:33

Calculate the offset and limit according to the page number and try the below query:

function list_tables($type, $offset, $limit){

$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'example' ORDER BY TABLE_NAME LIMIT $offset, $limit";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result)){
    $table_name = $row[0];
    echo $table_name; //edited out a lot to keep it simple
    //I'm just printing out a lot of data based on table name anyway
}
mysql_free_result($result);
}

score 0 · Answer 2 · answered Nov 17 '14 at 09:38

0

Use below given query which supports LIMIT so you can do pagination with your table names.

select * from information_schema.tables LIMIT 5

answered Nov 17 '14 at 09:38

Lalit Sharma

555
3
12

score 0 · Accepted Answer · answered Nov 17 '14 at 10:01

i did this:

function list_tables(){
$amtperpage = 15;
$sql = "SELECT COUNT(TABLE_NAME) FROM information_schema.tables WHERE TABLE_SCHEMA = 'my_dbname'";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$total_rows = $row[0];

//pagination stuff here
if(isset($_GET['p'])) $curpage = intval($_GET['p']); else $curpage=1;
$start = abs(($curpage-1)*amtperpage);
$sql = "SELECT TABLE_NAME FROM information_schema.tables ORDER BY TABLE_NAME ASC LIMIT $start,$per_page";
$res = mysql_query($sql);
while($row=mysql_fetch_array($res)) $DATA[++$start]=$row;
$uri = strtok($_SERVER['REQUEST_URI'],"?")."?";
$tmpget = $_GET;
unset($tmpget['p']);
if($tempget){
    $uri .= http_build_query($tmpget)."&";
}
$num_pages=ceil($total_rows/$amtperpage);
for($i=1;$i<=$num_pages;$i++) $PAGES[$i]=$uri.'p='.$i;
?><div id="container">Pages: 
foreach ($PAGES as $i => $link){
if($i == $curpage){
=$i
} else {
?><a href="<?=$link?>"><?=$i?></a>
}
} ?>
foreach($DATA as $i => $row){
$table_name = $row[0];
//use my functions to get data for each table name and list it and such
}
}

this is highly butchered since I have a lot of stuff that would've gotten in the way of the point but this should work. Thanks to the people who helped me. :)

Sorting/Limiting Table List MySQL and PHP

3 Answers3