get variable from bash from mysql or pass from php

qwertyjjj · 04-26-2010, 08:16 AM

I am not parsing on a webserver so is it possible to have both
#! /usr/bin/php &
#!/bin/bash

in the same script?

Alternatively, I have a current bash script that I need to get some variables from mysql and not sure how to get mysql results in bash:

Quote:

mysql -h server.net -u username1 -paaa -e "USE squid; SELECT email, usern FROM TABLE WHERE blah blah;"
emailadd="resultfrom above"
usern="resultfromabove"

smoker · 04-26-2010, 10:48 AM

This works for 2 values. the -N in the mysql command prevents the column headings, and we are only asking for values from one record.

Code:

data=$(mysql -N -h localhost -u username -ppassword -e "USE DBNAME;select field1,field2 from table where field3 = something")

set -- $data

field1=$1
shift
field2=$@

echo "$field1"
echo "$field2"

Of course you wouldn't echo the values but use them elsewhere.

If you want to get more values, then use shift and $1 for each value

ie.

Code:

set -- $data

field1=$1
shift
field2=$1
shift
field3=$1
shift
field4=$1

echo "$field1"
echo "$field2"
echo "$field3"
echo "$field4"

qwertyjjj · 04-26-2010, 11:06 AM

Quote:

Originally Posted by smoker

This works for 2 values. the -N in the mysql command prevents the column headings, and we are only asking for values from one record.

Code:

data=$(mysql -N -h localhost -u username -ppassword -e "USE DBNAME;select field1,field2 from table where field3 = something")

set -- $data

field1=$1
shift
field2=$@

echo "$field1"
echo "$field2"

Of course you wouldn't echo the values but use them elsewhere.

If you want to get more values, then use shift and $1 for each value

ie.

Code:

set -- $data

field1=$1
shift
field2=$1
shift
field3=$1
shift
field4=$1

echo "$field1"
echo "$field2"
echo "$field3"
echo "$field4"

Should that be field2=$2 ?
I have a different keyboard so not sure where your at sign is.

smoker · 04-26-2010, 11:13 AM

Quote:

Originally Posted by qwertyjjj

Should that be field2=$2 ?
I have a different keyboard so not sure where your at sign is.

What difference does my/your keyboard make ?
If I typed an @ that's what appears.

in the line concerned $@ returns whatever is left in the variable we are parsing, $1 returns the next input only.

qwertyjjj · 04-26-2010, 11:33 AM

Quote:

Originally Posted by smoker

What difference does my/your keyboard make ?
If I typed an @ that's what appears.

in the line concerned $@ returns whatever is left in the variable we are parsing, $1 returns the next input only.

Well, I thought $@ might be a typo because $2 made logical sense also and your @ sig might have been above the number 2

I used this and it seemed to work
field1=$1
shift
field2=$1
shift
field3=$1

smoker · 04-26-2010, 01:09 PM

Here is what the @ is for.
imagine a database with 3 fields per record.

Code:

|	firstname	|	lastname	|	hobbies		|
-------------------------------------------------------------------------
|	fred		|	basset		|	i eat bones	|

If you use this code

Code:

set -- $data

field1=$1
shift
field2=$1
shift
field3=$1

echo "$field1"
echo "$field2"
echo "$field3"

the output would be :
fred
basset
i

But if you use this code

Code:

set -- $data

field1=$1
shift
field2=$1
shift
field3=$@

echo "$field1"
echo "$field2"
echo "$field3"

The output would be :
fred
basset
i eat bones

The thing to remember is that the order of the fields in the variable is the same as the order of the fields in the select statement. So if you have a field that has spaces in the contents, always select it last, because if you select it first then use $@ in the first assignment, this happens

Code:

set -- $data

field3=$@
shift
field2=$1
shift
field1=$1

echo "$field1"
echo "$field2"
echo "$field3"

output would be :
eat
bones
i eat bones fred basset

which is not expected or required.

qwertyjjj · 04-28-2010, 11:21 AM

If the data set is empty, how can I exit the script after the first sql check.
ie what's the check for the dataset?

smoker · 04-28-2010, 12:07 PM

Using the fred basset example above :

Code:

data=$(mysql -N blah blah blah)

if [ "$data" ]

then
    set -- $data
    firstname=$1
    shift   
    blah
    blah 
    blah

else

    exit 0

fi

qwertyjjj · 04-28-2010, 12:22 PM

Quote:

Originally Posted by smoker

Using the fred basset example above :

Code:

data=$(mysql -N blah blah blah)

if [ "$data" ]

then
    set -- $data
    firstname=$1
    shift   
    blah
    blah 
    blah

else

    exit 1

fi

Is there another in bash to do it such as a NOT check?
I have a lot of code in between so could I do:

if ![ "$data" ] then exit 1
else {all code below}

What is fi above?

smoker · 04-28-2010, 12:31 PM

Surely it makes no difference. Both sets of code have to go in somewhere. Presumably you were hoping to get some results otherwise it's all a bit pointless.

fi is the end of the if section.

done is the end of a do section

esac is the end of a case section

http://tldp.org/LDP/abs/html/

BTW I changed the exit code in the example, because you are not using a return value from the script.

It now reads
exit 0

You can change it to your method by using

Code:

if [ -n "$data" ]
then
    exit 0
else
    the rest 
    of the
    code
fi

If you really wanted you could put the main code in a subroutine so you would end up with

Code:

function process {
    the rest 
    of the
    code
    return
}

data=$(mysql -N blah blah blah)

if [ -n "$data" ]
then
    exit 0
else
    process
fi

qwertyjjj · 04-28-2010, 12:58 PM

Just tried this but it seemed to run anyway when it should have exited:

Quote:

#!/bin/bash
data=$(mysql -N -h server.net -u squid -paaa -e "USE DB;select email_address, user,customers_id FROM TABLE WHERE enabled=1 AND
UpdateOnNextCycle=1 LIMIT 0,1;")

if [ -n "$data" ]
then
exit 0
else

set -- $data
sqlemailadd=$1
shift
sqluserstring=$1
shift
sqlcustid=$1
emailadd=$sqlemailadd
usern=$sqluserstring
custid=$sqlcustid
#echo $emailadd
#echo $usern

cd /etc/openvpn/
cd /etc/openvpn/easy-rsa/2.0/
source ./vars

etc.

etc.

fi

smoker · 04-28-2010, 03:30 PM

sorry, should be -z not -n

read the link I gave you.