need info on access of mysql in shell script...

[Karthik_sg]

hi thr i want to know how do we access a colum in the followin query in shell script.
var1=`mysql <......EOF`
now i want to access the second column(class) first row.
But whn i echo $var1. it gives all the values together
[note:there are many rows]
now how do i access the first row, thn the second,.....
Pls do write a script or give me an example.Its urgent.
thnks in advance

[Padma Asrani]

Hi

If various fields are separated through space then you could use awk. Can you paste the output of var1 then I could write and send it to you.

Padma

[Stuart Browne]

There are a few issues involved here which need to be addressed.

First:

If you are working in a shell, and want to get each 'name' and 'class' into a shell variable to manipulate, then you want to use 'read' and a 'while' loop. This can be done very easily, i.e.:

mysql -N -B -e 'select name, class from student' | while read NAME CLASS; do ....; done

This makes an assumption or two though.

Second:

This assumption is that 'name' or 'class' contains no spaces (not likely it seems). So this means you want to do something more like this:

----- start ------
#!/bin/sh

OFS=$IFS
IFS=|

mysql -N -e 'select name, class from student' | while read NAME CLASS
do
echo ::${NAME}::${CLASS}::
done
IFS=$OFS

-----end ------

This is messing around with the Input Field Separator, so it pays to be a little bit careful.

[Karthik_sg]

Assume name Hexstatus,timestamp,N1 are the fields.now

name Hex(Status) timestamp N1 00000000000000000000000000000002 2007-06-20 10:06:00 0016e694818b
00000000000000000000000000000200 2007-06-20 10:02:18 /B1/G1/r1
00000000000000000000000000000200 2007-06-20 10:02:18
i want the fileds with 0s whch is a hexadecimal value.
i want to access all of them individually

[Stuart Browne]

Then alter this line:

mysql -N -e 'select name, status, class from student' | while read NAME STATUS CLASS

Your 3 values are '$NAME', '$STATUS' and '$CLASS'. All individually accessable within the loop.

The '-N' flag, coupled with the IFS redelegation are important, as these set up the structure for the 'read'.