[SOLVED] mysql in bash script

slibbe · 11-30-2011, 08:19 AM

Hi,

when I want to query some mysql database from bash, I can use the following construction as long as the query is simple:
QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select * from table where userid=10')"

How do I manage to do something similar when the query itself allready has quotes? Like:
select * from table where created='2011-10-10 09:00:05' .
Now QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select * from table where created=`2011-10-10 09:00:05` and userid=10')" doesn't work.

Regards,

slibbe

corp769 · 11-30-2011, 08:58 AM

Instead of having 2011-10-10 09:00:05 in quotes, you should store it in a variable instead. Also, you used backticks in your example, instead of single quotes; Anything within backticks will be executed.

slibbe · 12-01-2011, 07:29 AM

Code:

SEL="select username, articleid"
TBL="table"
WHC1="userid=10"
WHC2="created='2011-10-10 09:00:05'"
Q1="$SEL from $TBL"
Q2="where $WHC1 and $WHC2"
QRY="$Q1 $Q2"
echo $QRY

does echo
select username, articleid from table where userid=10 and created='2011-10-10 09:00:05'

However, a line like

Code:

mysql -u$USER -p$PASS $DB -e "$QRY"

or

Code:

mysql -u$USER -p$PASS $DB -e $QRY

doesn't work whereas the double quotes are substituted by single quotes in the first line and omitted in the second.

slibbe · 12-02-2011, 08:37 AM

I got it!

Instead of

Code:

QRY="$(mysql -u$USER -p$PASS $DB -Bse 'select col1,col2,col3,datetime from table where col1=5 and datetime like '2010-04-01%' ')"

I can use

Code:

QRY=$(mysql -u$USER -p$PASS $db -e "select col1,col2,col3,datetime from table where col1=5 and datetime like '2010-04-01%'")

Don't know why the double quotes in the first line would be needed anyway. However if they are, you can do
'\''2010-04-01%'\'' instead of '2010-04-01%' (all single quotes).

Couldn't stand it not to find an answer for such a common problem