Bash script: Mysql query field to variable

Question

I'm trying to obtain a recordset and read a few fields from it. I'm not able to figure it out how put the fields into variables. The script is:

#!/bin/bash
sqlQuery="$(mysql -h host -u user -ppass -D oberonsaas_v2 -s -N -e 
'select ventas.id_venta,
ventas_entradas.id_ventas_entradas,
ventas.id_evento,
id_tarifa,
DATE_FORMAT(fecha_evento,"%Y%m%d") as fecha,
TIME_FORMAT(pase,"%H%i") as pase

from pases,ventas,ventas_entradas,recintos

where ventas.id_recinto = recintos.id_recinto 
and ventas.id_pase = pases.id_pase
and ventas.id_venta = ventas_entradas.id_venta
and recintos.id_cliente = 32
and ventas.estado="Pagada"
and date(fecha_venta) = date_add(date(CURRENT_TIMESTAMP),INTERVAL -1 day)')"
echo $sqlQuery

I get all the recordset in $sqlQuery, but i want to do a loop and concat the fields.

At this moment the output is the values of the query. I want to access the fields to format that output — Rafael Osuna Dominguez
– Rafael Osuna Dominguez, Commented Feb 5, 2013 at 16:01
It would be useful to get an echo from the $sqlQuery. Depending on how the result appears, you can parse it by using cut, grep, etc. — fedorqui
– fedorqui, Commented Feb 5, 2013 at 16:10

X Tian · Accepted Answer · 2013-02-05 16:16:21Z

1

Well I have invented some output

#!/bin/bash

Result="21,336,purchase,tarif_exspensive,Jose"
(IFS=","
for i in $Result
do
    echo "I Have: " $i
done
)

Ouput is

$ bash t5.sh 
I Have:  21
I Have:  336
I Have:  purchase
I Have:  tarif_exspensive
I Have:  Jose

answered Feb 5, 2013 at 16:16

X Tian

7827 silver badges14 bronze badges

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

1 Comment

X Tian Over a year ago

I set the Inter Field Separator IFS to comma so the for loop runs over the fields. I do it in a sub shell ( ) so it doesn't affect the parent's IFS although you could set it back again to space, after the for loop.

FrankBoller · Accepted Answer · 2013-02-05 16:38:13Z

0

Collapse this into the simplest possible construct. Put returned recordset into a bash array (for example):

sqlQuery=( pases_value ventas_value ventas_entradas_value recintos_value )

note: yours could look like:

sqlQuery=( $(mysql -h host ... )

new lines between the array operators "(" and ")" are ok

each will be in a distinct sqlQuery array location. for example return: recintos_value

echo ${sqlQuery[3]}

concat all:

echo ${sqlQuery[*]}

if delimiters are of concern, use the mysql --delimiter switch to define a desired character, then

set concatValue=${sqlQuery[*]}

then to remove the delimiter character ("," for example):

set finalValue=${concatValue//,}

edited Feb 5, 2013 at 16:38

answered Feb 5, 2013 at 16:21

FrankBoller

1532 gold badges2 silver badges9 bronze badges

Collectives™ on Stack Overflow

Bash script: Mysql query field to variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related