asked on

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Insoftservice inso

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Bink

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=@
UNION 
SELECT 'name', name FROM MyTable WHERE ID=@
UNION
SELECT 'value1', value1 FROM MyTable WHERE ID=@
UNION
SELECT 'value2', value2 FROM MyTable WHERE ID=@
UNION
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.

projects

ASKER

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 Bink

>>> 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.

projects

ASKER

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 Bink

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.

projects

ASKER

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 Bink

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.

projects

ASKER

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.

SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial