• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Mysql command line display examples

From the Linux (centos in this case) I need to compare some mysql entries and looking from left to right is not an option.
I need to putt the data from long rows, displaying the column name and it's value in the form of a list I can latter compare side to side using another tool.

I need the output formatted so that the first column is the field name and the second columns are the values.
To make things more readable, optionally, the spacing between the two columns should be based on the longest field name I would suppose.

For example

ID                                 34
name                          something
date                            somedate
value1                        somevalue
value2                        somevalue
value3                        somevalue

Anyhow, I'm looking for a bash script to do this as I am not a programmer.

Thanks very much.
3 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is not "possible" in a normal sql statment, neither in mysql nor in other dbms languages.
this could be done in some reporting tools more easily (transpose)
I understand your need, and I would suggest that you do this rather in phpmyadmin, for example, which, as far as I remember, has such a record display.
alternatively you could use ms access, for example, create the linked table, and map a form with the requested layout (also available as standard option there) for your table.
This is not rdbms functionality it can be easily done in NOSQL ,Redis and hypertable.
For doing this thing in rdbms you have to create one more column
name as id
ID    column_name column_value
34    Name               something
34    date                  something
34    ---                        ---
Steve BinkCommented:
Angel is generally correct, but there is a really crappy way to do this - UNION.  Something like this:
SELECT 'id' AS Field_Name, id AS Field_Value FROM MyTable WHERE ID=@
SELECT 'name', name FROM MyTable WHERE ID=@
SELECT 'value1', value1 FROM MyTable WHERE ID=@
SELECT 'value2', value2 FROM MyTable WHERE ID=@
SELECT 'value3', value3 FROM MyTable WHERE ID=@;

Open in new window

The bash script would take your id value as the parameter.  It would do a text replace, switching the '@' with the value, then run the query.  The results would come out pretty similar to what you want.

That said, your formatting requirement are oddly specific for just wanting to get some data.  You might want to consider an actual reporting engine - PHPMyAdmin and MSAccess can both fill the role..  sort of..  Maybe if you explain a little bit more about your end goal, we can recommend a better process for you to follow.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

projectsAuthor Commented:
It doesn't have to be as specific as I posted but I find it's better to try and be as specific as possible when posting on this site.

I know there is no way to do this using mysql cli or phpmyadmin which is why I asked for a script. Basically, as you (Steve Bink) mention, that is the idea. Read a column, print column name then the value, read another column, print the column name then the value.

I don't want to use another program, I just need a quick and dirty script to do this because we need to compare tables and contents now and then and a side to side comparison is the only way to get it done.
Steve BinkCommented:
>>> we need to compare tables and contents now and then and a side to side
>>> comparison is the only way to get it done.

What is the need behind the comparison?  What actions are taken based on the comparison results?

Another possibility is to do full-row comparisons using diff, and leverage grep to programmatically identify the differences.
projectsAuthor Commented:
The need is simply to compare a large number of tables when trying to debug certain problems. The only way to do it is by looking at each column and its value which is hard to do in any way but a simple text output.

With that output, we can enter it into something like a side by side compare tool and more easily find problems.
Steve BinkCommented:
I would just use "SELECT *" for whichever IDs you want to compare, then paste them into Excel.  They won't be side-by-side, but they will be visible rows next to each other.  For that matter, a decent client, such as HeidiSQL would let you do the same thing.

BTW, if you have SSH access, but MySQL is not exposed publicly, you can use PuTTY to create an SSH tunnel to allow clients like HeidiSQL to do their work.
projectsAuthor Commented:
That's too much work. It's easier to create a script once and have it output what I can paste into a compare tool.
Yes, command line of course but I noticed I forgot a word when writing up my question.

>From the Linux (centos in this case) I need to compare
was supposed to be
From the Linux (centos in this case) cli, I need to compare
Steve BinkCommented:
If you're unwilling to use another client, and also do not want to use a third-party application to assist, then you're stuck with the UNION strategy I outlined earlier.
projectsAuthor Commented:
That doesn't seem to make any sense.
I'm sure I can write a simple script that pulls each columns value into a single line in a file.
The problem is that not being a programmer, I would probably not do it as well as someone who is could.
Steve BinkCommented:
As Angel commented originally, what you want is not how RDBs are designed to work.  Insoftservice suggested the possibility of using other, non-relational systems - those may work better for this, but you'll also have to contend with porting your data into it.  My suggestion of using "UNION" is as close as you'll get using SQL.  Otherwise, any script you write is going to boil down to "SELECT field1; print it; SELECT field2; print it; etc..", which is just the UNION method done the hard way.

So, short answer, yes, a script is possible.  I just feel that is a lot more work than just looking at the two rows in something like Excel...  using a pair of pliers to turn a screw.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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