Relational DB - Number guessing game

nbhawnani · November 17, 2022, 10:20pm

2 of the steps are not getting passed. I am not able to figure out what’s missing. Pls help.:

If that username has been used before, it should print Welcome back, <username>! You have played <games_played> games, and your best game took <best_game> guesses. , with <username> being a users name from the database, <games_played> being the total number of games that user has played, and <best_game> being the fewest number of guesses it took that user to win the game
When the secret number is guessed, your script should print You guessed it in <number_of_guesses> tries. The secret number was <secret_number>. Nice job!

#!/bin/bash
PSQL="psql -X --username=freecodecamp --dbname=number_guess --tuples-only -c"

echo 'Enter your username:'
read USERNAME

USERNAMEFOUND=$($PSQL "select username from usernames where username='$USERNAME';")

if [[ -z $USERNAMEFOUND ]]
then
# not found
  echo "Welcome, $USERNAME! It looks like this is your first time here."
  NEWUSER=$($PSQL "insert into usernames(username) values('$USERNAME');")
else
  #user found
  #echo $USERNAMEFOUND
  GAMEFOUND=$($PSQL "select username,count(game_id),MIN(guess_count) from games  group by username having  username='$USERNAME';")
  echo "GAMEFOUND:$GAMEFOUND"
  echo "$GAMEFOUND" | while read USER BAR COUNT BAR MIN
    do
      #print welcome message
      echo "Welcome back, $USER! You have played $COUNT games, and your best game took $MIN guesses." 
    
    done
  
  #generate secret number
  SECRETNUMBER=$(($RANDOM % 1000))
  echo $USERNAMEFOUND : $SECRETNUMBER
  TRIES=0
  while [[ $GUESS -ne $SECRETNUMBER ]]
  do
    echo 'Guess the secret number between 1 and 1000:'
    read GUESS
    TRIES=$((TRIES+1))
    if [[ ! $GUESS =~ ^[0-9]+$ ]]
    then
      #not a number
      echo 'That is not an integer, guess again:'
    else
      #check if secret number is higher than the guess
      if [[ $GUESS -le $SECRETNUMBER ]]
      then
        echo "It's higher than that, guess again:"
      fi
      #check if secret number is lower than the guess
      if [[ $GUESS -ge $SECRETNUMBER ]]
      then
        echo "It's lower than that, guess again:"
      fi
    fi
  done
  #secret number is guessed
  echo You guessed it in $TRIES tries. The secret number was $SECRETNUMBER. Nice job!
  #insert into table
  NEWGAME=$($PSQL "insert into games(username,guess_count) values('$USERNAME',$TRIES);")
  
fi

sanity · November 18, 2022, 5:37am

Could you share dump of your db as well?

nbhawnani · November 18, 2022, 3:42pm

Here’s what in the number_guess.sql file:

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE number_guess;
--
-- Name: number_guess; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE number_guess WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE number_guess OWNER TO freecodecamp;

\connect number_guess

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: games; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.games (
    game_id integer NOT NULL,
    username character varying(30) NOT NULL,
    guess_count integer
);


ALTER TABLE public.games OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.games_game_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.games_game_id_seq OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.games_game_id_seq OWNED BY public.games.game_id;


--
-- Name: usernames; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.usernames (
    username character varying(30) NOT NULL
);


ALTER TABLE public.usernames OWNER TO freecodecamp;

--
-- Name: games game_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games ALTER COLUMN game_id SET DEFAULT nextval('public.games_game_id_seq'::regclass);


