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
Solved

shell script to extract mysql data

Posted on 2014-11-17
19
294 Views
Last Modified: 2014-11-20
I need a bash script which does the following.

-reads from one table in mysql
-extracts data from one field if another field is a 1
-writes output to a file, each result on a new line
-moves on to next row

example;
table: clients

Each client has a row of information. We want to read each row and determine if we need to output
if field 'test' = 1 then read 'location' field and put that into a file on a new line.
Move on to next row.

If possible, reading a large number of rows into memory to save on number of reads because table could contain thousands of clients if not tens of thousands.
0
Comment
Question by:projects
  • 10
  • 9
19 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40448750
Since you need a driver to connect to MySQL, PHP or Perl on the command line are good choices.
0
 

Author Comment

by:projects
ID: 40448757
Yes, using 'mysql' from the bash script
0
 

Author Comment

by:projects
ID: 40451501
Is that what you meant?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40451657
No.  PHP and Perl are complete programming languages that can do what you want.  The 'mysql' command line program is only an interface for the MySQL server and is not nearly as complete.  I know I could do it in PHP but I don't think that 'mysql' can do all of what you want.
0
 

Author Comment

by:projects
ID: 40452487
Right now, I just need this as a bash script, safe or unsafe, then later it will get incorporated into the main php code.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40453357
There is no generic driver to communicate from 'bash' to MySQL.  That's why I keep suggesting PHP and Perl.
0
 

Author Comment

by:projects
ID: 40453507
Not sure what you are talking about?
I use mysql inserts and queries in bash scripts all the time.
Of course this can be done from a bash script.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40453528
Go ahead then, I guess you know more about it than I do.  But I will point out that if you are running the 'mysql' command line program, you are Not in a bash script.
0
 

Author Comment

by:projects
ID: 40453896
I didn't say I know more than you and if I did, I'd be the one answering the questions instead of posting them :)

I said that I don't understand your comment because I am using mysql calls in many of my bash scripts.

Here is just one example;

echo "Adding name/password to htaccess table"
mysql -u user -ppass -D someDB -h localhost -e "insert into htaccess (username,password) VALUES ('$TEMPM
AC','$PASS_CODE') ON DUPLICATE KEY UPDATE password='$PASS_CODE';"

Open in new window


I use this every single day. There is another line in there which updates another table as well. These things are in a bash script.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40453919
That's fine... but I don't see the ability to go thru the tables and extract data to a separate file in the 'mysql' command line program.  And while they are 'in a bash script', I think it is more accurate to say that you are running 'mysql' with a bash script.  There is nothing about 'bash' itself that will do that.

I don't remember at the moment how to do it, but you can write an SQL query that will conditionally extract data from one table and put it in another table.  Basically it's an INSERT from a SELECT that is part of the query.  More info on "INSERT ... SELECT" here: http://dev.mysql.com/doc/refman/5.6/en/insert.html
0
 

Author Comment

by:projects
ID: 40453940
Semantics I guess but yes, I am running command line mysql commands from a bash script and no, bash doesn't have any means of it's own to communicate with mysql.

I simply want to read a table, if field x = 1, then read field y and write it's content to a file.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40454160
Like I said, I don't think 'mysql' can do that directly.  You may be able to use the "INSERT ... SELECT" to create another table and export that.  I would (and do) use PHP on the command line for all sorts of things like that.  But then I program in PHP and MySQL almost everyday and I almost never use shell scripts like 'bash' except as the terminal interface.
0
 

Author Comment

by:projects
ID: 40454231
I'm still confused with your reply here. I can read and write to MySql databases from a bash script all day long. I already am doing exactly that. Yet you keep telling me that it can't be done?
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 40454321
No, that's not what I'm saying.  What I am saying is the extra steps you want of extracting a field under specified conditions and writing to a file (instead of a table) is not something that I think 'mysql' will do.  It is almost trivial to do in PHP.
Here is a simple PHP program to do what you are asking about.
<?php
//$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
$link = new mysqli("10.202.46.41", "CtlDave", "Ctl4me", "db373996086");

/* check connection */
if (!$link) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

$filename = 'thelist.txt';
$fp = fopen($filename, "w");

/* Select queries return a resultset */
if ($result = $link->query("SELECT * FROM stats_q WHERE qid = '72'")) {
	while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
		echo $row['sid']."\r\n";
		fwrite($fp, $row['sid'].",");
		}
	}
/* free result set */
$link->close();
fclose($fp);

?>

Open in new window

0
 

Author Comment

by:projects
ID: 40455016
I could use php instead, that isn't an issue. I'm just so used to doing things using bash scripts lately.

That works fine, well, almost at least :)

When the output is read on the screen while running, then it is exactly how I would need it in the file

1.2.3.4
2.3.4.5
3.4.5.6

But when looking at the file, it's
1.2.3.4,2.3.4.5,3.4.5.6

I think that is fine. I'll award this question then post another one using the results of this one because now I need to take this output and apply it to iptables without giving php root access to iptables.

Fun :)
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40455734
Use "\r\n" after $row['sid'] instead of ",".
fwrite($fp, $row['sid']."\r\n");

Open in new window

0
 

Author Comment

by:projects
ID: 40456279
Making that change works but puts control characters in the file at the end of each line;

1.2.3.4^M
2.3.4.5^M
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40456310
Then, since it is Linux, just use '\n'.
0
 

Author Comment

by:projects
ID: 40456345
Perfect, thanks!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql disables rename 4 93
MS Access - need to reduce row size 25 60
SIP / Streaming - real time communications testing 8 95
AWK: Pythagoras bp script 18 27
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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