PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, February 22, 2022

[FIXED] phpmyadmin - How make (displayed) column widths narrower than column header?

 February 22, 2022     mysql, phpmyadmin     No comments   

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
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing