'sqlstate[hy093]:

#1

i get this error when am trying to update my database. ‘SQLSTATE[HY093]:Invalid parameter number: no parameters were bound’.
here is my code.


here is my database class

please i need help here am stocked.thanks.

#2
  1. Post actual code. Pictures are for hanging on the wall.

  2. Your update and deleteRow methods are pointless duplicates that don’t do anything more than if you just called the insertRow method which is poorly named since it doesn’t explicitly insert a row(s). A more appropriate name would be runQuery since that is actually what it does.

  3. Why are YOU deciding that execute returns true? What if Php says it’s false?

  4. We have no idea where your variables are “magically” appearing from.

As to your error, I am not up to debugging a picture. I will wait for actual code to be posted.

#3

thanks for your response. am a newbie that was why i uploaded images here are my code.

This is my db class.

	$this->isConn= TRUE;
try{
$this->datab=new PDO("mysql:host={$host};dbname={$dbname};charaset=utf8", $username,$password,$options);
$this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
}catch (PDOException $e){
throw new Exception($e->getMessage()); 
}
}
//disconnect from db
public function Disconnect(){
	$this->datab=null;
	$this->isConn=false;
}
//get row
public function getRow($query,$params=[]){
	try{
	$stmt=$this->datab->prepare($query);
	$stmt->execute($params);
	return $stmt->fetch();
	}
	catch (PDOException $e){throw new Exception($e->getMessage());
	}
	}
//get rows
public function getRows($query,$params=[]){
	try{
	$stmt=$this->datab->prepare($query);
	$stmt->execute($params);
	return $stmt->fetchAll();
	echo $stmt;
	}
	catch (PDOException $e){
	throw new Exception($e->getMessage());
	}
		}
//insert row
	public function insertRow($query,$params=[]){
	try{		
	$stmt=$this->datab->prepare($query);
	$stmt->execute($params);
	return TRUE;
	}catch (PDOException $e){
	throw new Exception($e->getMessage());
	}
	}
	
//update row
	public function update($query,$params=[]){
		$this->insertRow($query,$params=[]);
	}
//delete row
	public function deleteRow($query,$params=[]){
		        $this->insertRow($query,$params=[]);
}


}


Here is my edit.php file

if (isset($_GET['id'])){
//get id.
  $id=$_GET['id'];

  $query2="SELECT * from property where id =".$id;
  $stmt=$db->getRows($query2);
  var_dump($_POST['submit']);
}

if (isset($_POST['submit']) ){
   $property_name=$_POST['property_name'];
   $property_location=$_POST['property_location'];
   $property=$_FILES['property']['name'];
   $property_price=$_POST['property_price'];
   $category_id=$_POST['category_id'];
   $status=$_POST['status'];
   $id=$_POST['id']; 
  
 
 $sql="UPDATE `property` SET  property_name =:property_name, property_location =:property_location,property_price=:property_price, category_id=:category_id,  property=:property,status=:status WHERE id=".$_GET['id'];
 var_dump($sql);
   $params = (array(':property_name'=>$property_name,
                  ':property_location'=>$property_location,
                  ':property'=>$property,
                  ':property_price'=>$property_price,
                  ':category_id'=> $category_id,
                  ':status'=>$status,
                  ':id'=>$_GET['id'])); 
                  
var_dump($params);
                 if(($db->update ($sql, $params))==1)
   
    
     {    echo "updated Successfuly. ";}
    
    else {echo "Error in update.";}
  }
#4
...WHERE id=".$_GET['id'];

:grimacing:
Here is your problem.

I hope you know why the above snippet of code is very wrong.

#5

if a var_dump ($_Get) it prints.
what do you suggest?

#6

You bind the id into the params array, but in the query you don’t have a placeholder for it, you put the $_GET directly into the query, therefore the parameter number is wrong.
Never put user input directly into a query.

#7

yeah i noticed that but don’t know what else to do.
what is the right thing please? what do i do?

#8

i have updated my code to this

if (isset($_POST['submit']) ){
   $property_name=$_POST['property_name'];
   $property_location=$_POST['property_location'];
   $property=$_FILES['property']['name'];
   $property_price=$_POST['property_price'];
   $category_id=$_POST['category_id'];
   $status=$_POST['status'];
   $id=$_GET['id']; 
   //var_dump($_POST);
 
 $sql="UPDATE `property` SET id=:id  property_name =:property_name, property_location =:property_location,property_price=:property_price, category_id=:category_id,  property=:property,status=:status WHERE id=".$_GET['id'];
 var_dump($sql);
   $params = (array(':property_name'=>$property_name,
                  ':property_location'=>$property_location,
                  ':property'=>$property,
                  ':property_price'=>$property_price,
                  ':category_id'=> $category_id,
                  ':status'=>$status,
                  ':id'=>$id)); 

But i still get the same error.

#9

uhhhh… no? Why do you have an assignment operator in a function call?

#10

params is an array that is why.

#11

yes, but your function call shouldnt be redefining the variable. It’s already defined by the update function.

$this->insertRow($query,$params);

#12
...WHERE id = :id" ;

Now this ID is passed in safely as a placeholder, there are now 7 placeholders in the query and 7 values in the params array.

#13

i have done that i am still having same issues.

#14

thanks this fixed the issue.

#15

thanks guys for your time i was able to fix the issue with all your effort.
here is the problem

	public function insertRow($query,$params=[]){
	try{		
	$stmt=$this->datab->prepare($query);
	$stmt->execute($params);
	return TRUE;
	}catch (PDOException $e){
	throw new Exception($e->getMessage());
	}
	}
	
//update row
	public function update($query,$params){
		$this->insertRow($query,$params=[]);
	}
//delete row
	public function deleteRow($query,$params=[]){
		        $this->insertRow($query,$params=[]);
}

i now made it

	public function runQuery($query,$params=[]){
	try{		
	$stmt=$this->datab->prepare($query);
	$stmt->execute($params);
	return TRUE;
	}catch (PDOException $e){
	throw new Exception($e->getMessage());
	}
	}

it is now working fine.

#16

You still have not addressed what I said about YOU forcing execute to return true. Please refer to the manual for execute.

The return values for execute are by default either TRUE on success or FALSE on failure. Yet you are forcing a TRUE return even if the query fails and execute returns FALSE.

To further add, you should not be outputting internal system errors to the user. Ditch all the Try/Catch blocks. Php will handle exceptions all by it self. If you want to do some sort of custom error handling, then use set_exemption_handler. All you are doing is littering your code base. Besides, all you are doing is re-throwing an exception that has already been thrown and not doing anything different with it.

Additionally, do not create variables for nothing. Depending on the name of a button to be submitted in order for your script to work will completely fail in certain cases. You need to check the REQUEST METHOD instead.

Feel free to use the short array syntax. It has been available since Php version 5.4.

$array = [
      "foo" => "bar"
    , "bar" => "foo"
    , "barfoo" => "foobar"
];

Take notice of the comma first format. On a large array you will quickly see the value of using it. You can also use it with your SQL parameters.

UPDATE `property`
SET     id=:id
      , property_name =:property_name
      ,  property_location =:property_location
      , property_price=:property_price
      , category_id=:category_id
      , property=:property
      , status=:status
WHERE   id= :id

I would VERY HIGHLY recommend you take a brief break and get your self the book PHP & MySQL: Novice to Ninja, 6th Edition by our resident author @TomB. (Tom Butler and Kevin Yank). It will advance your skills by leaps and bounds. I wish I had that book when I was starting out.

closed #17

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.