vet911 Posted March 29, 2016 Share Posted March 29, 2016 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/ Share on other sites More sharing options...
benanamen Posted March 30, 2016 Share Posted March 30, 2016 Why did you stop binding the parameters when you got to $getid? Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/#findComment-1532601 Share on other sites More sharing options...
mac_gyver Posted March 30, 2016 Share Posted March 30, 2016 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? Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/#findComment-1532602 Share on other sites More sharing options...
vet911 Posted March 30, 2016 Author Share Posted March 30, 2016 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"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/#findComment-1532603 Share on other sites More sharing options...
mac_gyver Posted March 30, 2016 Share Posted March 30, 2016 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. Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/#findComment-1532604 Share on other sites More sharing options...
Solution vet911 Posted March 30, 2016 Author Solution Share Posted March 30, 2016 (edited) $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 Quote Link to comment https://forums.phpfreaks.com/topic/301111-need-some-help-with-pdo-update-to-database/#findComment-1532608 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.