MYSQL & Shell Scripting: Reference individual row attributes from table results?

Question

I am writing a shell script that outputs selected MySql table data as an exercise. Searches using my key words turn up only how to print out an entire MySql table, doing this in a different language and irrelevant questions.

My questions:

1) If possible, how do I reference individual attributes of a certain row from the results (like iterating when through rows with PHP results)?

2) Which of the two ways below, if either, makes the most sense for this purpose? Neither works quite the way I would like at the moment.

MYSQL="mysql -u$db_user -p$db_passwd $db_name -Nbe"

A)

RESULT=`$MYSQL "select * from $table_name where can_auto_check='yes';"`
for UNIQUEID in $RESULT
do
    echo "selected: $client $desc "
    someFunction $client $anothervariable
done

B)

$MYSQL "select * from $table_name where can_auto_check='yes';" \
| while read client anothervariable;
do
    echo "selected: $client $desc "
    someFunction $client $anothervariable
done

It is possible I am wording my searches incorrectly; if that is the case, I would appreciate if someone would nudge me in the right direction.

Thank you.

EDIT: possible lead.. something to do with awk '{print $1" "$2 }' maybe. but for every line.

Final format = " mkdir -p /net/$SERVER/$CLIENT/$DESC/someFiles "

Please just move your comment into your original question as option C. Too hard to read here. It would also help if you showed 1-2 lines of sample output from the query AND how your formatting requirement for your final output. Good luck. — shellter
– shellter, Commented Apr 12, 2012 at 18:03

shellter · Accepted Answer · 2012-04-18 05:04:22Z

1

Not bad, but less code means fewer chances for errors. Unfortunately, I can't test my edit, as I don't have mySQL available. Hopefully you'll get the idea. (or others are welcome to test and edit this post).

# unless you need to use ALL_CLIENTS and DESC_FOR_CLIENT later.
#hidden ALL_CLIENTS=

for CLIENT in $($MYSQL "select distinct client from $table_name where can_auto_check='yes'")
do
    #hidden DESC_FOR_CLIENT=
    for DESC in $($MYSQL  "select desc from $table_name where client='$CLIENT'")
    do
         someFunction $CLIENT $DESC
    done
done

If any of your values for CLIENT or DESC_FOR_CLIENT windup having spaces in them, you will need to quote your usage values like "$CLIENT" .

One other note: back-ticks for command substitution have been deprecated since at least 1995 (unless you're using orginal unix bourne shell or csh, maybe ash, and few other more obscure shell). Bash and ksh no need. use $( .. cmd ...) for your cmd-substitution.

Good luck.

answered Apr 18, 2012 at 5:04

shellter

37.6k7 gold badges87 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sixthfore Over a year ago

Thank you for pointing out that I can do that. For this scenario, I will need the query more than once, but this will probably be helpful for other functions. Thanks again! (would one-up, but I can't at the moment..)

Collectives™ on Stack Overflow

MYSQL & Shell Scripting: Reference individual row attributes from table results?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related