Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

PHP PDO Insert and returns

I have the following PHP / PDO code that inserts an array of data into a DB Table.

I am trying to understand how to ECHO what files have been updated.  At the end I printf if records have been updated.  What I would like to do, in addition, is to give a list of First Names and Last Names of each Employee ID that has been updated.

An suggestions?

//  START THE INSERT INTO SCHEDULE SETUP
			$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date)
					VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");
	//  END THE INSERT INTO SCHEDULE SETUP
			
		//start a record counter so we can track how many records are updated
			$records = 0;

	//loop through each set of POSTed data
	foreach ($_POST['driver'] as $eid => $info) {

		//was the tick box ticked
		if (isset($info['ticked'])) {
	
		//get the info to insert into the database
				$data = array(
					'start_date' => $info['start_date'],
					'end_date' => $info['end_date'],
					'employee_id' => $eid,
					'first_name' => $info['first_name'],
					'last_name' => $info['last_name']
				);
		
		//run the query
				$stmt->execute($data);
			
		//and increase the record counter
				$records++;
				}  //  END IF
			}  //  END FOREACH

		//give some feedback
			if ($records) {
				printf("<div class='right'><fieldset>%d record(s) were updated</fieldset></div>", $records);
			} else {
				echo "<div class='right'><fieldset><strong>No records were updated!</strong></fieldset></div>";
			}

	}
	 catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
	}
	

Open in new window

0
rgranlund
Asked:
rgranlund
  • 8
  • 8
  • 3
  • +1
1 Solution
 
Chris StanyonCommented:
You could write the name to an array when you call the query and then loop through that at the end. At the start of your script add:

$updated = array();

Open in new window

And then after the execute statement add this:

$updated[] = array(
   'first_name' => $info['first_name'],
   'last_name' => $info['last_name']
);

Open in new window

The when you output the number of records, something like this:

foreach ($updated as $driver) {
   echo $driver['first_name'] . " " . $driver['last_name'];
};

Open in new window

0
 
rgranlundAuthor Commented:
@ChrisStanton

I'm not sure where to place it?

		if (isset($info['ticked'])) {
	
		//get the info to insert into the database
				$data = array(
					'start_date' => $info['start_date'],
					'end_date' => $info['end_date'],
					'employee_id' => $eid,
					'first_name' => $info['first_name'],
					'last_name' => $info['last_name']
				);
		
		
		//run the query
	$updated = array();
				$stmt->execute($data);
				
		foreach ($updated as $driver) {
   echo $driver['first_name'] . " " . $driver['last_name'];
};
			
		//and increase the record counter
				$records++;
				}  //  END IF
			}  //  END FOREACH

Open in new window

0
 
Chris StanyonCommented:
Right. You need to declare the $updated variable before the loop, populate it during the loop, and echo it back out after the loop. Something like this:

<?php 
// START THE INSERT INTO SCHEDULE SETUP
$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date) VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");

//start a record counter so we can track how many records are updated
$records = 0;

//create a variable to hold the updated info
$updated = array();

//loop through each set of POSTed data
foreach ($_POST['driver'] as $eid => $info) {

	//was the tick box ticked
	if (isset($info['ticked'])) {
		//get the info to insert into the database
		$data = array(
			'start_date' => $info['start_date'],
			'end_date' => $info['end_date'],
			'employee_id' => $eid,
			'first_name' => $info['first_name'],
			'last_name' => $info['last_name']
		);
		
		//run the query
		$stmt->execute($data);
			
		//increase the record counter
		$records++;
		
		//add the info to the 'updated' array
		$updated[] = array(
			'first_name' => $info['first_name'],
			'last_name' => $info['last_name']
		);
	};
};

//give some feedback
if ($records) {
	printf("<div class='right'><fieldset>%d record(s) were updated</fieldset></div>", $records);
	
	//echo out some info about the updated records
	foreach ($updated as $driver) {
		echo $driver['first_name'] . ' ' . $updated['last_name'];
	};
	
} else {
	echo "<div class='right'><fieldset><strong>No records were updated!</strong></fieldset></div>";
}
?>

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Julian HansenCommented:
Why can't you just output the values as you loop through your $_POST array?

