PDO Insert help

[mike16889]

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?

[fenway]

Not sure what you mean by doesn't work.

[mike16889]

it doesn't insert the entry to the database

[PFMaBiSmAd]

Each value you put into the query statement needs its own placeholder. So, you would need 55 of them. The syntax of the sql statement you put into the ->prepare(......) method is complete the way you would have written it without using a prepared statement, except it has a placeholder for each value and no single quotes around string values. You would also supply the $newValue array to the ->execute() method.

 

You need to always have error checking and error reporting/logging logic in your code to get your code to tell you when and why it is failing. Some error checking/reporting to tell you why your existing code is failing -

 

	$statement = $db->prepare('INSERT INTO job VALUES (?)');
	if(!$statement->execute(array($sqlString))){
		$err = $statement->errorInfo();
		echo "Query failed: {$err[2]}";
	}

[mike16889]

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

[PFMaBiSmAd]

Prepared statements are not just about preventing sql injection, they are also about handling data values in a way that is appropriate for each data type, so that you don't need to, for example, escape each piece of string data that might have special sql characters in the data.

[PFMaBiSmAd]

I just realized that you are deleting and then inserting a row with the same index. You should just use one REPLACE query to do both of those things at once.

[mike16889]

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?

[PFMaBiSmAd]

am i going to have to label all the fields in the SQL statment

 

^^^ If by that you mean, list all the fields in the INSERT query statement - INSERT INTO your_table (field list) VALUES (value list), you should already be doing that so that the corresponding data will ALWAYS get put into the correct database table field.

 

Having the list of fields in an array would also allow you to (eventually) validate each piece of submitted data and perform any unique conversions (your existing true/false data values and the start/end time data values don't match your database field definitions and require some extra processing to work in the query) by entering a list of 'rules' in the array entry for each field (that you eventually write code to make use of.)

 

Assuming you had an array of the field names - $fields = array('jobDetailID','JobNo', ..., ...);, you can produce the query and pass it your existing array $newValue of data as follows -

 

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

$field_list = implode('`,`',$fields);
$placeholders = implode(',',array_fill(0,count($fields),'?'));
$statement = $db->prepare("INSERT INTO job (`$field_list`) VALUES ($placeholders)");
if(!$statement->execute($newValue)){
	$err = $statement->errorInfo();
	echo "Query failed: {$err[2]}";
} else {
	echo "Inserted: {$statement->rowCount()} row.<br />";
}
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

[mike16889]

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] ));

[PFMaBiSmAd]

$newValue is already an array. Listing out all the elements of it inside another array is a waste of typing time.

[mike16889]

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

[PFMaBiSmAd]

Then there's the wasted processing time and wasted memory...