could someone have a look at my code and help me figure out what is causing the error in my PDO query.

The query works fine when I search by “Department”, “Datarange”, “Employer” and “Status”. But when I search and there is data in the “name” field, it returns this error message.

Error message:

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in /home/xyz/public_html/dir1/user3/user_lists.php:102 Stack trace: #0 /home/xyz/public_html/dir1/user3/user_lists.php(102): PDOStatement->execute() #1 {main} thrown in /home/xyz/public_html/dir1/user3/user_lists.php on line 102

The line 102 in my code is: $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

My Code:

// Get org ID only from the array
$current_organisation_id = $current_organisation['current_organisation_id'];

// Initialize search parameters
$search_params = [
    'name' => trim(isset($_POST['search_name']) ? str_replace("'", "", $_POST['search_name']) : ''),
    'start_date_range' => isset($_POST['search_start_date']) ? $_POST['search_start_date'] : '',
    'status' => trim(isset($_POST['search_status']) ? str_replace("'", "", $_POST['search_status']) : ''),
    'employer' => trim(isset($_POST['search_employer']) ? str_replace("'", "", $_POST['search_employer']) : ''),
    'department' => trim(isset($_POST['search_department']) ? str_replace("'", "", $_POST['search_department']) : '')
];

// Parse date range if provided
$by_start_date_from = "";
$by_start_date_to = "";
if ($search_params['start_date_range'] != "") {
    $by_start_date_from = substr($search_params['start_date_range'], 6, 4) . "-" . substr($search_params['start_date_range'], 3, 2) . "-" . substr($search_params['start_date_range'], 0, 2);
    $by_start_date_to = substr($search_params['start_date_range'], 19, 4) . "-" . substr($search_params['start_date_range'], 16, 2) . "-" . substr($search_params['start_date_range'], 13, 2);
}


// Base SQL query
$sql = "SELECT 
    tb_users.id AS users_id, 
    tb_users.name AS users_name, 
    tb_users.surname AS users_surname, 
    tb_users.username AS users_username, 
    tb_users.password AS users_password, 
    tb_users.created AS users_created, 
    tb_users.active AS users_active, 
    tb_users.employer AS users_employer, 
    tb_users.department AS users_department, 
    tb_employer.id AS employer_id, 
    tb_employer.employer_name AS employer_name, 
    tb_departments.id AS department_id, 
    tb_departments.department_name AS department_name, 
    tb_user_organisation.user_id AS user_organisation_id, 
    tb_schedules.id AS schedule_id, 
    tb_schedules.schedule_name AS schedule_name 
FROM 
    tb_users 
    JOIN tb_user_organisation ON tb_users.id = tb_user_organisation.user_id 
    JOIN tb_employer ON tb_users.employer2 = tb_employer.id 
    JOIN tb_schedules ON tb_users.schedule_id = tb_schedules.id 
    JOIN tb_departments ON tb_users.department = tb_departments.id 
WHERE 
    tb_user_organisation.organisation_id = :current_organisation_id";


// Array to hold query conditions
$query_conditions = [];
$query_params = [':current_organisation_id' => $current_organisation_id];

// Loop through search parameters to build query conditions
if ($_POST) {
    if ($search_params['name'] != "") {
        $query_conditions[] = "(tb_users.name LIKE :name OR tb_users.surname LIKE :name)";
        $query_params[':name'] = "%" . $search_params['name'] . "%";
    }
    if ($by_start_date_from != "") {
        $query_conditions[] = "LEFT(tb_users.created, 10) >= :by_start_date_from";
        $query_params[':by_start_date_from'] = $by_start_date_from;
    }
    if ($by_start_date_to != "") {
        $query_conditions[] = "LEFT(tb_users.created, 10) <= :by_start_date_to";
        $query_params[':by_start_date_to'] = $by_start_date_to;
    }
    if ($search_params['status'] != "x") {
        $query_conditions[] = "tb_users.active = :status";
        $query_params[':status'] = $search_params['status'];
    }
    if ($search_params['employer'] != "x") {
        $query_conditions[] = "tb_employer.id = :employer";
        $query_params[':employer'] = $search_params['employer'];
    }
    if ($search_params['department'] != "x") {
        $query_conditions[] = "tb_users.department = :department";
        $query_params[':department'] = $search_params['department'];
    }
}

// Append conditions to the SQL query
if (!empty($query_conditions)) {
    $sql .= " AND " . implode(" AND ", $query_conditions);
}

$sql .= " ORDER BY tb_users.id DESC";
//echo $sql;

// Prepare and execute the query
$stmt = $pdo->prepare($sql);
$stmt->execute($query_params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);