PHP PDO error - SQLSTATE[HY093]: Invalid parameter number

Question

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);

Add:

When I replace this line

$query_conditions[] = "(wt_users.name LIKE :name OR wt_users.surname LIKE :name)";

With this line:

 $query_conditions[] = "(wt_users.name LIKE :name)";

It fixes the error, but it stop searching for surname.

What does the query come out as and what parameters do you have? var_dump($sql, $query_params)... generally Invalid parameter number means you have more placeholders or values — user3783243
– user3783243, Commented Jul 26, 2024 at 20:22
Is the str_replace to avoid SQL injections also? If so that is not needed and may result in you getting incorrect results. I would remove it. — user3783243
– user3783243, Commented Jul 26, 2024 at 20:26
You can't have :name palceholder twice. Use something like wt_users.name LIKE :name OR wt_users.surname LIKE :sname and set both sname and name. — u_mulder
– u_mulder, Commented Jul 26, 2024 at 21:16
Are emulations on? Have you outputted the query and the parameters? — user3783243
– user3783243, Commented Jul 26, 2024 at 21:19

u_mulder · Accepted Answer · 2024-07-26 21:50:33Z

0

@u-mulder's solution was correct, I had to change the name of second placeholder:

u-mulder: You can't have :name palceholder twice. Use something like wt_users.name LIKE :name OR wt_users.surname LIKE :sname and set both sname and name.

My solution:

$query_conditions[] = "(wt_users.name LIKE :name OR wt_users.surname LIKE :surname)";
$query_params[':name'] = "%" . $search_params['name'] . "%";
$query_params[':surname'] = "%" . $search_params['name'] . "%";

edited Jul 26, 2024 at 21:50

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

answered Jul 26, 2024 at 21:32

cornacum

695 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP PDO error - SQLSTATE[HY093]: Invalid parameter number

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related