Select tables from DB using php

Question

I have the below code to display a list of all tables in my database.

$table_count = mysql_query("
    SELECT TABLE_NAME 
    FROM information_schema.tables 
    WHERE table_schema = 'DB_NAME'
"); 
$tables = array();
while($row = mysql_fetch_array($table_count))
{
    $table = $row["TABLE_NAME"];
}

Is it possible to change this query to show all tables EXCEPT some?

If so can someone explain/show me how to do it?

baao · Accepted Answer · 2015-08-03 16:56:02Z

2

Sure you can. You can either select some columns

SELECT foo, bar, baz from ...

or you specify where not by chaining your conditions with AND or OR

$table_count = mysql_query("
SELECT TABLE_NAME AS tableName, another_complicated_column as niceName 
FROM information_schema.tables 
WHERE table_schema = 'DB_NAME' 
AND another_column NOT LIKE 'FOOBARBAZ' 
AND another_another_column != 333
");

edited Aug 3, 2015 at 16:56

answered Aug 3, 2015 at 16:52

baao

73.5k18 gold badges152 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shane Over a year ago

Perfect answer, thank you. I dont know if I need to ask this as another question but is it possible to show the table names "nicer"? EG instead of client_info show Client Information

samayo Over a year ago

Yes, just use as keyword. Not sure if you are allowed spaces though

baao Over a year ago

Well, yes you can do this, though you shouldn't use whitespaces. Whitespaces will make handling the data really hard, and I'm not really sure if you can use them either way. I would go with camelCase or lodash only @user3092953

baao Over a year ago

I've edited it in my answer in the second select block to show you how to give an alias to a column with AS

Shane Over a year ago

Thank you, great help :)

philosp · Accepted Answer · 2015-08-03 16:52:36Z

1

If you change your query, you could filter out tables by name, for example:

$table_count = mysql_query(
"SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'DB_NAME' AND TABLE_NAME NOT LIKE 'TMP_%' "
);

This would filter out tables that begin with TMP_, leaving all others.

answered Aug 3, 2015 at 16:52

philosp

364 bronze badges

Collectives™ on Stack Overflow

Select tables from DB using php

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related