if statement in while loop bash

Question

I am trying to make script that inserts hostname and ip address for certain website in mysql , but before the script inserts results i want to check if they already exist and if it does the script should not insert it.

result_check=$(mysql -uroot -p123qwe webs -e "SELECT COUNT(*) AS NUMBER FROM webs WHERE hostname='$hostname' AND ip='$ip'";)
        cat $dir/webs | while read hostname ip; do
    if [[ $result_check -eq 0 ]]; then
    echo "INSERT INTO webs (hostname,ip) VALUES ('$hostname','$ip');"
    fi;
    done | mysql -uroot -p123qwe webs;

And this somehow does not work , i can think of other way to get the job done(without if) but i want to make it this way. My webs file looks like that : somedomain.com 192.168.3.3 ... and etc.

This is the error i get:

line 23: [[: NUMBER 0: syntax error in expression (error token is "0")

I have tried many ways to escape this error (changing the brackets from [[ to (( ) but i've had no luck.

Answer 1

The error message suggests that the output from the first mysql begins with NUMBER. Maybe there's a way to suppress column headers? Ah yes, the -N option.

Secondly, you appear to require the values that are being read from the file to be present in the query, so you have to rearrange things accordingly.

Additionally, you want to clean up the syntax.

while read hostname ip; do
    result_check=$(mysql -N -uroot -p123qwe webs \
        -e "SELECT COUNT(*) AS NUMBER FROM webs WHERE hostname='$hostname' AND ip='$ip'")
    if [[ $result_check -eq 0 ]]; then
        echo "INSERT INTO webs (hostname,ip) VALUES ('$hostname','$ip');"
    fi
done <"$dir"/webs | mysql -uroot -p123qwe webs

I dislike the temporary variable for the result_check but inlining it would hamper readability, so I guess it'll have to stay.

However, a much better approach would be to use native SQL constructs for INSERT ... WHERE NOT EXISTS or similar. See also How to 'insert if not exists' in MySQL?

Answer 2

Your code is kosher, so $result_check is clearly not a number. Here are some suggestions:

1. Add an echo if [[ $result_check -eq 0 ]] before the if check to see what's being checked.
2. In Bash it's sometimes helpful to protect against strings being empty. In this case, the protection would be: if [[ "x$result_check" = x0 ]], but you can't do any interesting integer stuff with that check, for example testing equality for "0" and "00" would fail. if [[ "0$result_check" -eq 0 ]] should solve that problem, but I haven't tested it. On my system, the [[ function actually handles the empty string with no problem, so [[ $undefined_variable -eq 0 ]] is always true.
3. You can run type [[ to see what the [[ function actually is. For me, it's a shell keyword (bash version 3.2).
4. For fun, try the [ function instead. As long as you use quotes and add a prefix to protect against empty variables, the result should be the same for these simple tests.