Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Beginners - Learning PHP
#1

Please I have am seeing this error after migrating from php 7.3 to 8.1.

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter
number: number of bound variables does not match number of tokens in
/var/www/hospital/classes/users.class.php:2871 Stack trace: #0
/var/www/hospital/classes/users.class.php(2871):
PDOStatement->execute() #1
/var/www/hospital/classes/usersview.class.php(71):
Users->getAllvisits() #2 /var/www/hospital/user.php(1176):
UsersView->showAllvisits() #3 {main} thrown in
/var/www/hospital/classes/users.class.php on line 2871

This is how I USUALLY write prepared statement inside users.class.php for most function and no error with this.

 protected function getAlluserregtype($reg_type_id,$subgroup, $hospital_id){
		$sql="SELECT `user_id` FROM `user_reg_type` WHERE `reg_type_id`=? AND `subgroup`=? AND `hospital_id`=? ORDER BY `id` DESC";
		$stmt= $this->connect()->prepare($sql);
		$stmt->execute([$reg_type_id,$subgroup, $hospital_id]);
		$user=$stmt->fetchAll();
		return $user;

	}

 protected function getLimiteduserregtype($reg_type_id,$subgroup, $hospital_id,$offset,$limit){
		$sql="SELECT `user_id` FROM `user_reg_type` WHERE `reg_type_id`=? AND `subgroup`=? AND `hospital_id`=? ORDER BY `id` DESC LIMIT ?,?";
		$stmt= $this->connect()->prepare($sql);
		 $stmt->bindParam(1, $reg_type_id, PDO::PARAM_STR);
		 $stmt->bindParam(2, $subgroup, PDO::PARAM_STR);
			$stmt->bindParam(3, $hospital_id, PDO::PARAM_INT);
			$stmt->bindParam(4, $offset, PDO::PARAM_INT);
			$stmt->bindParam(5, $limit, PDO::PARAM_INT);
		 
		$stmt->execute();
		$user=$stmt->fetchAll();
		return $user;

	}

BUT WHEN I WRITE IT LIKE this I got error. Please I need help to solve it. Because this is the only way I think I can execute it under user.php without missing any variable
This is the users.class.php for the error function

protected function getAllvisits($pat_id){
		$sql="SELECT COUNT(DISTINCT(DATE(`at`))) AS visits FROM (SELECT `at`  FROM `treatments` WHERE `pat_id`='$pat_id' 
        
        UNION ALL
        SELECT `at`  FROM `complaints` WHERE `patient_id`='$pat_id' 
        
        UNION ALL
        SELECT `at` FROM `diagnosis` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `clinical_note` WHERE `patient_id`='$pat_id' 
        UNION ALL
         SELECT `at` FROM `round_ward` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `recommended_procedure` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `patient_symptoms` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `medical_exam` WHERE `patient_id`='$pat_id' 
         UNION ALL
        SELECT `at` FROM `patient_test` WHERE `patient_id`='$pat_id' 
          UNION ALL
           SELECT `at` FROM `past_obsterics` WHERE `patient_id`='$pat_id' 
          UNION ALL
        SELECT `at` FROM `patient_prescription` WHERE `patient_id`='$pat_id' 
          UNION ALL
        SELECT `at` FROM `patient_ward` WHERE `patient_id`='$pat_id' 
         UNION ALL
        SELECT `at` FROM `anc` WHERE `patient_id`='$pat_id' 
         UNION ALL
        SELECT `at` FROM `present_obsterics` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `presenting_complaint` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `medical_history` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `family_complaint` WHERE `patient_id`='$pat_id' 
        UNION ALL
        SELECT `at` FROM `vacination_schedule` WHERE `patient_id`='$pat_id'
       UNION ALL
        SELECT `at` FROM  `diet_treatment` WHERE `patient_id`='$pat_id' 
       UNION ALL
        SELECT `at` FROM `physiotheraphy_treatment` WHERE `patient_id`='$pat_id' 
        ) t
        ";
		$stmt= $this->connect()->prepare($sql);
		$stmt->execute([$pat_id]);
		$user=$stmt->fetch();
		return $user;

	}

And the other class here usersview.class.php

 public function showAllvisits($pat_id)
	{
		$results=$this->getAllvisits($pat_id);
		return $results;


	}

Implement here user.php

$patvisits=$usersview->showAllvisits($patient['user_id']);

Thanks. Please I humbly request someone help me rewrite this code so as not to bring error.

#2

The simple solution to your current problem would be to use array_fill() to produce an array with enough entries and supply it to the ->execute() call.

#3

Okay. Thanks can you help me to do it . Giving me sample on how to do it inside user.class.php, userview.class.php and user.php. Thanks

#4

You are expected to make an attempt at doing your own work. We are only here to provide programming help, not free programming services.

#5

The error you’re seeing suggests that the number of parameters being passed to execute() does not match the number of placeholders in the SQL query. In this case, the query has no placeholders, so passing an argument to execute() is not necessary.

Here’s how you can modify your getAllvisits() method in users.class.php to fix the error:

protected function getAllvisits($pat_id){
    $sql="SELECT COUNT(DISTINCT(DATE(`at`))) AS visits FROM (
        SELECT `at`  FROM `treatments` WHERE `pat_id`=?
        UNION ALL
        SELECT `at`  FROM `complaints` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `diagnosis` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `clinical_note` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `round_ward` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `recommended_procedure` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `patient_symptoms` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `medical_exam` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `patient_test` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `past_obsterics` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `patient_prescription` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `patient_ward` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `anc` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `present_obsterics` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `presenting_complaint` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `medical_history` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `family_complaint` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `vacination_schedule` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM  `diet_treatment` WHERE `patient_id`=?
        UNION ALL
        SELECT `at` FROM `physiotheraphy_treatment` WHERE `patient_id`=?
    ) t";
    $stmt= $this->connect()->prepare($sql);
    $stmt->execute([$pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id, $pat_id]);
    $visits=$stmt->fetchColumn();
    return $visits;
}

Here, I have added placeholders to the query and are passing an array of parameters to execute(). Also, I’m using fetchColumn() instead of fetch() since you are only interested in a single column value.

And here’s how you can modify the showAllvisits() method in usersview.class.php:

public function showAllvisits($pat_id)
{
    $users = new Users();
    $visits = $users->getAllvisits($pat_id);
    return $visits;
}

I hope that works.

Sponsor our Newsletter | Privacy Policy | Terms of Service