LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - General (http://www.linuxquestions.org/questions/linux-general-1/)
-   -   mysql in bash script (http://www.linuxquestions.org/questions/linux-general-1/mysql-in-bash-script-916360/)

slibbe 11-30-2011 08:19 AM

mysql in bash script
 
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 :)


All times are GMT -5. The time now is 04:36 PM.