Solved

PHP PDO Insert and returns

Posted on 2013-10-22
20
505 Views
Last Modified: 2013-10-23
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
Comment
Question by:rgranlund
  • 8
  • 8
  • 3
  • +1
20 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
@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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
@ChrisStanton, I tried it but it did not work.  I think I am missing something.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
No worries :)

Post your code as you have it and I'll take a look. Likely to be something simple.
0
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
			$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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
Array Arrayarray(2) { ["first_name"]=> array(1) { ["ticked"]=> string(7) "Chuckie" } ["last_name"]=> array(1) { ["ticked"]=> string(0) "" } }
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
@ 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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
@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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now