[SOLVED] First PHP Project. Submittal of multiple arrays

[andrew.sawyer]

All,

 

This is the first time I've used PHP and I've been stuck on this one issue for a week or so now, so I thought it was about time to ask for some help.

 

I have a page that shows a list of items in an MS SQL table with a 'status' dropdown box next to each with the current status per the database selected.  The user then changes the status of the items that they wish and submit.

 

When submitted, the database is updated with the new status for the correct item and also updates a field called 'ModifiedDate' for that item.

 

My problem is as follows:

 

Regardless of whether one status is changed or they are all changed, the ModifiedDate of all will be updated.  I want to get around this by using a checkbox so that you can select which you wish to update along with the status dropdown.  Review the below logic:

 

You check item one and change status from 'on hold' to 'ready' and submit.  This will update item one.

 

You don't check item one and change status from 'on hold' to 'ready' and submit.  Nothing will change as the checkbox was not checked.

 

You check item one and leave the status as 'on hold'.  The status will remain the same however the ModifiedDate will update.

 

My problem is that I don't know how to do this.  I've hit a brick wall.

 

My code extract is as follows:

 

First page

 

$count = 0;
		foreach ($ropenitems as $row)
		{
			echo "
			<tr>
				<td><input type='hidden' name='id[]' value='". $row['id'] ."'>
				<input type='checkbox' name='update[]' value='" . $count . "' /></td>
				<td>" . $row['ReceivedDate'] . "</td>
				<td>
				<select name='status[]' id='status'>";
					try
					{
						$currentstatus = $row['StatusDesc'];
						$result = DB::getInstance()->query("SELECT id, StatusDesc FROM dbo.tStatus");
						foreach($result as $row)
						{
							echo '	<option value="'.$row['id'].'"';
							if($row['StatusDesc']==$currentstatus)
							{
								echo ' selected';
							}
							echo '>'. $row['StatusDesc'] . '</option>'."\n";
						}
					}
					catch(PDOException $e)
					{
						echo $e->getMessage();
					}
				echo "
				</select></td>
			</tr>";
			$count++;

 

Update page:

 

try
    {
	$id = $_POST['id'] ;
	$status = $_POST['status'];
	$update = $_POST['update'];
	$modifieddate = date("m/d/y");
		for($i=0; $i < count($id); $i++) 
		{
			$echangestatus = DB::getInstance()->exec("
				UPDATE 
					tInvoices
				SET  
					Status='$status[$i]', 
					LastModified='$ruserid',
					ModifiedDate='$modifieddate'
				WHERE 
					id='$id[$i]'
			");
		}
	echo "The requested invoices' status have been updated.";
}
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }

 

I really hope that makes sense.  I've been playing around with the code a fair bit trying different things, but nothing seems to work.  Essentially, I want some way of only running the update query for the records that have a checkbox ticked.

 

If anyone out there is able to help, it would be very much appreciated.

 

Andy

[andrew.sawyer]

I continued on this and I found my solution.  For anyone who has the same problem, or anyone who didn't understand my original query, below is the full code for both pages:

 

First Page:

<?php
include("header.php");
$pagename = $_SERVER["PHP_SELF"];
if ($rgroupmd5!="f33080ea7dbf7554d694a5adbd0d3855" and $rgroupmd5!="e3afed0047b08059d0fada10f400c1e6" and $rgroupmd5!="20b69ba10e54e418278485c57bd37aa4" and $rgroupmd5!="a4e208ad36a52f166d367fc411f3fac5" and $rgroupmd5!="16c590c637ba7c30891a9ad59d481ab1")
echo "Not a valid user";
else
{
try
    {
$qopenitems = DB::getInstance()->query("
		SELECT
			i.id,
			convert(varchar,o.OwnershipDate,106) AS 'OwnershipDate', 
			o.Comment,  
			i.Supplier, 
			i.InvoiceNumber, 
			convert(varchar,i.InvoiceDate,106) AS 'InvoiceDate',
			round(i.InvoiceAmount,2) AS 'InvoiceAmount', 
			i.Currency,
			i.PONumber, 
			i.Office, 
			CASE i.Stream
				WHEN 1 THEN 'Corporate'
				WHEN 2 THEN 'Upstream'
				WHEN 3 THEN 'Midstream'
				When 4 THEN 'Downstream'
				ELSE 'Liquifaction'
			END AS 'Stream', 
			s.StatusDesc, 
			convert(varchar,i.ReceivedDate,106) AS 'ReceivedDate'
		FROM
			tOwnership AS o
			LEFT OUTER JOIN
			tInvoices AS i 
			ON 
				o.Invoice = i.id
			LEFT OUTER JOIN
			tStatus AS s
			ON
				i.Status = s.id
		WHERE
			(o.ForwardedTo IS NULL) AND
			(i.Batch IS NULL) AND
			(o.Owner = '$ruserid')
		ORDER BY
			o.OwnershipDate
		");
	$ropenitems = $qopenitems->fetchAll();
echo "
<form action='changestatus1.php' method='post'>
<table class='gridtable'>
		<tr>
			<th>Update</th>
			<th>Ownership Date</th>
			<th>Supplier</th>
			<th>Invoice Number</th>
			<th>Invoice Date</th>
			<th>Invoice Amount</th>
			<th>Currency</th>
			<th>PO Number</th>
			<th>Office</th>
			<th>Stream</th>
			<th>Received Into Office</th>
			<th>Status</th>
		</tr>";
		$update=0;
		foreach ($ropenitems as $row)
		{
			echo "
			<tr>
				<td><input type='hidden' name='id[]' value='".$row['id']."' />
				<input type='checkbox' name='update[]' value='".$update."' /></td>
				<td>" . $row['OwnershipDate'] ."</td>
				<td>" . $row['Supplier'] ."</td>
				<td>" . $row['InvoiceNumber'] . "</td>
				<td>" . $row['InvoiceDate'] . "</td>
				<td>" . number_format($row['InvoiceAmount'],2) . "</td>
				<td>" . $row['Currency'] . "</td>
				<td>" . $row['PONumber'] . "</td>
				<td>" . $row['Office'] . "</td>
				<td>" . $row['Stream'] . "</td>
				<td>" . $row['ReceivedDate'] . "</td>
				<td><select name='status[]' id='status'>
";
					try
					{
						$currentstatus = $row['StatusDesc'];
						$result = DB::getInstance()->query("SELECT id, StatusDesc FROM dbo.tStatus");
						foreach($result as $row)
						{
							echo '						<option value="'.$row['id'].'"';
							if($row['StatusDesc']==$currentstatus)
							{
								echo ' selected';
							}
							echo '>'. $row['StatusDesc'] . '</option>'."\n";
						}
					}
					catch(PDOException $e)
					{
						echo $e->getMessage();
					}
				echo "
				</select></td>
			</tr>";
			$update++;
		}
	echo"
	</table>
	<br />
	<INPUT type='submit' value='Change Status'> <INPUT type='reset'>
	</form>";
}
catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}
include("footer.php");
?>

 

Second page:

<?php
include("header.php");
$pagename = $_SERVER["PHP_SELF"];
if ($rgroupmd5!="f33080ea7dbf7554d694a5adbd0d3855" and $rgroupmd5!="e3afed0047b08059d0fada10f400c1e6" and $rgroupmd5!="20b69ba10e54e418278485c57bd37aa4" and $rgroupmd5!="a4e208ad36a52f166d367fc411f3fac5" and $rgroupmd5!="16c590c637ba7c30891a9ad59d481ab1")
echo "Not a valid user";
else
{
try
    {
	$status = $_POST['status'];
	$update = $_POST['update'];
	$id = $_POST['id'];
	$modifieddate = date("m/d/y");
	for($i=0; $i < count($id); $i++)
	{
		if(in_array($i,$update))
		{
			$echangestatus = DB::getInstance()->exec("
			UPDATE 
				tInvoices
			SET  
				Status='$status[$i]', 
				LastModified='$ruserid',
				ModifiedDate='$modifieddate'
			WHERE 
				id='$id[$i]'
		"); 
		}
	}
	echo "The requested invoices' status have been updated.";
}
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}
include("footer.php");
?>