How to insert Commitment dates for each unit

I had this question after viewing Adding additional variables to key => value association.

Hopefully this will be the last piece of the puzzle. I have text fields for each unit where a commitment date must be entered. I am using a foreach loop but when I var_dump it I can see way too many units.

Here is my html table. I am storing the unit key in a hidden field

<tr>
<td>{$thing}</td>
<td>{$row_data}</td>
<td>{$out_xwhen[$thing]}</td>
<td><input type="text" class="form-control" name="commit_date[]" placeholder="Commitment date" id="date"><input type="hidden" name="u_key[]" value="{$thing}"></td>
</tr>

Open in new window


The UPDATE query :

foreach($_POST['commit_date'] as $commitdate) {
$stmt = $link->prepare("UPDATE `units` SET `commit_date` = ? WHERE `u_key` = ?");
$stmt->bind_param("si", $commitdate, $_POST['u_key']);
$stmt->execute();
$stmt->close();

Open in new window


When testing, if I var_dump the commitment date, I get three dates for 3 text fields and the output matches what I inputted. The units however looked like this.

array(3) { [0]=> string(6) "905467" [1]=> string(6) "675483" [2]=> string(6) "501786" } array(3) { [0]=> string(6) "905467" [1]=> string(6) "675483" [2]=> string(6) "501786" } array(3) { [0]=> string(6) "905467" [1]=> string(6) "675483" [2]=> string(6) "501786" }
LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
text fields for each unit where a commitment date...
If it's a date, you probably want to use a DATE field for that column.

To go any further with this we should see the CREATE TABLE statements and the queries you're using.  We also need to see the fully resolved HTML that is in play.
Black SulfurAuthor Commented:
Thanks, Ray. In the database I am using a DATE field for that column, but a text input in the HTML with a date picker. I will post all the required code when I get back onto my development pc.

Regarding the created table statements, I create all my tables and columns in phpMyadmin. Would I manually need to type out the CREATE TABLE statements for you or is there a way to generate them from already created tables in phpMyadmin?
Ray PaseurCommented:
You can generate them with SHOW CREATE TABLE - very handy!

You might want to learn about the "Laravel way" of creating database migrations and seeding.  In practice, it stumbles over Git branches a little bit, but it gives you a clear path from the "big bang" of application origin to a current state for your database.  Also very handy!
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Learn More!
Black SulfurAuthor Commented:
Aha, nice one! Okay, so I hope this is enough info. Please let me know if not. 

CREATE TABLE `units` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `u_key` int(11) NOT NULL,
 `entry_date` date NOT NULL,
 `commit_date` date NOT NULL,
 `complete_date` date NOT NULL,
 PRIMARY KEY (`id`)

Open in new window


CREATE TABLE `defects` (
 `d_key` int(11) NOT NULL AUTO_INCREMENT,
 `non_green_desc` text NOT NULL,
 PRIMARY KEY (`d_key`)

Open in new window


CREATE TABLE `units_and_defects` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `u_key` int(11) NOT NULL,
 `d_key` int(11) NOT NULL,
 PRIMARY KEY (`id`)

Open in new window


here is the php and query that fetches the data:

if (!($stmt = $link->prepare("SELECT units.u_key AS u, non_green_desc, entry_date FROM units, defects, units_and_defects WHERE defects.d_key = units_and_defects.d_key AND units.u_key = units_and_defects.u_key ORDER BY u DESC"))) echo 'Prepare Error: (' . $link->errno . ') ' . $link->error;
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;

if ($numRows > 0) {
	$out_key = [];
	$out_xwhen = [];
	$old = FALSE;
	while ($row = $result->fetch_object()) {
		if ($row->u != $old) {
			$old = $row->u;
			$out_key[$row->u] = $row->non_green_desc;
			$out_xwhen[$row->u] = $row->entry_date;
		}
		else {
			$out_key[$row->u].= ', ' . $row->non_green_desc;
		}
	}

	foreach($out_key as $thing => $row_data) {
		$show_repair = <<<REPAIRUNITS
				
				<tr>
					<td>{$thing}</td>
					<td>{$row_data}</td>
					<td>{$out_xwhen[$thing]}</td>
					<td><input type="text" class="form-control" name="commit_date[]" placeholder="Commitment date" id="date"><input type="hidden" name="u_key[]" value="{$thing}"></td>
				</tr>
				
				
				
REPAIRUNITS;
		echo $show_repair;

Open in new window


The code to insert the commitment dates into the units table:

foreach($_POST['commit_date'] as $commitdate) {
$stmt = $link->prepare("UPDATE `repair_tanks` SET `commit_date` = ? WHERE `u_key` = ?");
$stmt->bind_param("si", $commitdate, $_POST['u_key']);
$stmt->execute();
$stmt->close();

Open in new window

Black SulfurAuthor Commented:
Hi Ray, did I leave out any info?
Black SulfurAuthor Commented:
I tried adding a second foreach but that just takes the input from the last textfield and inserts the same data for every row. 

foreach($_POST['commit_date'] as $commitdate) {
foreach($_POST['u_key'] as $unit) {
$stmt = $link->prepare("UPDATE `repair_tanks` SET `commit_date` = ? WHERE `u_key` = ?");
$stmt->bind_param("si", $commitdate, $unit);
$stmt->execute();
$stmt->close();

Open in new window

Black SulfurAuthor Commented:
I seem to have been forsaken on this particular question but I got it to work using a FOR loop instead of FOREACH. Hoorah!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
stale question
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.