Solved

shell script to extract mysql data

Posted on 2014-11-17
19
286 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 82

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
 
LVL 82

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 82

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 82

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

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 82

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 82

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 82

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 82

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now