how to use a specific column for a day
You really need to fix your design. Having 31 columns, one for every day and 6 columns, one for every week is not normalized and is going to cause a lot of pain on the road.

That aside, to solve the specific issue, you need dynamic SQL because the @daily = 'day28' is a string and you want it used as a column name:

-- the sql as a string
SET @sql = concat('select ', @daily, ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;


or:

-- the sql as a string
SET @sql = concat('select day', 
                   date_format(curdate(), '%d'), 
                  ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;