mysqlbashmariadbdialog

Dynamic dialog radiolist from MySQL or MariaDB query?


This works to create a name-picker radiolist dialog box:

dialog --stdout --no-tags --clear --backtitle "Name Picker" --title " Name Selection " --radiolist " Select the name " 12 95 0 "Adam Zero" "Adam Zero" off "John Smith" "John Smith" off > file

What I need, however, is for the names to come out of a Mariadb or MySQL database. So:

create table names (fullname varchar(128));
insert into names values ('John Smith');
insert into names values ('Adam Zero');

IFS=$'\n'; unset NAMELIST
results=( $(/usr/bin/mariadb -N -B -u myuser -h 10.0.0.25 -pmypass -P 3306 -D mydb --skip-ssl -e "select distinct fullname from names order by fullname" ) )
for i in ${results[*]}; do
  NAMELIST=$(echo $NAMELIST "\"$i\" \"$i\" off")
done  
echo "$NAMELIST"

...which returns this:

"Adam Zero" "Adam Zero" off "John Smith" "John Smith" off

...which appears to be exactly the same string as appeared at the end of the earlier, working 'dialog' command, so I just substitute it in:

dialog --stdout --no-tags --clear --backtitle "Name Picker" --title " Name Selection " --radiolist " Select the name " 12 95 0 $NAMELIST > file

...and get this result:

Expected at least 20 tokens for --radi, have 5.
Use --help to list options.

The same error returns whether I use $NAMELIST or "$NAMELIST" (i.e., with or without quotation marks)

First question: Why can't I just substitute into the dialog command a variable whose textual contents are exactly the same as text that, specified as static text, works?

More important question: How can I re-write this bash code to allow the results of a single-column query from MariaDB or MySQL to be successfully the source of a dialog radiolist dialog?

I've seen questions here about reading in data from a file, but not from a database... and I wasn't able to adapt my code accordingly to get a usable result. Any insights appreciated.


Solution

  • Try this:

    #!/bin/bash
    
    # Connection to DB
    DB_USER="root"
    DB_PASS="root"
    DB_HOST="localhost"
    DB_PORT="3306"
    DB_NAME="mydb"
    
    # Read names from the DB
    IFS=$'\n' read -r -d '' -a results < <(mariadb -N -B -u $DB_USER -h $DB_HOST -p$DB_PASS -P $DB_PORT -D $DB_NAME --skip-ssl -e "SELECT DISTINCT fullname FROM names ORDER BY fullname" && printf '\0')
    
    # Prepare the options array
    options=()
    for name in "${results[@]}"; do
        options+=("$name" "$name" "off")
    done
    
    # Check if the options array is empty
    if [ ${#options[@]} -eq 0 ]; then
      echo "No names found in the database!"
      exit 1
    fi
    
    # Use dialog with the correctly formatted options
    dialog --stdout --no-tags --clear --backtitle "Name Picker" --title "Name Selection" --radiolist "Select the name" 12 95 0 "${options[@]}" > file
    

    dialog expects each element of the array to be passed as a separate argument, and as a result it fails to create the list.