This is the step I’m having trouble with:
When you run your insert_data.sh script, it should insert a row into the games table for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct ID’s from the teams table (you cannot hard-code the values)
Here are the columns in the games table:
game_id, year, round, winner_goals, opponent_goals, winner_id, opponent_id
Here are the different lines in the games.csv file:
year, round, winner, opponent, winner_goals, opponent_goals
Here is my code:
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
INSERT_GAMES_INFO=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID $OPPONENT_ID")
Here is the error I’m receiving:
ERROR: syntax error at end of input
LINE 1: …nt_id) VALUES(year, ‘round’, winner_goals, opponent_goals,
I think the problem is that I dont have WINNER_ID or OPPONENT_ID properly defined. Im not really sure how else to define them. They are both foreign keys of team_id in the teams column.
But the teams table only has 2 columns: team_id and names. This table is just a list of all the UNIQUE teams part of the tournament. This table does not know which teams are winners and losers in specific rounds. Some teams can be a winner in one round, then they can be a loser in a later round.
I dont know how to reference a particular winner or opponent listed in the games.csv file to a team_id in the teams table and then assign that winner or opponent the appropriate ID in the games table.
Heres my full code:
#! /bin/bash
if [[ $1 == "test" ]]
then
PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi
# Do not change code above this line. Use the PSQL variable above to query your database.
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
if [[ $WINNER != winner ]]
then
# insert UNIQUE TEAM TO TEAMS TABLE
INSERT_WINNER=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
fi
if [[ $OPPONENT != opponent ]]
then
INSERT_OPPONENT=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
echo $INSERT_TEAMS
fi
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'")
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")
INSERT_GAMES_INFO=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID $OPPONENT_ID")
done
Your browser information:
User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36
Challenge: World Cup Database - Build a World Cup Database
Link to the challenge: