Need some help with pdo update to database

[vet911]

I'm trying to update the itemno specified from available (1) to (0). I'm getting this error message and can't seem to figure it out. I must be missing something really easy. Any help would be appreciated.

 

This is the error message: 

connected

tv-jw10-976

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='78'' at line 5

Updated

$getid = $_POST['id'];
$getitemno = $_POST['itemno'];
$getavailability = $_POST['availability'];
try 
{ echo "$getitemno";
 // UPDATE data 
$sql = "UPDATE cut_stones SET itemno= :itemno, availability= :availability, WHERE id='$getid'";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':itemno', $_POST['$itemno'], PDO::PARAM_STR);
$stmt->bindParam(':availability', $_POST['availability'], PDO::PARAM_INT);
$stmt->execute(); 
} 
catch(PDOException $e) 
{ 
echo $e->getMessage();
}
// if successfully updated. 
$dhb = null; 
echo " Updated";
?>

Edited March 29, 2016 by vet911

[benanamen]

Why did you stop binding the parameters when you got to $getid?

[mac_gyver]

you have an extra comma, right near where the error message is calling your attention to.

 

and as already mentioned, why are you using a prepared query, but still putting (external) data directly into the sql statement?

[vet911]

I updated the code, I don't get any error message. But when I look at the database it doesn't change availability to (0).

Still lost.

try
	{
	echo "$getitemno";
    // UPDATE data
	$sql = "UPDATE cut_stones 
			SET 
			itemno= :itemno, 
			availability= :availability
			WHERE id= :id";
    $stmt = $dbh->prepare($sql);
	$stmt->bindParam(':id', $_POST['$id'], PDO::PARAM_INT);	
	$stmt->bindParam(':itemno', $_POST['$itemno'], PDO::PARAM_STR);    
	$stmt->bindParam(':availability', $_POST['availability'], PDO::PARAM_INT);	
	$stmt->execute(); 
    
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
	}
// if successfully updated.
$dhb = null;
	 
	 echo "       Updated";
	 
?>

[mac_gyver]

you have a couple of incorrect $_POST index names. if you had php's error_reporting set to E_ALL and display_errors set to ON you would be getting some undefined index errors alerting you to which ones.

[vet911]

$getid = $_POST['id'];
	$getitemno = $_POST['itemno'];
	$getavailability = $_POST['availability'];
		
// Connect to server and select database.
	try
	{
	echo "$getitemno";
    // UPDATE data
	$sql = "UPDATE cut_stones 
			SET 
			itemno= :itemno, 
			availability= :availability
			WHERE id= :id";
    $stmt = $dbh->prepare($sql);
	$stmt->bindParam(':id', $_POST['id'], PDO::PARAM_INT);	
	$stmt->bindParam(':itemno', $_POST['itemno'], PDO::PARAM_STR);    
	$stmt->bindParam(':availability', $_POST['availability'], PDO::PARAM_INT);	
	$stmt->execute(); 
    
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
	}
// if successfully updated.
$dhb = null;
	 
	 echo "       Updated";

I figured it out. I have listed the code above.

Thanks for the help.

Edited March 30, 2016 by vet911