I currently have the following query. Which fetches all column names for a table. But when there are 2 tables with the same name (in different databases) it fetches those column names as well.

In the following query $table of course means the requested table. In this case for example customer.

select column_name from information_schema.columns 
where lower(table_name)=lower(\''.$table.'\')

So my question is: How do I specify which database the query should look into.

  • What DBMS are we talking about? – mustaccio May 7 '19 at 15:51
  • I use PHPmyadmin – Granny May 7 '19 at 16:02
  • "PHPmyadmin" is not a DBMS. – mustaccio May 7 '19 at 16:18
  • Sorry I misunderstood your question. Then it is MySQL – Granny May 7 '19 at 16:26
  • You should use the mysql CLI to do your database work. If a query fails there, then ask a question here. If it works there and fails in PHP or whatever tool you're using, then it's a programming question. Always ensure that it's not a database issue by using the mysql client first. Saves a lot of bother! – Vérace May 7 '19 at 19:59

In MySQL database is a synonym for schema. Subsequently, the information you're looking for is in the column table_schema:

select column_name from information_schema.columns 
where lower(table_name)=lower(\''.$table.'\')
  and lower(table_schema)=lower(\''.$mydb.'\')
  • Awesome that works! I didn't understand how to filter on the database. Do you have a good recourse where I can read more about this? – Granny May 7 '19 at 16:41
  • 1
    The best resource is usually product documentation – mustaccio May 7 '19 at 17:00
  • Awesome! Thank you! – Granny May 7 '19 at 17:26
  • And the use of LOWER() is probably redundant with what the collation will do. – Rick James May 8 '19 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.