case statement in INSERT INTO statement

Question

I have the following register.php code

<?php
if( $_POST )
{
  include("php_connect/php_connect.php");

  $start_year  = $_POST['start_year'];
  $start_month = $_POST['start_month'];
  $start_day = $_POST['start_day'];
  $start_hour = $_POST['start_hour'];
  $start_minute = $_POST['start_minute'];
  $place = $_POST['place'];
  $salah = $_POST['salah'];
  $rakat = implode(",", $_POST['rakat']); 

  $query = "INSERT INTO performed_salah 
           (year, month, date, time, place, prayer, rakat, reg_date) VALUES 
           ('$start_year', '$start_month', '$start_day', ('$start_hour:$start_minute'), '$place', '$salah', '$rakat', NOW())";

  if ($conn->query($query) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $query . "<br>" . $conn->error;
}

$conn->close();
}
?>

In my table i have a column performance. I am trying to create a CASE STATEMENT that looks something like

CASE WHEN prayer='A' and rakat=('Rakat-A,Rakat-B') then performance = 100%
     WHEN prayer='A' and rakat=('Rakat-A') or rakat=('Rakat-B') then performance = 50%
     WHEN prayer='B' and rakat=('Rakat-A,Rakat-B,Rakat-C,Rakat-D) then performance = 100%
WHEN prayer = 'B' and rakat=('Rakat-A,Rakat-B,Rakat-C') then performace = 75%
WHEN prayer = 'B' and rakat=('Rakat-A,Rakat-B') then performace = 50%
WHEN prayer = 'B' and rakat=('Rakat-A') then performace = 25%

How to i go about making it?

I tried this but got error:

"INSERT INTO performed_salah('$start_year', '$start_month', '$start_day', ('$start_hour:$start_minute'), '$place', '$salah', '$rakat', 
(case when prayer = 'fajar' and (rakat='2Sunnat,2Faradh') then " . '100%' . "    
      when prayer = 'fajar' and (rakat='2Sunnat' or rakat='2Faradh') then " . ' '50%' . "end) '$performance', NOW())";

Answer 1

This is your query:

INSERT INTO performed_salah(year, month, date, time, place, prayer, rakat, reg_date)
     VALUES  ('$start_year', '$start_month', '$start_day', ('$start_hour:$start_minute'), '$place', '$salah', '$rakat', NOW());

It makes sense. Your second query makes no sense at all. But, one fundamental problem is that the case statement cannot use column names from the table. You can do that after the fact, using an update, but in the insert, you need the variables used for the insert. Something like this:

INSERT INTO performed_salah(year, month, date, time, place, prayer,
            rakat, reg_date, performance)
     VALUES  ('$start_year', '$start_month', '$start_day', ('$start_hour:$start_minute'),
              '$place', '$salah', '$rakat',
               (case when '$prayer' = 'fajar' and '$rakat' = '2Sunnat,2Faradh'
                     then '100%'   
                     when '$prayer' = 'fajar' and '$rakat' in ('2Sunnat', '2Faradh')
                     then '50%' 
               end),
              NOW());

There is still lots of room for improvement. A field like performance looks like a number, so you should store it that way. SQL code is much safer if you use parameters, rather than explicitly putting the values in the string. But this might give you an idea on how to proceed.