Salon Appointment Scheduler

Please help. Ive really been struggling with these last 2 incomplete tasks for the last 2 days:

You can create another row in the appointments table by running your script and entering 2 , 555-555-5555 , 11am at each request for input if that phone number is(and is not) already in the customers table. The row should have the customer_id for that customer, and the service_id for the service entered

This is the error message I receive when running the script:

ERROR:  invalid input syntax for type integer: ""
LINE 1: ...ntments(time, customer_id, service_id) VALUES('10:30', '', '1')
                                                                                                                              ^

Heres my code:

#!/bin/bash
PSQL="psql --username=freecodecamp --dbname=salon --tuples-only -c"
echo -e "\n~~~~~ MY SALON ~~~~~\n"

SERVICE_MENU() {
  if [[ $1 ]]
  then
  echo -e "\n$1\n"
  fi

  echo Welcome to My Salon, how may I help you?
  echo -e "\n1) Mullet
2) Jheri curl
3) The Rachel"
read SERVICE_ID_SELECTED

case $SERVICE_ID_SELECTED in
1) MULLET_MENU ;;
2) JHERI_CURL_MENU ;;
3) THE_RACHEL_MENU ;;
*) SERVICE_MENU "I could not find that service. What would you like today?" ;;
esac

CUSTOMER_PHONE=$($PSQL "SELECT phone FROM customers WHERE name = '$CUSTOMER_NAME'")
SERVICE_ID_SELECTED=$($PSQL "SELECT name FROM services")
CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers")

}

MULLET_MENU() {
  # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your mullet, $CUSTOMER_NAME?"
  read SERVICE_TIME
  echo -e "\nI have put you down for a mullet at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
 INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', '$CUSTOMER_ID', '$SERVICE_ID_SELECTED')")

}

JHERI_CURL_MENU() {
 # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your jheri curl, $CUSTOMER_NAME?"
  read SERVICE_TIME
 
  echo -e "\nI have put you down for a jheri curl at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', '$CUSTOMER_ID', '$SERVICE_ID_SELECTED')")

}

THE_RACHEL_MENU() {
 # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your Rachel, $CUSTOMER_NAME?"
  read SERVICE_TIME
 
  echo -e "\nI have put you down for a Rachel at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', '$CUSTOMER_ID', '$SERVICE_ID_SELECTED')")

}

SERVICE_MENU

                                                               ^

I’ve edited your code for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.

You can also use the “preformatted text” tool in the editor (</>) to add backticks around text.

