HELP update/edit db content with PDO

[chemicaluser]

Hi I am hoping someone can help me figure out why this script is not wokring … its been driving me crazy

This code is to update an existing record in the datbase.

When I run it I get the following error … i also echoed the SQL statement being passed (the column names are correct) - in this particular example I was trying to update only the 4 fields listed

Array
(
[0] =>
[1] =>
[2] =>
)

UPDATE investigations SET site = ?, revenue_potential = ?, revenue_billed = ?, revenue_claimed = ? WHERE investigation_id = ?

here is my code that is causing the problem …
I have also included th ecode that works (which only updates data from one array vs. the two that I need)
hope someone can point me in the rigth direction

<?php
//Connect to the database
$dbh = new PDO('mysql:host=localhost;dbname=testdb', 'root', '');
//Set the default fetch mode to be an associative array.
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,  PDO::FETCH_ASSOC);

//Define the fields
$fields = array(
	'site'=>'Site',
	'tour'=>'Tour',
	'customer'=>'Customer',
	'date_started'=>'Date Started',
	'date_closed'=>'Date Closed',
	'investigated_by'=>'Investigated By'
);

$dropfields = array(
	'revenue_potential'=>'RP',
	'revenue_billed'=>'RB',
	'revenue_claimed'=>'RC'
);



/* Using $_REQUEST as a shortcut - when we first access
the page, the contact_id will be in the URL/$_GET array.
When we submit the form it will then be in $_POST. */
if(isset($_REQUEST['investigation_id'])){
	$fields_str = '`investigation_id`, `'.implode(array_merge(array_keys($fields), array_keys($dropfields)), '`, `').'`';
	$sql = "SELECT {$fields_str} FROM `investigations` WHERE investigation_id = ?";
	$stmt = $dbh->prepare($sql);
	$stmt->execute(array($_REQUEST['investigation_id']));
	$values = $stmt->fetch();
	if(!$values){
		$error = 'Invalid ID provided';
	}
}else{
	$error = 'No ID provided';
}


//If the form has been submitted, validate it.
if(isset($_POST['submit'])){
	
	//We require the contact ID be submitted.
	if(isset($_POST['investigation_id'])){
		
		//Create an array to hold the values we want to update.
		$update_values = array();
		$update_values2 = array();

		//For each of the fields we want, check if the field was posted, and if so compare it to the previous value
		foreach($fields AS $field=>$label){
			if(isset($_POST[$field])){
				//trim the whitepace
				$value = trim($_POST[$field]);
				//If it's different than what we had stored before, update it.
				if($value != $values[$field]){
					//Add it to the array of values to update
					$update_values[$field] = $value;
					//Add the value to the values array, so it will show up in the form as well.
					$values[$field] = $value;
				}
			}
		}
		
		
		foreach($dropfields AS $dropfield=>$droplabel){
			if(isset($_POST[$dropfield])){
				//trim the whitepace
				$dropvalue = trim($_POST[$dropfield]);
				//If it's different than what we had stored before, update it.
				if($dropvalue != $dropvalues[$dropfield]){
					//Add it to the array of values to update
					$update_values2[$dropfield] = $dropvalue;
					//Add the value to the values array, so it will show up in the form as well.
					$dropvalues[$dropfield] = $dropvalue;
				}
			}
		}
		
		
		
		$errors = array();
		/*
		First and Last Names are required. strlen will return the string's length
		Since this time we are UPDATING, we only need to check if the strlen is > 0
		if the field has actually changed. Compare this code carefully to the add code,
		now we are checking if the field was sent AND doesn't have a length.
		*/
		if(isset($update_values['site']) && !strlen($update_values['site'])){
			$errors['site'] = 'Please Enter a Site';
		}
		if(isset($update_values['customer']) && !strlen($update_values['customer'])){
			$errors['customer'] = 'Please Enter a Customer';
		}
		
		//If there are not and there are fields to update, update the data.
		if(!count($errors) && (count($update_values) || count($update_values2))){
			$sql = "UPDATE `investigations` SET ";
			foreach(array_keys($update_values) AS $field){
				$sql .= "`{$field}` = ?, ";
			}
			foreach(array_keys($update_values2) AS $dropfield){
				$sql .= "`{$dropfield}` = ?, ";
			}
			//Trim the trailing comma.
			$sql = trim($sql, ', ');
			//Add the WHERE clause
			$sql .= ' WHERE `investigation_id` = ?';
			
			//Prepare the statement
			$stmt = $dbh->prepare($sql);
			
			/* The values to put in the prepared statement will be the update values, with the contact ID at the end.
			array_values() will pull just the array's values, without the string keys.
			The positional placeholder requires a numeric key. */
			$update_values = array_values($update_values);
			$update_values[] = $values['investigation_id'];
			
			$update_values2 = array_values($update_values2);
			$update_values2[] = $dropvalues['investigation_id'];
			
			$result = $stmt->execute($update_values, $update_values2);
		}
	}else{
		$error = 'No ID provided';
	}
}
?>