--
-- Data for Name: games; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.games VALUES (1, 'test1', 3);
INSERT INTO public.games VALUES (2, 'test2', 6);
INSERT INTO public.games VALUES (3, 'test2', 2);
INSERT INTO public.games VALUES (4, 'test2', 9);
INSERT INTO public.games VALUES (5, 'test2', 1);
INSERT INTO public.games VALUES (6, 'test2', 1);
INSERT INTO public.games VALUES (7, 'test2', 1);
INSERT INTO public.games VALUES (8, 'user_1668706381551', 209);
INSERT INTO public.games VALUES (9, 'user_1668706381550', 15);
INSERT INTO public.games VALUES (10, 'user_1668706381551', 926);
INSERT INTO public.games VALUES (11, 'user_1668706381551', 687);
INSERT INTO public.games VALUES (12, 'user_1668706381551', 59);
INSERT INTO public.games VALUES (13, 'test2', 1);
INSERT INTO public.games VALUES (14, 'test2', 1);
INSERT INTO public.games VALUES (15, 'test1', 1);
INSERT INTO public.games VALUES (16, 'test1', 1);
INSERT INTO public.games VALUES (17, 'test1', 1);
INSERT INTO public.games VALUES (18, 'test1', 1);
INSERT INTO public.games VALUES (19, 'test3', 1);
INSERT INTO public.games VALUES (20, 'test3', 1);
INSERT INTO public.games VALUES (21, 'test1', 1);
INSERT INTO public.games VALUES (22, 'test3', 1);
INSERT INTO public.games VALUES (23, 'test1', 1);
INSERT INTO public.games VALUES (24, 'user_1668723069112', 761);
INSERT INTO public.games VALUES (25, 'user_1668723069111', 109);
INSERT INTO public.games VALUES (26, 'user_1668723069112', 541);
INSERT INTO public.games VALUES (27, 'user_1668723069112', 526);
INSERT INTO public.games VALUES (28, 'user_1668723069112', 375);
INSERT INTO public.games VALUES (29, 'test1', 8);
INSERT INTO public.games VALUES (30, 'user_1668723396009', 335);
INSERT INTO public.games VALUES (31, 'user_1668723396008', 502);
INSERT INTO public.games VALUES (32, 'user_1668723396009', 375);
INSERT INTO public.games VALUES (33, 'user_1668723396009', 875);
INSERT INTO public.games VALUES (34, 'user_1668723396009', 676);
INSERT INTO public.games VALUES (35, 'test1', 1);


--
-- Data for Name: usernames; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.usernames VALUES ('test1');
INSERT INTO public.usernames VALUES ('test2');
INSERT INTO public.usernames VALUES ('user_1668703356169');
INSERT INTO public.usernames VALUES ('user_1668703356168');
INSERT INTO public.usernames VALUES ('user_1668703710512');
INSERT INTO public.usernames VALUES ('user_1668703710511');
INSERT INTO public.usernames VALUES ('user_1668704728512');
INSERT INTO public.usernames VALUES ('user_1668704728511');
INSERT INTO public.usernames VALUES ('user_1668706381551');
INSERT INTO public.usernames VALUES ('user_1668706381550');
INSERT INTO public.usernames VALUES ('test3');
INSERT INTO public.usernames VALUES ('user_1668723069112');
INSERT INTO public.usernames VALUES ('user_1668723069111');
INSERT INTO public.usernames VALUES ('user_1668723396009');
INSERT INTO public.usernames VALUES ('user_1668723396008');


--
-- Name: games_game_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.games_game_id_seq', 35, true);


--
-- Name: games games_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_pkey PRIMARY KEY (game_id);


--
-- Name: usernames usernames_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.usernames
    ADD CONSTRAINT usernames_pkey PRIMARY KEY (username);


--
-- Name: games games_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_username_fkey FOREIGN KEY (username) REFERENCES public.usernames(username);


--
-- PostgreSQL database dump complete
--

sanity · November 18, 2022, 5:53pm

Have you tried to start a game with new user? The game appears to exit after entering username.

The other issue is number of guesses and secret number that’s detected by test. It’s doing that in a bit peculiar way, but generally you’d want to remove printing all lines that are not needed - debug information, etc.
For example the Guess the secret number between 1 and 1000: is printed after each incorrect guess, that’s not needed. I’ve also couple times guessed the correct number, but script printed also

It's higher than that, guess again:
It's lower than that, guess again:

Before showing that number was guessed.

nbhawnani · November 18, 2022, 8:50pm

All these inputs helped very much! Fixed them and the tests cleared. thank you so much!!

Topic		Replies	Views
Build a number guessing game Backend Development	2	1078	December 10, 2022
Build a Number Guessing Game Backend Development	3	106	June 5, 2024
Number Guessing Game - Build a Number Guessing Game Backend Development	10	962	March 18, 2024
Build a number guessing game - Relational Database Backend Development	10	679	December 27, 2022
Number Guessing Game // SUBTASKS 1.1 :8 Backend Development	2	106	June 11, 2024

Relational DB - Number guessing game

Related topics