PHP function for update rows is working but generating an error message

I have written this function using PDO statement for updating rows in table ‘bengali’. The function is working but generating an error message. The error message is at the bottom of the topic.

public function dataUpdate(){
		$id="";
		$Updatefield = "";
		if(isset($_POST['update'])){
				$id= $_POST['id'];
				$Updatefield = $_POST['Bn_Meaning'];
				}
		$sql = "UPDATE bengali SET Bn_Meaning ='$Updatefield' WHERE id=$id";
		// Prepare statement
		$stmt = $this->dbconn()->prepare($sql);
		$stmt->execute();
		// execute the query
		echo $stmt->rowCount() . " records UPDATED successfully";
	}

I called the function by creating the object as below in update.php file

<?php
	$update = new GetData();
	$update->dataUpdate();
?>

The error message:
: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘’ at line 1 in C:\xampp\htdocs\update\classes\GetData.php:56 Stack trace: #0 C:\xampp\htdocs\update\classes\GetData.php(56): PDOStatement->execute() #1 C:\xampp\htdocs\update\includes\update.php(58): GetData->dataUpdate() #2 C:\xampp\htdocs\update\index.php(18): include_once(‘C:\xampp\htdocs…’) #3 {main} thrown in C:\xampp\htdocs\update\classes\GetData.php on line 56

The error means your function/method is being called with an empty $id value. It is probably being called at a point in time when there is no $_POST data. There are five issues with the posted code -

  1. This is NOT how to do a prepared query. The main point of a prepared query is that they separate the sql syntax from any external/unknown data values. You do this by putting a place-holder (a ? positional place-holder is the simplest to use) in the sql query statement where each value goes, then supply the value(s) (as an array to the ->execute([…]) method call) for the place-holders when you execute the query.
  2. The code is not validating all the inputs before using them. The three inputs to this code are ‘required’ for it to work. You must validate that they contain expected values before even calling this function/method. The current logic you have testing if $_POST['update'] isset() should be part of your main code and should control if the rest of the form processing code is executed. You should then validate that the two input values are not empty strings before calling the function/method.
  3. The input data to a function/method should be passed as call-time parameters, so that the function/method is general purpose and can be called with values from any source.
  4. Your post method form processing code should first detect that a post method form has been submitted before referencing any of the form data.
  5. Functions/methods should not output anything. They should return any result they produce to the calling code. The echo rowCount… should not be part of this function/method.

The error output also indicates that you are using emulated prepared queries (the sql syntax error was detected at the execute() call, not at the prepare() call.) When you make the database connection, you should set emulated prepared queries to false.

There’s also one more minor point from the error output. You should use ‘require’ for things that your code must have for it to work, not ‘include’ or ‘include_once’.

Wow! Learnt many things. Thanks a lot. After I assign a value to $id, it is working.
Is the following code is correct now? Please suggest me for more perfection.

           <?php
	public function dataUpdate($id, $Updatefield){
	$sql = "UPDATE bengali SET Bn_Meaning =:Bn_Meaning WHERE id=:";
	// Prepare statement
	$stmt = $this->dbconn()->prepare($sql);
	$stmt -> bindParam(':id' , $id , PDO::PARAM_INT);
	$stmt -> bindParam(':Bn_Meaning', $Updatefield, PDO::PARAM_STR);
	$stmt->execute();
	//What should I return from here?
	}

			
	//function caller is here
	
	$id="";
	$Updatefield = "";
	if(isset($_POST['update'])){
		$id= $_POST['id'];
		$Updatefield = $_POST['Bn_Meaning'];
		$update = new GetData();
		$update->dataUpdate($id, $Updatefield);
		if($query -> rowCount() > 0)
		{
			echo $stmt->rowCount() . " records UPDATED successfully";
		}
		else{
			echo "No update";
		}
	?>

you’re still missing that placeholder.

You should be returned true or false for an update function. RowCount should be in that function to verify that an update was made and return the boolean from there.

Thanks for reply. But not clearly understood by me. I think, I used this placeholder in my code line no 5.
$stmt -> bindParam(’:id’ , $id , PDO::PARAM_INT);
Besides how can I use the true or false as return of the function?

You used part of a placeholder, all that is there is the colon, not the id portion.

	public function dataUpdate($id, $Updatefield){
	    $sql = "UPDATE bengali SET Bn_Meaning =:Bn_Meaning WHERE id=:id";
	    // Prepare statement
	    $stmt = $this->dbconn()->prepare($sql);
	    $stmt -> bindParam(':id' , $id , PDO::PARAM_INT);
	    $stmt -> bindParam(':Bn_Meaning', $Updatefield, PDO::PARAM_STR);
	    $stmt->execute();
	
        return $stmt->rowCount() == 0 ?  false : true;
	}

Oh! I see. Thanks for your help.

Sponsor our Newsletter | Privacy Policy | Terms of Service