<style>
label{
	display: block;
	margin: 5px 0;
}

.error{
	font-weight: bold;
	color: #BB0000;
}
</style>

<?php

//If there was an error, display it. Otherwise display the form.
if(isset($error) && $error){
	echo $error;
}else{
	//If the form was submitted and an UPDATE was attempted, display a message.
	if(isset($result)){
		if($result){
			echo '<b>Successfully Updated!</b>';
		}else{
			echo '<b>Unable to Update</b>';
			print '<pre>'.print_r($stmt->errorInfo(), true);
			echo $sql;
		}
	}
	
	require_once('menu.php');
	?>
	
	<h1>Update Contact</h1>
	<form method="post" action="edit.php">
		<?php
		foreach($fields AS $field=>$label){
			//Print the form element for the field.
			echo "<label>{$label}:<br>";
			//If the field had an error, display it.
			if(isset($errors[$field])){
				echo ' <span class="error">'.$errors[$field].'</span><br>';
			}
			//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
			echo '<input type="text" name="'.$field.'"';
			if(isset($values[$field])){
				echo ' value="'.$values[$field].'"';
			}
			echo '/></label>';
		}
		
		
		
		foreach($dropfields AS $dropfield=>$droplabel){
			//Print the form element for the field.
			echo "<label>{$droplabel}:<br>";

			//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
			echo '<input type="text" name="'.$dropfield.'"';
			if(isset($dropvalues[$dropfield])){
				echo ' value="'.$dropvalues[$dropfield].'"';
			}
			echo '/></label>';
		}
		
		
		
		//Add the hidden input for the investigation id.
		echo '<input type="hidden" name="investigation_id" value="'.$values['investigation_id'].'" />';
		?>
		<input type="submit" name="submit" value="Edit" />
	</form>
<?php
}
?>

When I use the same code but only update data from one array it works no problem …
This is the code that works (again, not updateding all the fields I need - it does not include data from the $dropfields array… which is what I need)

<?php
//Connect to the database
$dbh = new PDO('mysql:host=localhost;dbname=testdb', 'root', '');
//Set the default fetch mode to be an associative array.
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,  PDO::FETCH_ASSOC);

//Define the fields for our CRUD application
$fields = array(
	'site'=>'Site',
	'tour'=>'Tour',
	'customer'=>'Customer',
	'date_started'=>'Date Started',
	'date_closed'=>'Date Closed',
	'investigated_by'=>'Investigated By'
);

$dropfields = array(
	'revenue_potential'=>'RP',
	'revenue_billed'=>'RB',
	'revenue_claimed'=>'RC'
);


/* Using $_REQUEST as a shortcut - when we first access
the page, the contact_id will be in the URL/$_GET array.
When we submit the form it will then be in $_POST. */
if(isset($_REQUEST['investigation_id'])){
	$fields_str = '`investigation_id`, `'.implode(array_keys($fields), '`, `').'`';
	$sql = "SELECT {$fields_str} FROM `investigations` WHERE investigation_id = ?";
	$stmt = $dbh->prepare($sql);
	$stmt->execute(array($_REQUEST['investigation_id']));
	$values = $stmt->fetch();
	if(!$values){
		$error = 'Invalid ID provided';
	}
}else{
	$error = 'No ID provided';
}


