We help IT Professionals succeed at work.

How to insert Commitment dates for each unit

Black Sulfur
Black Sulfur asked
on
49 Views
Last Modified: 2017-04-24
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" }
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Author

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?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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!

Author

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

Author

Commented:
Hi Ray, did I leave out any info?

Author

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

This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
stale question

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions