Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql command line display examples

Posted on 2016-07-14
11
Medium Priority
?
109 Views
Last Modified: 2016-07-19
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.
0
Comment
Question by:projects
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 41711714
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.
0
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 500 total points
ID: 41711743
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    ---                        ---
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41711744
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.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:projects
ID: 41713258
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.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41713312
>>> 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.
0
 

Author Comment

by:projects
ID: 41713637
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.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41713763
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.
0
 

Author Comment

by:projects
ID: 41714626
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
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 41714713
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.
0
 

Author Comment

by:projects
ID: 41719812
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.
0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 500 total points
ID: 41719885
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question