//If the form has been submitted, validate it.
if(isset($_POST['submit'])){
	
	//We require the contact ID be submitted.
	if(isset($_POST['investigation_id'])){
		
		//Create an array to hold the values we want to update.
		$update_values = array();
		
		//For each of the fields we want, check if the field was posted, and if so compare it to the previous value
		foreach($fields AS $field=>$label){
			if(isset($_POST[$field])){
				//trim the whitepace
				$value = trim($_POST[$field]);
				//If it's different than what we had stored before, update it.
				if($value != $values[$field]){
					//Add it to the array of values to update
					$update_values[$field] = $value;
					//Add the value to the values array, so it will show up in the form as well.
					$values[$field] = $value;
				}
			}
		}
		
		$errors = array();
		/*
		First and Last Names are required. strlen will return the string's length
		Since this time we are UPDATING, we only need to check if the strlen is > 0
		if the field has actually changed. Compare this code carefully to the add code,
		now we are checking if the field was sent AND doesn't have a length.
		*/
		if(isset($update_values['site']) && !strlen($update_values['site'])){
			$errors['site'] = 'Please Enter a Site';
		}
		if(isset($update_values['customer']) && !strlen($update_values['customer'])){
			$errors['customer'] = 'Please Enter a Customer';
		}
		
		//If there are not and there are fields to update, update the data.
		if(!count($errors) && count($update_values)){
			$sql = "UPDATE `investigations` SET ";
			foreach(array_keys($update_values) AS $field){
				$sql .= "`{$field}` = ?, ";
			}
			//Trim the trailing comma.
			$sql = trim($sql, ', ');
			//Add the WHERE clause
			$sql .= ' WHERE `investigation_id` = ?';
			
			//Prepare the statement
			$stmt = $dbh->prepare($sql);
			
			/* The values to put in the prepared statement will be the update values, with the contact ID at the end.
			array_values() will pull just the array's values, without the string keys.
			The positional placeholder requires a numeric key. */
			$update_values = array_values($update_values);
			$update_values[] = $values['investigation_id'];
			
			$result = $stmt->execute($update_values);
		}
	}else{
		$error = 'No ID provided';
	}
}
?>


<style>
label{
	display: block;
	margin: 5px 0;
}

.error{
	font-weight: bold;
	color: #BB0000;
}
</style>

<?php

//If there was an error, display it. Otherwise display the form.
if(isset($error) && $error){
	echo $error;
}else{
	//If the form was submitted and an UPDATE was attempted, display a message.
	if(isset($result)){
		if($result){
			echo '<b>Successfully Updated!</b>';
			echo $sql;
		}else{
			echo '<b>Unable to Update</b>';
			print '<pre>'.print_r($stmt->errorInfo(), true);
		}
	}
	
	require_once('menu.php');
	?>
	
	<h1>Update Contact</h1>
	<form method="post" action="edit.php">
		<?php
		foreach($fields AS $field=>$label){
			//Print the form element for the field.
			echo "<label>{$label}:<br>";
			//If the field had an error, display it.
			if(isset($errors[$field])){
				echo ' <span class="error">'.$errors[$field].'</span><br>';
			}
			//Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission.
			echo '<input type="text" name="'.$field.'"';
			if(isset($values[$field])){
				echo ' value="'.$values[$field].'"';
			}
			echo '/></label>';
		}
		//Add the hidden input for the contact id.
		echo '<input type="hidden" name="investigation_id" value="'.$values['investigation_id'].'" />';
		?>
		<input type="submit" name="submit" value="Edit" />
	</form>
<?php
}
?>

Any suggestions are greatly appritiated
Thanks

[fretburner]

Hi,

The problem is that you’re trying to pass two arrays into the execute method, which only takes one argument:

$result = $stmt->execute($update_values, $update_values2);

Merging the arrays first should do the trick:

$result = $stmt->execute( array_merge($update_values, $update_values2) );

[chemicaluser]

@fretburner

Hi,

The problem is that you’re trying to pass two arrays into the execute method, which only takes one argument:

$result = $stmt->execute($update_values, $update_values2);

Merging the arrays first should do the trick:

$result = $stmt->execute( array_merge($update_values, $update_values2) );

Hi,
thanks for taking the time to review the code and respond …

Now I keep geting SQLSTATE error … again im just echoing out the SQL statement being run. I can confirm the column names are correct

Array
(
[0] => HY093
[1] =>
[2] =>
)
UPDATE investigations SET tour = ?, revenue_potential = ? WHERE investigation_id = ?

any thoughts?

[fretburner]

Just looking through your code some more, I see this line:

if($dropvalue != $dropvalues[$dropfield]){ 

but the array dropvalues isn’t defined anywhere in the code before that.

[chemicaluser]

Hi, thank you very much … got it working

$dropvalues should have been [COLOR=#0000bb][FONT=Courier New]$values

[/FONT][/COLOR]… however ever after I changed this I was still getting the same error

Array
(
[0] => HY093
[1] =>
[2] =>
)

so I ended up commenting ( // ) out this line and now everything works good

$update_values[] = $values['investigation_id']; 

Thanks again for your help