Issue
Using phpmyadmin to manage a MySQL database.
When designing our database, we made descriptive column names. These tend to be fairly long, even if the column contents are quite narrow.
Is there a way to tell phpmyadmin to display columns, with width based on column contents, ignoring column headers?
I would like to fit more useful data on my monitor, not have to scroll sideways as much.
For a given table, I know how to write custom queries that use shorter names in the result, but that is tedious: SELECT long_name ln, another_column ac, ..
.
Best answer would be some option in phpmyadmin.
I would accept an answer with a mysql query that uses meta data of table to simply shorten all column names (in the result, not changing the table), if that can be done without having to manually mention all the column names. Its okay if some columns end up with duplicate display names - if phpmyadmin can still display the result.
Not acceptable (for my purpose) would be an answer that entirely hides headers, or replaces them with names that are not derived from the column names - e.g. 1, 2, 3 or a, b, c arbitrarily - would be non-obvious which column was which when looking at the output.
Solution
A partial solution which was good enough for my purposes.
Step 1:
-- USE mydatabase;
SET @columnList = (SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.COLUMNS WHERE TABLE_NAME='mytable');
SET @query = CONCAT('SELECT ', @columnList, ' FROM mytable');
SELECT @query;
Step 2: Manually edit the result from above, into phpmyadmin's SQL query box.
OR into '...' below to build a prepared statement:
SET @query = '...';
PREPARE stmt FROM CONCAT(@query, ' LIMIT ?');
SET @limit = '50';
EXECUTE stmt USING @limit;
DEALLOCATE PREPARE stmt;
Note that without the limit, ALL rows are sent by EXECUTE
to phpmyadmin for display, which will likely timeout and/or exceed some limit. So for SELECT query, include a limit.
(A complete solution would create short aliases for long column names fed into GROUP_CONCAT
. I didn't take the time to figure out that step; I just manually truncated some by aliasing them; columns I didn't care about I deleted or moved to the end.)
Answered By - ToolmakerSteve
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.