Relational database - Number counting game

Backend Development
1

Hello,

I’ve been banging my head against this error all day, so maybe some fresh eyes can help!

PSQL="psql --username=freecodecamp --dbname=number_guess -t --no-align -c"
...
if [[ -z $BEST_SCORE ]]
then
#This part works
  INSERT_INTO_RESULT=$($PSQL "INSERT INTO games(user_id, best_score, total_games) VALUES($USER_ID, $NEW_SCORE, 1)")
else
#This part always fails
  UPDATE_INFO_RESULT=$($PSQL "UPDATE games SET (best_score, total_games) = ($NEW_SCORE, total_games + 1) WHERE user_id = $USER_ID")
fi

The error I get is

./number_guess.sh: line 65: psql --username=freecodecamp --dbname=number_guess -t --no-align -c: command not found

Which indicates that there’s something wrong with my SQL, but when I plug it in directly via the psql interface or from the command line, it updates my DB just fine.

I’ve used debugging statements to make sure that all of the variables ($NEW_SCORE, $USER_ID) are properly making it into the conditional. I even tried deleting the existing row, and inserting a new one and both of those failed with the same error message.

Thank you for taking the time to look!

edit: Other DB queries that work, in case it helps:

USERINFO=$($PSQL "SELECT users.user_id, best_score, total_games FROM users INNER JOIN games ON users.user_id = games.user_id WHERE name = '$USERNAME'")
...
INSERT_USER_RESULT=$($PSQL "INSERT INTO users(name) VALUES ('$USERNAME')")
USER_ID=$($PSQL "SELECT user_id FROM users WHERE name = '$USERNAME'")
2

I’m not seeing anything sticking out here. Could you show your full script?

1 Like
3

I’m not sure, but it could be the math you are trying to do in there → total_games + 1. I didn’t test it, but that’s the only thing I see that differs between the commands.

1 Like
4

Here is the whole script, thank you for taking the time to look at it!

#!/bin/bash


PSQL="psql --username=freecodecamp --dbname=number_guess -t --no-align -c"
#NUMBER_TO_BE_GUESSED=$(( 1 + $RANDOM % 1000 ))
NUMBER_TO_BE_GUESSED=300
GUESS_COUNT=0

GET_GUESS() {
  echo $1
  read GUESS

  while ! [[ "$GUESS" =~ ^[0-9]+$ ]]
  do
    echo "That is not an integer, guess again:"
    read GUESS
  done

  ((GUESS_COUNT++))

  while [[ $GUESS -ne $NUMBER_TO_BE_GUESSED ]]
  do
    if [[ $GUESS -gt $NUMBER_TO_BE_GUESSED ]]
    then
      GET_GUESS "It's lower than that, guess again:"
    else
      GET_GUESS "It's higher than that, guess again:"
    fi
  done
}

echo "Enter your username:"
read USERNAME

USERINFO=$($PSQL "SELECT users.user_id, best_score, total_games FROM users INNER JOIN games ON users.user_id = games.user_id WHERE name = '$USERNAME'")

if [[ -z $USERINFO ]]
then
  echo "Welcome, $USERNAME! It looks like this is your first time here."
  INSERT_USER_RESULT=$($PSQL "INSERT INTO users(name) VALUES ('$USERNAME')")
  USER_ID=$($PSQL "SELECT user_id FROM users WHERE name = '$USERNAME'")

else
  IFS='|'; read USER_ID BEST_SCORE TOTAL_GAMES <<< "$USERINFO"
  echo "Welcome back, $USERNAME! You have played $TOTAL_GAMES games, and your best game took $BEST_SCORE guesses."
fi

GET_GUESS "Guess the secret number between 1 and 1000:"

echo "You guessed it in $GUESS_COUNT tries. The secret number was $NUMBER_TO_BE_GUESSED. Nice job!"

#tests and new code
if [[ -z $BEST_SCORE ]] || [[ $BEST_SCORE -gt $GUESS_COUNT ]]
then
  NEW_SCORE=$GUESS_COUNT
else
  NEW_SCORE=$BEST_SCORE
fi

if [[ -z $BEST_SCORE ]]
then
#This part works
  INSERT_INTO_RESULT=$($PSQL "INSERT INTO games(user_id, best_score, total_games) VALUES($USER_ID, $NEW_SCORE, 1)")
else
#This part always fails
  UPDATE_INFO_RESULT=$($PSQL "UPDATE games SET (best_score, total_games) = ($NEW_SCORE, total_games + 1) WHERE user_id = $USER_ID")
fi
5

I originally incremented the variable $TOTAL_GAMES and had it in the SQL statement, but while I was trying to figure out the problem switched it to the current code.

Tried switching it back, but nada.

Thank you for looking though!

6

Well, I don’t know @colebtucker - this is completely strange. It’s saying ‘command not found’ as if the psql command isn’t there. But it works everywhere else. I will keep digging and let you know if I come up with something.

1 Like
7

I’m baffled as well. I’ve copied the whole psql (...) command into the UPDATE_INFO_RESULT, to not rely on the $PSQL variable and it works that way. This suggests there’s something wrong with the $PSQL, but it can be echoed without issues.

As a try I’ve also swapped the order of the last if and the check to if [[ -n $BEST_SCORE ]]. Problem was still occurring only in the updating part.

1 Like
8

I played around with it quite a bit earlier and didn’t get anywhere. Changed the order of things, the query, the syntax, changed conditions - that one command failed any way I used it. Maybe just go with @sanity’s solution if it works @colebtucker.

9

@sanity’s attempt didn’t resolve it either.

I tried rewriting everything using SQL’s MIN() and COUNT() functions and just INSERTINGing every game into the DB, since all the INSERTs are working fine in this code, and then it started happening on the final INSERT!

I’ll take a break and come back around in a few days. Thank you both for all your efforts, have a lovely week!

10

Whew, I got it.

This line changes IFS for the rest of script as well, what seems to be affecting some commands with psql later.
IFS='|'; read USER_ID BEST_SCORE TOTAL_GAMES <<< "$USERINFO"
Removing the semicolon makes it work.

:ok_man:t2:

2 Likes