mysqlinsertbindparamarray

Mysql Insert if the Specific Column is not available for the same Employee

I have a controller to save a record

My Table contains BELOW Fields

This is Must (It has to repeat in LOOP).

I want to insert a record single time in a table for a Employee Id, and it should not repeat again. But Same Employee can have multiple Batch Ids and Multiple Course ID's.

If I take Unique as a Employee Id that is not working again to insert the another record to the same employee.

This process should repeat inside the loop and I need to get the Last Inserted ID from the Table and have to assign the number of students in the another table. This everything is working fine if I create a Procedure in Mysql and If I call Procedure. But my Linux server is not executing and throwing MySQL error.

Here is my query and 

<code>
  
    $insert_staff_assign = "insert into staff_assign 
    (`main_course_id`, `main_batch_id`, `section`, `semester_course_id`, `emp_mas_staff_id`, `emp_category`) 
    VALUES 
    (:main_course_id, :main_batch_id, :section_id, :semester_course_id, :emp_mas_staff_id, :emp_category) 
    ON DUPLICATE KEY UPDATE 
    main_course_id=:main_course_id, main_batch_id=:main_batch_id, section=:section_id, semester_course_id=:semester_course_id, emp_mas_staff_id=:emp_mas_staff_id, emp_category=:emp_category ";

    insert into staff_assign 
    (`main_course_id`, `main_batch_id`, `section`, `semester_course_id`, `emp_mas_staff_id`, `emp_category`) 
    VALUES 
    (:main_course_id, :main_batch_id, :section_id, :semester_course_id, :emp_mas_staff_id, :emp_category) 
    ON DUPLICATE KEY UPDATE 
    main_course_id=:main_course_id, main_batch_id=:main_batch_id, section=:section_id, semester_course_id=:semester_course_id, emp_mas_staff_id=:emp_mas_staff_id, emp_category=:emp_category 

    insert into staff_assign 
    (`main_course_id`, `main_batch_id`, `section`, `semester_course_id`, `emp_mas_staff_id`, `emp_category`) 
    SELECT * FROM (
        SELECT 
        :main_course_id, :main_batch_id, :section_id, :semester_course_id, :emp_mas_staff_id, :emp_category
    ) AS tmp WHERE NOT IN (
        SELECT emp_mas_staff_id FROM staff_assign WHERE emp_mas_staff_id = $save_emp_mas_staff_id
    ) LIMIT 1
  </code>

Please send me the query to get rid of this problem.

The above are my queries.

My Table Fields

Solution

  • I found the answer for the above question.

    mysql.proc problem.

    In my mysql my mysql.proc was corrupted. That is the reason it doesn't execute the above query.

    To fix the above issue you need to update mysql in linux.