Inserting PHP variables into MySQL query

Question

I have some source code for a Payroll Management System that I am altering to use for an Overtime management system. I altered a MySQL select query to filter the employees based on the selected department. Now I am trying to pass the value of the "Department" drop down list into the SELECT query that is being ran (WHERE department_id = ?). I tried following some tips and code from other posts, but they have not worked so far. I try running the code, but get an error that says "Commands out of sync..." on line 27 ($result = $employee->get_result();). I am not sure if my code is wrong, or if it is trying to execute before the department drop down list has been selected?

This is how I am wanting the code to work:

1. Select a department from the Department drop down list. (Starting line 6 in the code)

2. The value of the department drop down list is passed into the Employee drop down list SELECT query, which shows the user only employees for the selected department. (Around line 24 of the code)

   <?php include 'db_connect.php' ?>
   <?php ?>
   <div class="container-fluid">
   <div class="col-lg-12">
   <form action="" id="employee-overtime">
       <div class="row form-group">
           <div class="col-md-4">
               <label for="" class="control-label">Department</label>
               <select id="department" class="borwser-default select2"> <!-- This section 
   populates the Department drop down list -->
                   <option value=""></option>
                   <?php 
                   $department = $conn->query("SELECT department_id, name FROM employee INNER 
   JOIN department on department.id = employee.department_id");
                   while($row = $department->fetch_assoc()):
                   ?>
                       <option value="<?php echo $row['department_id'] ?>"><?php echo 
   $row['name'] ?></option>
                   <?php endwhile; ?>
               </select>
           </div>
           <div class="col-md-4">
               <label for="" class="control-label">Employee</label>
               <select id="employee_id" class="borwser-default select2"> <!-- This section 
   populates the Employee drop down list -->
                   <option value=""></option>
                   <?php 
                   $employee = $conn->prepare("SELECT *,concat(lastname,', ',firstname,' 
   ',middlename) as ename FROM employee WHERE department_id = ? order by concat(lastname,', 
   ',firstname,' ',middlename) asc");
                   $result = $employee->get_result();
                   while($row = $result->fetch_assoc()):
                   ?>
                       <option value="<?php echo $row['id'] ?>"><?php echo $row['ename'] . ' | '. 
   $row['employee_no'] ?></option>
                   <?php endwhile; ?>
               </select>
           </div>
   

Answer 1

You're getting the error because you're missing a bind_param() statement that provides a value for the ? in $conn->prepare(...).

$employee->bind_param('s', $selectedDepartment);

Which brings up the question where you're going to get $selectedDepartment.

Your script doesn't seem to reflect the fact that when the page loads for the first time, there is no selected department. So there can be no select box listing employees of that department. Also, the user may submit the form with no department selected because the first option is <option value=""></option>.

Only after you submit the form with your department choice, can you read it on the server in $_GET[] array. For that, add some name to the department SELECT box. (attribute id is not used for this.)

<select name="department" id="department" class="borwser-default select2">

Then you'll be able to read the selected department in $_GET['department'].

So to put all this together, first add if (!empty($_GET['department'])) {} statement around the area that shows employee select box:

<?php
if (!empty($_GET['department'])) {
?>
<div class="col-md-4">
       <label for="" class="control-label">Employee</label>
...
</div>
<?php
}
?>

Then add bind_param() after the employee query:

$employee = $conn->prepare("SELECT ... FROM ... WHERE department_id = ? order by ...");
$employee->bind_param('s', $_GET['department']);
$result = $employee->get_result();