[SOLVED] Using CASE to Compare Result and Add Dates

[dprichard]

I have a form that lets a user choose whether or not they have to wait days or months and lets them specify the number of days or months they have to wait.  They choose the radio button wait_days_months and then they put in the number of days or months they have to wait in wait_number.  Once they do, I am trying to add that date to today's date and then save it as the result in the field in the database.  I think I am close, but can't get this one down.  Any help would be greatly appreciated!!!

 

INSERT INTO emp_time_table (empto_emp_id, empto_to_type_id, empto_accrual_start_date, empto_accrual_expiration_date, empto_plan_cap, empto_max_carry_over, empto_waiting_period, empto_renewal_term, empto_time_accrued, empto_last_updated, empto_updated_by) VALUES (%s, %s, %s, %s, %s, %s, CASE WHEN %s = 'months' THEN DATE_ADD(%s + INTERVAL %s MONTH) WHEN %s = 'days' THEN DATE_ADD(%s, INTERVAL %s DAY) END, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['empto_emp_id'], "int"),
                       GetSQLValueString($_POST['empto_to_type_id'], "int"),
                       GetSQLValueString($_POST['empto_accrual_start_date'], "date"),
                       GetSQLValueString($_POST['empto_accrual_expiration_date'], "date"),
                       GetSQLValueString($_POST['empto_plan_cap'], "text"),
                       GetSQLValueString($_POST['empto_max_carry_over'], "text"),
                       GetSQLValueString($_POST['wait_days_months'], "text"),
                       GetSQLValueString($_POST['today'], "date"),
				   GetSQLValueString($_POST['wait_number'], "int"),
                       GetSQLValueString($_POST['wait_days_months'], "text"),
                       GetSQLValueString($_POST['today'], "date"),
				   GetSQLValueString($_POST['wait_number'], "int"),
                       GetSQLValueString($_POST['empto_renewal_term'], "int"),
                       GetSQLValueString($_POST['empto_time_accrued'], "double"),
                       GetSQLValueString($_POST['empto_last_updated'], "date"),
                       GetSQLValueString($_POST['empto_updated_by'], "int"));

[bubblegum.anarchy]

I suggest renaming the radio element value attribute to MONTH and DAY and then something like this:

 

adddate(CURRENT_DATE, INTERVAL $_POST['interval'] $_POST['period']);

 

... and perform the appropriate data integrity checks before querying.

[dprichard]

I tried it like this... 

 

INSERT INTO emp_time_table (empto_emp_id, empto_to_type_id, empto_accrual_start_date, empto_accrual_expiration_date, empto_plan_cap, empto_max_carry_over, empto_waiting_period, empto_renewal_term, empto_time_accrued, empto_last_updated, empto_updated_by) VALUES (%s, %s, %s, %s, %s, %s, ADDDATE(CURRENT_DATE, INTERVAL %s %s), %s, %s, %s, %s)",
                       GetSQLValueString($_POST['empto_emp_id'], "int"),
                       GetSQLValueString($_POST['empto_to_type_id'], "int"),
                       GetSQLValueString($_POST['empto_accrual_start_date'], "date"),
                       GetSQLValueString($_POST['empto_accrual_expiration_date'], "date"),
                       GetSQLValueString($_POST['empto_plan_cap'], "text"),
                       GetSQLValueString($_POST['empto_max_carry_over'], "text"),
                       GetSQLValueString($_POST['interval'], "int"),
                       GetSQLValueString($_POST['period'], "text"),
                       GetSQLValueString($_POST['empto_renewal_term'], "int"),
                       GetSQLValueString($_POST['empto_time_accrued'], "double"),
                       GetSQLValueString($_POST['empto_last_updated'], "date"),
                       GetSQLValueString($_POST['empto_updated_by'], "int"));

 

But and still getting an error...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DAY'), 12, '0', '2007-05-30', 71)' at line 1

 

I checked the syntax versus what is on MySQL's site, and it looks fine. Any other help would be appreciated!

 

???

[bubblegum.anarchy]

change the following line:

 

GetSQLValueString($_POST['period'], "text"),

 

to:

 

mysql_real_escape_string($_POST['period']),

 

so that no quoting occurs.

 

[dprichard]

Thank you so much!  I thought it had something to do with the quotes, but wasn't sure how to get rid of them.