pdo UPDATE statment in my code not working.

[mike16889]

ok, im writing a simple CRUD application for my work and i am having trouble with the UPDATE statement in the following code, the insert statement works fine.

 

frm_bpd_sv.php:

<?php 
error_reporting(-1);
 
$page['title'] = "BPD Save";
require_once('config.inc.php');
 
if(isset($_SESSION['jobDetailID'])){
$jobdetailid = $_SESSION['jobDetailID'];
} else {
header("location:index.php");
}
 
try {
$dbh = new PDO("mysql:host=$db_host;dbname=$db_name", $db_username, $db_password);
}
catch(PDOException $e){
echo $e->getMessage();
}
if(isset($_GET['editID'])){$data['bpdID'] = $_GET['editID'];}
$data['jobDetailID'] = $jobdetailid;
if(isset($_POST['backflowID'])){$data['backflowID'] = $_POST['backflowID'];} else {$data['backflowID'] = null;}
if(isset($_POST['bpdOnsite'])){$data['bpdOnsite'] = $_POST['bpdOnsite'];} else {$data['bpdOnsite'] = 0;}
if(isset($_POST['bpdLocation'])){$data['bpdLocation'] = $_POST['bpdLocation'];} else {$data['bpdLocation'] = null;}
if(isset($_POST['bpdMake'])){$data['bpdMake'] = $_POST['bpdMake'];} else {$data['bpdMake'] = null;}
if(isset($_POST['bpdModel'])){$data['bpdModel'] = $_POST['bpdModel'];} else {$data['bpdModel'] = null;}
if(isset($_POST['bpdSerial'])){$data['bpdSerial'] = $_POST['bpdSerial'];} else {$data['bpdSerial'] = null;}
if(isset($_POST['bpdSize'])){$data['bpdSize'] = $_POST['bpdSize'];} else {$data['bpdSize'] = null;}
if(isset($_POST['protectionType'])){$data['protectionType'] = $_POST['protectionType'];} else {$data['protectionType'] = null;}
if(isset($_POST['bpdType'])){$data['bpdType'] = $_POST['bpdType'];} else {$data['bpdType'] = null;}
if(isset($_POST['protectionLvl'])){$data['protectionLvl'] = $_POST['protectionLvl'];} else {$data['protectionLvl'] = null;}
if(isset($_POST['wmNo'])){$data['wmNo'] = $_POST['wmNo'];} else {$data['wmNo'] = null;}
if(isset($_POST['wmSize'])){$data['wmSize'] = $_POST['wmSize'];} else {$data['wmSize'] = null;}
if(isset($_POST['wmLocation'])){$data['wmLocation'] = $_POST['wmLocation'];} else {$data['wmLocation'] = null;}
if(isset($_POST['sprinklers'])){$data['sprinklers'] = $_POST['sprinklers'];} else {$data['sprinklers'] = null;}
if(isset($_POST['drinkingTaps'])){$data['drinkingTaps'] = $_POST['drinkingTaps'];} else {$data['drinkingTaps'] = null;}
if(isset($_POST['longitude'])){$data['longitude'] = $_POST['longitude'];} else {$data['longitude'] = null;}
if(isset($_POST['latitude'])){$data['latitude'] = $_POST['latitude'];} else {$data['latitude'] = null;}
if(isset($_POST['accuracy'])){$data['accuracy'] = $_POST['accuracy'];} else {$data['accuracy'] = null;}
if(isset($_POST['activity'])){$data['activity'] = $_POST['activity'];} else {$data['activity'] = null;}
if(isset($_POST['permisionTTOW'])){$data['permisionTTOW'] = $_POST['permisionTTOW'];} else {$data['permisionTTOW'] = null;}
if(isset($_POST['comments'])){$data['comments'] = $_POST['comments'];} else {$data['comments'] = null;}
if(isset($_POST['pass'])){$data['pass'] = $_POST['pass'];} else {$data['pass'] = null;}
print_r($data);
echo '<hr>'.$_GET['editID'];
if(isset($_GET['editID'])){
$editID = $_GET['editID'];
$temp = "UPDATE frm_bpd SET `backflowID` = :backflowID, `jobDetailID` = :jobDetailID, `bpdOnsite` = :bpdOnsite, `bpdLocation` = :bpdLocation, `bpdMake` = :bpdMake, `bpdModel` = :bpdModel, `bpdSerial` = :bpdSerial, `bpdSize` = :bpdSize, `protectionType` = :protectionType, `bpdType` = :bpdType, `protectionLvl` = :protectionLvl, `wmNo` = :wmNo, `wmSize` = :wmSize, `wmLocation` = :wmLocation, `sprinklers` = :sprinklers, `drinkingTaps` = :drinkingTaps, `longitude` = :longitude, `latitude` = :latitude, `accuracy` = :accuracy, `activity` = :activity, `permisionTTOW` = :permisionTTOW, `comments` = :comments, `pass` = :pass` WHERE `bpdID` = :bpdID";
} else {
$editID = false;
$temp = "INSERT INTO frm_bpd (`backflowID`, `jobDetailID`, `bpdOnsite`, `bpdLocation`, `bpdMake`, `bpdModel`, `bpdSerial`, `bpdSize`, `protectionType`, `bpdType`, `protectionLvl`, `wmNo`, `wmSize`, `wmLocation`, `sprinklers`, `drinkingTaps`, `longitude`, `latitude`, `accuracy`, `activity`, `permisionTTOW`, `comments`, `pass`) VALUES (:backflowID, :jobDetailID, :bpdOnsite, :bpdLocation, :bpdMake, :bpdModel, :bpdSerial, :bpdSize, :protectionType, :bpdType, :protectionLvl, :wmNo, :wmSize, :wmLocation, :sprinklers, :drinkingTaps, :longitude, :latitude, :accuracy, :activity, :permisionTTOW, :comments, :pass);"; 
}
 
