Insert Using Case

[SalientAnimal]

Hi All,

 

I have a form that has an attendance status field. This field needs to populate two different rows in my table. But I am not able to get my case statement to work.

 

Here is my current code:

        // INSERT THE NEW FOR INFORMATION INTO THE DATABASE TABLE
        if ($insert_stmt = $mysqli->prepare(	"INSERT INTO 
															shift_agent_report 
												(			
															shift
												,			username
												, 			agent
												, 			attendance_indicator
												, 			attendance_status
												, 			agent_comments
												) 
												VALUES 
												(
															?
												, 			?
												, 			?
												, 			(CASE 
																	WHEN (attendance_status = 'Present') THEN '0'
																	ELSE '1'
															END) AS attendance_indicator
												, 			?														
												, 			?
												)"))
			

		{
	
			$insert_stmt->bind_param('ssssss' ,$shift, $username, ucfirst($agent), $attendance_status, $attendance_status, $agent_comments);
            // EXECUTE THE PREPARED QUERY
            if (! $insert_stmt->execute())
			//PRINT THE NUMBERS OF ROWS THAT HAVE BEEN AFFECTED 
 			{
				//var_dump($_POST);exit;
				header('Location: ../error.php?err=Registration failure: INSERT');
				exit;

            }
			include "../success/shift_agent_success.php";
			exit;
        }

		

		/* CLOSE THE CONNECTION */
		$mysqli->close();
    }

}

?>

[Barand]

Set the value of $attendance_indicator in the code prior to the insert and use "?" instead of the CASE statement

$attendance_indicator = ($attendance_status=='Present') ? "1": "0";
$insert_stmt->bind_param('ssssss' ,$shift, $username, ucfirst($agent), $attendance_indicator, $attendance_status, $agent_comments);

Alternatively, specify the status before the indicator in the insert column list so it then has a value before you do the CASE for the indicator and you only need 5 parameters as the indicator is a case statement

INSERT INTO 
     shift_agent_report 
     (            
          shift
     ,    username
     ,    agent
     ,    attendance_status
     ,    attendance_indicator
     ,    agent_comments
     ) 
     VALUES 
     (
          ?
     ,    ?
     ,    ?
     ,    ?                    
     ,    (CASE 
          WHEN (attendance_status = 'Present') THEN '0'
          ELSE '1'
          END)
     ,    ?
     )

then

$insert_stmt->bind_param('sssss' ,$shift, $username, ucfirst($agent), $attendance_status, $agent_comments);

Edited February 11, 2014 by Barand

[SalientAnimal]

Awesome, was exactly what I was looking for. Thanks.