Each iteration is running an insert using the data from POST so just echo the data out.

If you need to do it at the end then loop through the $_POST again - no need to setup a different array - the data you need is already in the $_POST array ...
0
 
Ray PaseurCommented:
When you have a catch block, you probably want to have a try block, too.  This tells how it's done.
http://php.net/manual/en/language.exceptions.php

This is untested code, but it's probably about right in principle.

//  START THE INSERT INTO SCHEDULE SETUP
$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date)
VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");
//  END THE INSERT INTO SCHEDULE SETUP
		
//start a record counter so we can track how many records are updated
$records = 0;

//loop through each set of POSTed data
$updates = array();
foreach ($_POST['driver'] as $eid => $info) 
{
	//was the tick box ticked
	if (isset($info['ticked'])) {
	
		//get the info to insert into the database
		$data = array(
		'start_date' => $info['start_date'],
		'end_date' => $info['end_date'],
		'employee_id' => $eid,
		'first_name' => $info['first_name'],
		'last_name' => $info['last_name']
		);
		
		//run the query
		try
		{
	 		$stmt->execute($data);
	 	}
	 	catch(PDOException $e) 
	 	{
			var_dump($e);
		}

		// RECORD THE UPDATES
		$updates[] = $info['first_name'] . ' ' . $info['last_name'];
		
		//and increase the record counter
		$records++;
	}  //  END IF
}  //  END FOREACH

//give some feedback
if ($records) {
	printf("<div class='right'><fieldset>%d record(s) were updated</fieldset></div>", $records);
	print_r($updates);
} else {
	echo "<div class='right'><fieldset><strong>No records were updated!</strong></fieldset></div>";
}

Open in new window

0
 
rgranlundAuthor Commented:
I get the following warning:
Array to string conversion in /add_schedule.php on line 28

//  START THE INSERT INTO SCHEDULE SETUP
$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date)
VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");
//  END THE INSERT INTO SCHEDULE SETUP
		
//start a record counter so we can track how many records are updated
$records = 0;

//loop through each set of POSTed data
$updates = array();
foreach ($_POST['driver'] as $eid => $info) 
{
	//was the tick box ticked
	if (isset($info['ticked'])) {
	
		//get the info to insert into the database
		$data = array(
		'start_date' => $info['start_date'],
		'end_date' => $info['end_date'],
		'employee_id' => $eid,
		'first_name' => $info['first_name'],
		'last_name' => $info['last_name']
		);
		
		//run the query
		try
		{
	 		$stmt->execute($data);
	 	}
	 	catch(PDOException $e) 
	 	{
			var_dump($e);
		}

		// RECORD THE UPDATES
		$updates[] = $info['first_name'] . ' ' . $info['last_name'];
		
		//and increase the record counter
		$records++;
	}  //  END IF
}  //  END FOREACH

//give some feedback
if ($records) {
	printf("<div class='right'><fieldset>%d record(s) were updated</fieldset></div>", $records);
	print_r($updates);
} else {
	echo "<div class='right'><fieldset><strong>No records were updated!</strong></fieldset></div>";
}                                            

Open in new window

0
 
Chris StanyonCommented:
The line 28 warning is not from line 28 of the code you posted. Post Line 28 from your full code.

Any reason why you've not tried the code I originally posted? It was simple and worked fine!
0
 
rgranlundAuthor Commented:
@ChrisStanton, I tried it but it did not work.  I think I am missing something.
0
 
Chris StanyonCommented:
Just spotted a slight type in my code. The output should be this:

foreach ($updated as $driver) {
	echo $driver['first_name'] . ' ' . $driver['last_name'];
}

Open in new window

Other than that, it'll work fine.

FYI - When reporting that something doesn't work it helps us to know precisely what you mean - 'it doesn't work' is not an error message or even remotely helpful :)
0
 
