mike16889 Posted February 10, 2014 Share Posted February 10, 2014 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. Quote Link to comment Share on other sites More sharing options...
fastsol Posted February 10, 2014 Share Posted February 10, 2014 You have an extra ` here `pass` = :pass` WHERE in the update query string. Quote Link to comment Share on other sites More sharing options...
mike16889 Posted February 10, 2014 Author Share Posted February 10, 2014 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). Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 10, 2014 Share Posted February 10, 2014 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); Quote Link to comment 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.