[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

phpMyAdmin: How to Search Database for Header Titles

In phpMyAdmin, when I perform a search, it does not return matches for Table Header strings.

EXAMPLE
-------------
Search for string: Approved
All Tables.
Result returns text in data fields only. No headers are found that match that string, yet they do exist.

QUESTION
----------------
How do I perform a search for "Approved" among Headers of the tables?

.
0
WizeOwl
Asked:
WizeOwl
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You mean column and table names? You will have to query the information_schema for that. If you want just table names, then you would use the tables view. If you want either, then I would search the columns view for column_name or table_name equal to or like the string you are searching on.
0
 
WizeOwlAuthor Commented:
My intention was to say, Column Headers. After selecting the database, Localhost > db_name, I then click on the tab called, Search. (see attached screen capture)

Where do I find the "Columns View" ?  or "Tables View" ?

 Search Screen
0
 
Kevin CrossChief Technology OfficerCommented:
Those are system views in the INFORMATION_SCHEMA. If you are using a built-in search function of phpMyAdmin, then you may not have that ability. I was responding with what you would do in MySQL itself.
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.

 
WizeOwlAuthor Commented:
It is on a client server running CentOS, so the only GUI that I know of is from phpMyAdmin.

How would I access mySQL directly, and how would I initiate a search using "Tables View" or "Columns View"?  Maybe I'll need a primer on mySQL queries, please recommend one. Can you also give an example using the string, "Final Approval".
0
 
Kevin CrossChief Technology OfficerCommented:
You could do so from the CentOS command line, but by MySQL directly I am meaning SQL syntax within MySQL; therefore, you would use phpMyAdmin and run a SQL query.

For example, to find the table that contains a specific column:
SELECT TABLE_SCHEMA, TABlE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'Final Approval'
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I guess the question I should have asked you is WHY are you searching for this string? WHAT is your end goal or need from phpMyAdmin or MySQL? I went after answering the question, but if you are unfamiliar with MySQL this may not be what you are actually looking to do. Therefore, please shed some light on the scenario that led you to searching the database for this string.
0
 
WizeOwlAuthor Commented:
PURPOSE
-------------
I want to locate all tables in a particular database that contain a particular Column Header, use "Final Approval" as an example. If this can be done across multiple databases, I would also want to do that.

(Please Clarify: Can column headers have upper and lower case characters and a space between words? If not, then just use "approved" as the sample search string.)
0
 
Kevin CrossChief Technology OfficerCommented:
It is not a good practice to add spaces. Column names can be a mix of case. Most collation for MySQL is case insensitive, but just in case I am just generalizing and case sensitivity proves to be an issue for you -- http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html -- you can use COLLATE to set case insensitive collation for your search without impacting the overall server collation.

0
 
WizeOwlAuthor Commented:
Thank you. That is what I was looking for.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now