rgranlundAuthor Commented:
Sorry, I did not mean to be vague.  I wrote that reply in haste.

What ends up happening is that I get for the "Foreach" Array Array.

This is where I am stuck.

Newbie.....
0
 
Chris StanyonCommented:
No worries :)

Post your code as you have it and I'll take a look. Likely to be something simple.
0
 
rgranlundAuthor Commented:
			$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date) VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");

//start a record counter so we can track how many records are updated
$records = 0;

//create a variable to hold the updated info
$updated = array();

//loop through each set of POSTed data
foreach ($_POST['driver'] as $eid => $info) {

	//was the tick box ticked
	if (isset($info['ticked'])) {
		//get the info to insert into the database
		$data = array(
			'start_date' => $info['start_date'],
			'end_date' => $info['end_date'],
			'employee_id' => $eid,
			'first_name' => $info['first_name'],
			'last_name' => $info['last_name']
		);
		
		//run the query
		$stmt->execute($data);
			
		//increase the record counter
		$records++;
		
		//add the info to the 'updated' array
		$updated[] = array(
			'first_name' => $info['first_name'],
			'last_name' => $info['last_name']
		);
	};
};

//give some feedback
echo '<div><fieldset>';
if ($records) {
	printf("%d record(s) were updated", $records);
	
	//echo out some info about the updated records
	foreach ($updated as $driver) {
	echo '<br />'.$driver['first_name'] . ' ' . $driver['last_name'];
}
	
} else {
	echo "<strong>No records were updated!</strong>";
}
echo '</fieldset></div>';

Open in new window

0
 
Chris StanyonCommented:
Hmmm. Can't see anything wrong with the code. Didn't really fully understand your previous message about getting the "Foreach" Array Array.  Instead of echoing out the $updated array, try using var_dump to make sure you have the correct info:

foreach ($updated as $driver) {
	var_dump($driver);
}

Open in new window

0
 
rgranlundAuthor Commented:
Array Arrayarray(2) { ["first_name"]=> array(1) { ["ticked"]=> string(7) "Chuckie" } ["last_name"]=> array(1) { ["ticked"]=> string(0) "" } }
0
 
Ray PaseurCommented:
Sounds like the HTML form may be out of whack.  Maybe you could use var_dump($_POST) and show us what you've got there.

Also, it may not matter, but it makes me itch when I see the arguments in the prepare() out of sequence with the contents of $data.  I've never done that so I don't know what would happen.
0
 
Chris StanyonCommented:
Agree with Ray. You could only end up with that output if the POST array is somehow wrong. inspect your POST array before the loop, and let us see what that looks like:

