shell script to extract mysql data

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.
projectsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Since you need a driver to connect to MySQL, PHP or Perl on the command line are good choices.
0
projectsAuthor Commented:
Yes, using 'mysql' from the bash script
0
projectsAuthor Commented:
Is that what you meant?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Dave BaldwinFixer of ProblemsCommented:
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
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
There is no generic driver to communicate from 'bash' to MySQL.  That's why I keep suggesting PHP and Perl.
0
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
Use "\r\n" after $row['sid'] instead of ",".
fwrite($fp, $row['sid']."\r\n");

Open in new window

0
projectsAuthor Commented:
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
Dave BaldwinFixer of ProblemsCommented:
Then, since it is Linux, just use '\n'.
0
projectsAuthor Commented:
Perfect, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.