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).

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.

I just used NotePad++ find and replace on the SQL dump for column names and NotePad ++ and open office calc for the array only took about 2 mins

thanx for the help, i ended up just doing this: $statement = $db->prepare("INSERT INTO job (jobDetailID,JobNo,Sequence,Initias,jobDate,ClientName,jobLocation,jobAddress,jobSuburb,jobContact,jobPhone,jobMobile,jobDescription,trafficyn,traffic,worklayoutyn,worklayout,machyn,mach,fireyn,fire,weldyn,weld,heightsyn,heights,handelingyn,hndleing,elecyn,elec,plantyn,plant,weatheryn,weather,noiseyn,noise,lightingyn,lighting,scafoldyn,saftyNetyn,handrailyn,harnessyn,PermitNo,jobComplete,startTime,endTime,travel,onSite,TotalTime,overTime,worksDescription, SequenceClose,taskOrder,clientOrderNo,appointmentTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); $statement->execute(array($newValue[0],$newValue[1], $newValue[2], $newValue[3], $newValue[4], $newValue[5], $newValue[6], $newValue[7], $newValue[8], $newValue[9], $newValue[10], $newValue[11], $newValue[12], $newValue[13], $newValue[14], $newValue[15], $newValue[16], $newValue[17], $newValue[18], $newValue[19], $newValue[20], $newValue[21], $newValue[22], $newValue[23], $newValue[24], $newValue[25], $newValue[26], $newValue[27], $newValue[28], $newValue[29], $newValue[30], $newValue[31], $newValue[32], $newValue[33], $newValue[34], $newValue[35], $newValue[36], $newValue[37], $newValue[38], $newValue[39], $newValue[40], $newValue[41], $newValue[42], $newValue[43], $newValue[44], $newValue[45], $newValue[46], $newValue[47], $newValue[48], $newValue[49], $newValue[50], $newValue[51], $newValue[52], $newValue[53] ));

Thanx for your help so far. whats the best way for me to pull this off? am i going to have to label all the fields in the SQL statment and then take each peace of data and insert it individually or can i somehow just pass the Array to it with the 1st field being at [1] and the second at [2] and so on?

i don't rly need to worry about SQL injection, this is not, nor will ever be publicly accessible. so could i do something like this? $statement->execute(array('INSERT INTO job VALUES '.$sqlString))

it doesn't insert the entry to the database

ok, bascly im building an app for my work for android that allows the user to download info about a job they have to go to, fill out details and submit it. but im having trouble getting the updated data back in the database. here is my table CREATE TABLE IF NOT EXISTS `job` ( `jobDetailID` int(11) NOT NULL, `JobNo` int(11) NOT NULL, `Sequence` int(11) NOT NULL, `Initias` varchar(10) NOT NULL, `jobDate` date NOT NULL, `ClientName` varchar(255) NOT NULL, `jobLocation` varchar(255) NOT NULL, `jobAddress` varchar(255) NOT NULL, `jobSuburb` varchar(255) NOT NULL, `jobContact` varchar(255) NOT NULL, `jobPhone` varchar(10) NOT NULL, `jobMobile` varchar(10) NOT NULL, `jobDescription` varchar(255) NOT NULL, `trafficyn` tinyint(1) NOT NULL, `traffic` varchar(255) NOT NULL, `worklayoutyn` tinyint(1) NOT NULL, `worklayout` varchar(255) NOT NULL, `machyn` tinyint(1) NOT NULL, `mach` varchar(255) NOT NULL, `fireyn` tinyint(1) NOT NULL, `fire` varchar(255) NOT NULL, `weldyn` tinyint(1) NOT NULL, `weld` varchar(255) NOT NULL, `heightsyn` tinyint(1) NOT NULL, `heights` varchar(255) NOT NULL, `handelingyn` tinyint(1) NOT NULL, `hndleing` varchar(255) NOT NULL, `elecyn` tinyint(1) NOT NULL, `elec` varchar(255) NOT NULL, `plantyn` tinyint(1) NOT NULL, `plant` varchar(255) NOT NULL, `weatheryn` tinyint(1) NOT NULL, `weather` varchar(255) NOT NULL, `noiseyn` tinyint(1) NOT NULL, `noise` varchar(255) NOT NULL, `lightingyn` tinyint(1) NOT NULL, `lighting` varchar(255) NOT NULL, `scafoldyn` tinyint(1) NOT NULL, `saftyNetyn` tinyint(1) NOT NULL, `handrailyn` tinyint(1) NOT NULL, `harnessyn` tinyint(1) NOT NULL, `PermitNo` int(11) NOT NULL, `jobComplete` tinyint(1) NOT NULL, `startTime` decimal(10,0) NOT NULL, `endTime` decimal(10,0) NOT NULL, `travel` decimal(10,0) NOT NULL, `onSite` decimal(10,0) NOT NULL, `TotalTime` decimal(10,0) NOT NULL, `overTime` decimal(10,0) NOT NULL, `worksDescription` longtext NOT NULL, `SequenceClose` tinyint(1) NOT NULL, `taskOrder` int(11) NOT NULL, `clientOrderNo` varchar(255) NOT NULL, `appointmentTime` varchar(255) NOT NULL, PRIMARY KEY (`jobDetailID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; and here is my PHP <?php //Temp String's $tag = 112234; $value = <<<EOD [\"112234\",\"104104\",\"0\",\"MR\",\"2012-07-09\",\"Service Stream\",\"some building\",\"1 Luck St\",\"Mowbray\",\"me\",\"0363262617\",\"0400557999\",\"Comma, FullStop. Bakcslash\\/ colan: semicolan; dblQoute\\\" Qoute\' openBracket( closeBracket) star* percent% d\",true,\"\",true,\"\",true,\"\",true,\"\",false,\"\",false,\"\",false,\"\",true,\"\",true,\"\",true,\"\",true,\"\",false,\"\",false,true,true,false,\"123456\",true,\"10\",\"11:30\",\".5\",\"1\",\"1.5\",\"0\",\"Works i completed\",true,\"0\",\"1612123456\",\"8am\"] EOD; //$tag = $_POST['tag']; //$value = $_POST['value']; $value = str_replace('\"', '&&TEMPSTRING&&', $value); $value = str_replace('"', '\"', $value); $value = str_replace('&&TEMPSTRING&&', '"', $value); $value = substr($value, 2, -1); $newValue = explode(",", $value); $host = 'localhost'; $port = 3306; $database = 'JobDispatch'; $username = 'root'; $password = ''; print_r($newValue); echo '<br />'; $newValue[1] = intval($newValue[1]); $newValue[2] = intval($newValue[2]); $newValue[3] = intval($newValue[3]); $newValue[42] = intval($newValue[42]); $newValue[52] = intval($newValue[52]); $sqlString = implode(", ", $newValue); try { $dsn = "mysql:host=$host;port=$port;dbname=$database"; $db = new PDO($dsn, $username, $password); $statement = $db->prepare("DELETE FROM job WHERE jobDetailID = ?"); $statement->execute(array($tag)); $statement = $db->prepare('INSERT INTO job VALUES (?)'); $statement->execute(array($sqlString)); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); }; echo $sqlString; ?> eventual it will be called by the app but for now i just have set the string the app submits saved to a veriable. i then manipulate the string a bit so if fits the database a bit better. but for some reason when i try to insert the data it just doesn't work and i cant figure out why. any ideas?