try {
$sql = $dbh->prepare($temp);
$sql->execute($data);
$succsess = 1;
} catch(PDOException $e) {
header("location:frm_bpd.php?id=".$editID."&message=".$e->getMessage());
$succsess = 0;
}
 
if($editID == false){
$editID = $dbh->lastInsertId();
}
 
/*if($succsess == 1){
header("location:frm_bpd.php?id=".$editID."&message=BPD%20Saved");
} else {
header("location:frm_bpd.php?id=".$editID."&message=An%20Error%20Occured");
}*/
?>

 

frm_bpd Table:

-- phpMyAdmin SQL Dump
-- version 4.1.4
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 10, 2014 at 01:36 AM
-- Server version: 5.6.15-log
-- PHP Version: 5.4.24
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
--
-- Database: `omni`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `frm_bpd`
--
 
CREATE TABLE IF NOT EXISTS `frm_bpd` (
  `bpdID` int(11) NOT NULL AUTO_INCREMENT,
  `backflowID` varchar(255) DEFAULT NULL,
  `jobDetailID` int(11) DEFAULT NULL,
  `bpdOnsite` tinyint(11) DEFAULT '0',
  `bpdLocation` varchar(255) DEFAULT NULL,
  `bpdMake` varchar(255) DEFAULT NULL,
  `bpdModel` varchar(255) DEFAULT NULL,
  `bpdSerial` varchar(255) DEFAULT NULL,
  `bpdSize` int(11) DEFAULT NULL,
  `protectionType` tinyint(11) DEFAULT NULL,
  `bpdType` tinyint(4) DEFAULT NULL,
  `protectionLvl` tinyint(4) DEFAULT NULL,
  `wmNo` varchar(255) DEFAULT NULL,
  `wmSize` int(11) DEFAULT NULL,
  `wmLocation` varchar(255) DEFAULT NULL,
  `sprinklers` tinyint(11) DEFAULT NULL,
  `drinkingTaps` tinyint(11) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `accuracy` int(11) DEFAULT NULL,
  `activity` tinyint(11) DEFAULT NULL,
  `permisionTTOW` tinyint(11) DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  `pass` tinyint(11) DEFAULT NULL,
  UNIQUE KEY `bpdID` (`bpdID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 
--
-- Dumping data for table `frm_bpd`
--
 
INSERT INTO `frm_bpd` (`bpdID`, `backflowID`, `jobDetailID`, `bpdOnsite`, `bpdLocation`, `bpdMake`, `bpdModel`, `bpdSerial`, `bpdSize`, `protectionType`, `bpdType`, `protectionLvl`, `wmNo`, `wmSize`, `wmLocation`, `sprinklers`, `drinkingTaps`, `longitude`, `latitude`, `accuracy`, `activity`, `permisionTTOW`, `comments`, `pass`) VALUES
(1, NULL, 1328449, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(2, NULL, 1328449, -1, '2', '3', '4', '5', 6, 1, 2, NULL, '7', 8, '9', -1, -1, NULL, '', 0, 2, -1, 'bla', -1);

 

output of $data with print_r();

Array ( [bpdID] => 1 [jobDetailID] => 1328449 [backflowID] => [bpdOnsite] => -1 [bpdLocation] => some [bpdMake] => [bpdModel] => [bpdSerial] => [bpdSize] => [protectionType] => 0 [bpdType] => 0 [protectionLvl] => [wmNo] => [wmSize] => [wmLocation] => [sprinklers] => [drinkingTaps] => [longitude] => [latitude] => [accuracy] => [activity] => 0 [permisionTTOW] => [comments] => [pass] => )

 

i know it must be a typo somewhere but i just cant spot it, as i said the insert statement works fine, and no errors are being thrown. am i just not using the UPDATE statement correctly?

 

any help would be greatly appreciated.

[fastsol]

You have an extra ` here `pass` = :pass` WHERE in the update query string.

[mike16889]

thanks for that, i haven't tested it yet but that would definitely do it, knew it was a typo somewhere (copy and paste error).

[mac_gyver]

the reason your code didn't tell you about the error is you need to setup pdo to throw exceptions. after you create the instance of the pdo class/make the connection, you need to add -

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);