MySQL & PHP- Show Tables In Database
Someone asked me the other day how to display the existing tables in their database with a PHP script. The query itself is pretty simple, but people seem to get confused about the PHP side of it. Then again, once you look at the code, you might be surprised how easy it really is…
Printing tables with PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <? $showtablequery = " SHOW TABLES FROM [database] "; $showtablequery_result = mysql_query($showtablequery); while($showtablerow = mysql_fetch_array($showtablequery_result)) { echo $showtablerow[0]."<br />"; } ?> |
That query will show ALL tables, but you can use a LIKE function in the query as well, to get particular tables only.
Printing particular tables with PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <? $showtablequery = " SHOW TABLES FROM [database] LIKE '%brightcherry%' "; $showtablequery_result = mysql_query($showtablequery); while($showtablerow = mysql_fetch_array($showtablequery_result)) { echo $showtablerow[0]."<br />"; } ?> |
That query will pull out all tables with the word ‘brightcherry’ in.
brightcherry.co.uk
Hey man might sound like a silly question im new to php and all – but why does the array only print out properly when you specify the key as 0?
echo $showtablerow[0]
does the key increment in the while loop automatically?
Hey Ben,
The 0 doesn’t increment. 0 is the index- it’s calling the first element in the array, which is the tablename.
You could rename the variable to something like this:
$tablename = $showtablerow[0];
echo $tablename;
Now $tablename will print the tablenames.
its giving me an error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/08/7066208/html/rifa/datos.php on line 16
any idea?
Hi Jorge,
I suspect your query is wrong, and/or you don’t have an open connection to your DB.
Hello Sir !
If I have a database with many tables(100), and I want to show only 15 tables on page, how to do that ?
Thank you very much in advance
George, You should add LIMIT 15 to the end of your query to show the first 15. For the next page, use LIMIT 15,15, then LIMIT 15,30, etc. with the first number being the offset.
Or, actually the second number is the offset, sorry. The first number is the amount of rows to use.