See this post to find the backtick on your keyboard.
Note: Backticks (`) are not single quotes (').

1 Like

Got it. Was wondering why my code looked so different from others on here. Thank you!!!

You don’t mention much about your thought process and as I am unfamiliar with the exercise here, the first thought I have is:

Why are you trying to insert a customer_id that is blank into the table?

I think you should do some debugging to identify where the blank is coming from…

The error message tells you the exact problem you are having, you should try to understand that message. It even shows the line where the error occurs.

“invalid input syntax for type integer”

Dig in to this a bit more.

I understand what the error message is saying. I dont understand why it is saying that. As far as I can tell, I’ve instructed to insert a row into the “appointments” table that includes a customer_id obtained from the “customers” table. But the code and the error message is returning a NULL value.

Again, as I am not familiar with the exercise, I am going to just guess something…
The last line here is just doing a select on all customer ids?
Not a specific one?

Im not trying to insert a customer_id that is blank. Im trying to insert a customer_id - that is currently located in the “customers” table - into the appointments table. In the project, a customer_id is generated and inserted into the customers table when a new customer provides their info. After the customer provides their info, they then schedule an appointment. I need to insert a row into the appointments table that includes appointment info, including the customer_id located in the customers table.

This line of code is producing the error:

 INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', '$CUSTOMER_ID', '$SERVICE_ID_SELECTED')")

This is the customer_id variable referenced in the above line of code:

 CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers")

I think you are missing a where clause…

I think so too, but I cant figure out what it is. I’m getting the same error code using these WHERE statements:

WHERE name = $CUSTOMER_NAME

AND

WHERE phone = $CUSTOMER_PHONE

The salon database has 3 tables: customers, appointments and services.

The customer table has 3 columns: name, phone and customer_id.
The appointments table has 4 columns: appointment_id, time, customer_id, service_id
The services table has 2 columns: service_id and name.

The error is telling you that the syntax for INT is incorrect, and it specifically highlights the quotation marks.

For example:
VARCHAR needs single quotes around the value
INT, NUMERIC, DECIMAL, DEC do not need single quotes around the values

Do you understand?

Yes. And Ive tried running the following code without the single quotes but its producing pretty much the exact same error message:

ERROR: syntax error at or near “,”
LINE 1: …pointments(time, customer_id, service_id) VALUES(‘3’, , ‘1’)

 INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', $CUSTOMER_ID, '$SERVICE_ID_SELECTED')")

I think the problem may have something to do with this customer_id variable referenced in the above line of code:

 CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone = '$CUSTOMER_PHONE'")

i think it would be helpful to share your code in full as well as the schema of the tables you are using.
(also for someone who hasn’t tried this exercise, a link to the exercise would be good so I don’t have to spend extra time searching for it in FCC)

Do you think I should add that here? Or would it make sense to start fresh and clean in a new post?

if you create a duplicate post the mods will just move it here. So please just add this here.
(the dump of your salon.sql would be helpful probably as well, maybe also try running select * from the table you are using to see if it has the records you think it has)

All tasks are completed in the Build a Salon Appointment Scheduler Project, except for:

You can create a row in the appointments table by running your script and entering 1 , 555-555-5555 , Fabio , 10:30 at each request for input if that phone number is (or is not) in the customers table. The row should have the customer_id for that customer, and the service_id for the service entered

Project can be found here: https://www.freecodecamp.org/learn/relational-database/build-a-salon-appointment-scheduler-project/build-a-salon-appointment-scheduler

Here is the info from the customers table:

customer_id | phone | name
-------------±-----------±--------
1 | Q |
3 | 333-3333 | Tyrone
24 | 999-9999 | mcfly
30 | 7 | D

Info from the appointments table:

appointment_id | time | customer_id | service_id
----------------±-----±------------±-----------
1 | 11 | 1 | 1
(1 row)

Error message:

ERROR: syntax error at or near “,”
LINE 1: …pointments(time, customer_id, service_id) VALUES(‘3’, , ‘1’)

2 lines of code in question:

 INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', $CUSTOMER_ID, '$SERVICE_ID_SELECTED')")

AND

 CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone = '$CUSTOMER_PHONE'")

And here is my complete salon.sql script:

#!/bin/bash
PSQL="psql --username=freecodecamp --dbname=salon --tuples-only -c"
echo -e "\n~~~~~ MY SALON ~~~~~\n"

SERVICE_MENU() {
  if [[ $1 ]]
  then
  echo -e "\n$1\n"
  fi

  echo Welcome to My Salon, how may I help you?
  echo -e "\n1) Mullet
2) Jheri curl
3) The Rachel"
read SERVICE_ID_SELECTED

case $SERVICE_ID_SELECTED in
1) MULLET_MENU ;;
2) JHERI_CURL_MENU ;;
3) THE_RACHEL_MENU ;;
*) SERVICE_MENU "I could not find that service. What would you like today?" ;;
esac

CUSTOMER_PHONE=$($PSQL "SELECT phone FROM customers WHERE name = '$CUSTOMER_NAME'")
SERVICE_ID_SELECTED=$($PSQL "SELECT name FROM services")
CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone = '$CUSTOMER_PHONE'")

}

MULLET_MENU() {
  # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your mullet, $CUSTOMER_NAME?"
  read SERVICE_TIME
  echo -e "\nI have put you down for a mullet at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
 INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', $CUSTOMER_ID, '$SERVICE_ID_SELECTED')")

}

JHERI_CURL_MENU() {
 # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your jheri curl, $CUSTOMER_NAME?"
  read SERVICE_TIME
 
  echo -e "\nI have put you down for a jheri curl at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', $CUSTOMER_ID, '$SERVICE_ID_SELECTED')")

}

THE_RACHEL_MENU() {
 # GET CUSTOMER INFO
 echo -e "\nWhats your phone number?"
 read CUSTOMER_PHONE

 CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")

# IF NO CUSTOMER INFO

 if [[ -z $CUSTOMER_NAME ]]
then
  # THEN GET NAME
  echo -e "\nWhats your name?" 
  read CUSTOMER_NAME

  # INSERT NEW CUSTOMER INFO
  INSERT_CUSTOMER=$($PSQL "INSERT INTO customers(name, phone) VALUES('$CUSTOMER_NAME', '$CUSTOMER_PHONE')") 
  fi

  # GET SERVICE TIME
  echo -e "\nWhat time would you like to schedule your Rachel, $CUSTOMER_NAME?"
  read SERVICE_TIME
 
  echo -e "\nI have put you down for a Rachel at $SERVICE_TIME, $CUSTOMER_NAME."

  #INSERT APPOINTMENT
INSERT_APPOINTMENT=$($PSQL "INSERT INTO appointments(time, customer_id, service_id) VALUES('$SERVICE_TIME', $CUSTOMER_ID, '$SERVICE_ID_SELECTED')")

}

SERVICE_MENU

Have you figured out the error yet?

Looks like the customer_id isn’t being retrieved properly.

No I have not figured it out yet

quick question, are you able to echo this value?
(CUSTOMER_ID right after this line executes?)
Just want to confirm it gives you back a number

No. It doesnt give back anything. Should it provide something back without a phone number to reference?