I have 2 columns in a staff table that each column values has to be unique (staff_code, staff_name)
| staff_id | staff_code | staff_name |
|-------------|------------|------------|
| 1 | MGT | Management |
| 2 | IT | IT staff |
When inserting or updating an item to the table I have to check whether staff_code is and staff_name is unique.
In the current procedure I use 4 functions to check it.
- Check staff_code when inserting,
- Check staff_code when updating
- Check staff_name when inserting
- Check staff_name when updating
I have included the simplified code
The function for checking 1)
$SELECT * FROM staff WHERE staff_code = $staff_code
if num_of_rows > 0 //cannot insert, staff_type already exists
The function for checking 2)
get current staff type from
$current_staff_type => SELECT * FROM staff WHERE staff_id = $staff_id
if($current_staff_type == $updated_staff_type){
//don't update
return
}
SELECT * FROM staff WHERE staff_type = $updated_staff_type
if(num_of_rows > 0){
//the type you're going to update already exists
return
} else{
//update
}
if num_of_rows > 0 //cannot insert, staff_type already exists
I have similar functions for other 2 as well. The thing is I have to send the error messages separately for the 4 conditions. Is this the best practice to do this? Can I do it another simple way than accessing database several times?