var_dump($_POST);
foreach ($_POST['driver'] as $eid => $info) {

Open in new window

We may also need to see the code that generates your form
0
 
rgranlundAuthor Commented:
@ Chris and Ray;

Thank you for your help so far.  I have it almost cleaned up and working right.  However, after closer study, I realize that there is something afoot that I cannot seem to find and/or understand.

The two main issues are as follows;
1. when I run the script it places the correct employee ID in the DB but for the first_name and last_name it inserts "Array"

2. Neither the start_date or the end_date are inserted only 0000-00-00       

This is the Form Code:
$stmt = $pdo->prepare("SELECT *
							FROM assets
							WHERE NOT asset_id = '$aid' ORDER BY asset_type DESC");
	//  PREPARE PDO QUERY
					try {
    					$stmt->execute();
	//  RUN A FOREACH FOR EACH EMPLOYEE ID AGAIST SCHEDULE
						if ($stmt) {
	//loop through the results
							while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
									 
								echo '<tr>
									<td width="25"><input type="checkbox" name="driver['.$row->asset_id.'][ticked]"/>
									<input type="hidden" name="asset['.$row->asset_id.'][asset_name][ticked]" value="'.$row->asset_name.'" />
									<input type="hidden" name="asset['.$row->asset_id.'][asset_type][ticked]" value="'.$row->asset_type.'" />
									<input type="hidden" name="asset['.$row->asset_id.'][start_date][ticked]" value="'.$start_date.'" />
									<input type="hidden" name="asset['.$row->asset_id.'][end_date][ticked]" value="'.$end_date.'" />
									</td>
									<td width="100">'.$row->asset_name. ' ' . $row->asset_type.'</td>';
								echo '</tr>';
							
							}  //  END MAIN WHILE
			 			}  //  END MAIN IF
			 
			 		}  //  END TRY
					catch(PDOException $exca) {
   					var_dump($exca);
    				trigger_error($exca->getMessage(), E_USER_ERROR);
					}

Open in new window


This is the Insert code:
$stmt = $pdo->prepare("INSERT INTO schedule (employee_id, first_name, last_name, start_date, end_date) VALUES (:employee_id, :first_name, :last_name, :start_date, :end_date)");

//start a record counter so we can track how many records are updated
$records = 0;


//create a variable to hold the updated info



//loop through each set of POSTed data
foreach ($_POST['driver'] as $eid => $info) {

	//was the tick box ticked
	if (isset($info['ticked'])) {
		//get the info to insert into the database
		$data = array(
			'employee_id' => $eid,
			'first_name' => $info['first_name'],
			'last_name' => $info['last_name'],
			'start_date' => $info['start_date'],
			'end_date' => $info['end_date']
		);
		
		//run the query
		$stmt->execute($data);
		echo '<pre>';
var_dump($stmt);
echo '<br /><br />';
			
		//increase the record counter
		$records++;
		
		//add the info to the 'updated' array

	};
};

Open in new window

0
 
Ray PaseurCommented:
When PHP casts an array to a string, it creates the word Array.
http://php.net/manual/en/language.types.type-juggling.php
http://php.net/manual/en/language.types.string.php#language.types.string.casting

You might want to use var_dump($_POST) to see what the form script is creating.  I think the issue is a mismatch in the form logic and the action script logic.
0
 
Chris StanyonCommented:
The code to create your form and the code to run the inserts are out of sync. Your INSERT code is expecting form inputs with names of first_name, last_name, start_date, end_date and ticked, but you seem to be trying to pass asset_name, asset_type, start_date, end_date, and ticked. The INSERT script also expects the array to be called $_POST['driver'] but you are calling it $_POST['asset']. I'm not sure which is correct (the form script or the insert script) but they need to match.

If your INSERT code is correct, then you need to create the form to match. Something like this:

while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
	echo '<tr>
		<td width="25"><input type="checkbox" name="driver['.$row->asset_id.'][ticked]"/>
		<input type="hidden" name="driver['.$row->asset_id.'][first_name]" value="'.$row->asset_name.'" />
		<input type="hidden" name="driver['.$row->asset_id.'][last_name]" value="'.$row->asset_type.'" />
		<input type="hidden" name="driver['.$row->asset_id.'][start_date]" value="'.$start_date.'" />
		<input type="hidden" name="driver['.$row->asset_id.'][end_date]" value="'.$end_date.'" />
		</td>
		<td width="100">'.$row->asset_name. ' ' . $row->asset_type.'</td>';
	echo '</tr>';
}

Open in new window

That will create a POST array with elements called 'driver' with properties for ticked, first_name, last_name, start_date and last_date. Obviously you'll need to put in the correct values from your database (should last_name really be populated by asset_type). This also assumes that asset_id and employee_id are the same thing.

Your code is too out of sync to really make sense, so maybe you need to explain in more detail exactly what you are pulling from the database to create the form, and exactly what you expect to be pushed back to the database in the INSERT queries
0
 
rgranlundAuthor Commented:
@chris;

OMG I sent you the WRONG code!   That was from a scratch file I had going.  I'm so sorry to waste your time like that.

However, your reply did cause me to look closer at something.  I had [ticked] after each input.  Once I removed that.  Evert hing worked 100%

Thank you for your patience and help  with this matter.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 8
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now