Building a table using date ranges

General PHP Help
#1

Hi Folks
I am having some real difficulty working this one out and I hope someone can assist.
I have a table with fields as follows:
id, userid, name, amount, nextdue, frequency

(The frequency field contains an integer from another table with options: weekly(1), fortnightly(2), monthly(3), quarterly(4) and annually(5))

What the user will do is select a number of weeks (‘x’) (i.e.4,8,12 etc) and from this I want to build a table with ‘x’ number of columns, each being one week, starting from todays date.
i.e. col1 = today’s date to (today’s date + 6)
col2 = (today’s date+7) to (today’s date + 13)
col3 = (today’s date+14) to (today’s date + 20) etc etc
Then I want to iterate through each record from the above table and insert the ‘amount’ field into each column where this amount would be due, given the columns date range, and the frequency of the records. If the amount does not fall due within that columns date range, a $0.00 would be inserted.

So say for example that a record has a nextdue date of today and a frequency of ‘1’ (being weekly). This would insert that amount into every column across the table because this amount will fall due once a week. A monthly frequency with a nextdue date of today will show in the first column, then approximately every 4th column after that. I hope that makes sense :slight_smile:

Then at the bottom I would like to total each column.
What I am thinking is that I will put the values into textboxes and setup an event to update the totals box using javascript so that a user can change the values in the table and see how the total amounts will change.
My first problem though is that I cannot get the table setup so the amounts are being inserted into the right columns to start with, and that they are then being totalled properly.
Here is where I am at currently:

A simple form getting the number of columns for the table:

[code][

4 Weeks 8 Weeks 12 Weeks [/code] On submit I run a script to update the nextdue field. This uses the current nextdue value and using the frequency field, calculates the next date that this record would be due and updates the db field.

Now I want to build my table:
[php]

<?php $totals = array(); $todaysdate = mktime(0,0,0,date("m"), date("d"), date("y")); for($k=0; $k < ($_POST['numWeeks']); $k++) { if($k == 0){ echo ""; } else { echo ""; } } echo ""; $sql="SELECT name, amount, nextdue, frequency FROM records ORDER BY nextdue"; $result = mysql_query($sql, $mysql) or die(mysql_error()); $startdate = $todaysdate; while ($row = mysql_fetch_assoc($result)) { echo ""; [/php] This gives me a simple table with a first column with the name field and then $_POST['numWeeks'] number of columns with weekly date ranges. This is also where I start having trouble. How do I then iterate through the date ranges for each record, inserting the value where appropriate? Any help will be greatly appreciated!
" . date("d-m-y",($todaysdate+($k * 604800))) . " to " . date("d-m-y",($todaysdate+518400)) . "" . date("d-m-y",($todaysdate+($k * 604800))) . " to " . date("d-m-y",($todaysdate+($k * 604800)+518400)) . "
" . $row['name'] . "
#2

Well, you show code that creates a table’s headers, then a query that pulls fields from a database.
The data is pulled as a list of rows which would each be you would just have to do your calculations
and place them into the table. Something like the start that you posted:
while ($row = mysql_fetch_assoc($result))
{
echo “

” . $row[‘name’] . “”;

Something similar to this would work… (Not sure about your calculations!)
[php]
while ($row = mysql_fetch_assoc($result)) {
// For each row of your data create a new row…
echo “

”;
// Loop thru same number cols as headers…
for($k=0; $k < ($_POST[‘numWeeks’]); $k++) {
echo “”;
// Next check for legal value to display or create it… (If not, display 0)
if (???){
echo $row[‘amount’] . “”;
} else {
echo “0.00”;
}
echo “”;
}
[/php]
The above code is just an example… I do not know exactly what you want displayed. This sample displays the amount or zero depending on the condition which I did not include. You mentioned "budget’ so, I will assume you would want to take the amount and divide it by the number of cols. So, if the amount was $100 and the number of cols was 4, then the amount displayed would be $100 / 4 or $25… You would have to change the two lines in the above sample. The display would not be: echo $row[‘amount’] . “”;
But, would be something like: echo ($row[‘amount’] / $_POST[‘numWeeks’] . “”; And, the condition
would have to check for the amount minus the previous cols times the amount for that col. This would be
a bit tricky, you would have to use a formula something like this:
$colAmount = $row[‘amount’] / $_POST[‘numWeeks’];
$currentAmount = $row[‘amount’] - ($k-1) * $colAmount;
So, with this the condition would be to check for $currentAmount to be zero… Or just use number_format
function of PHP to display only whole numbers with two decimals. So, 0.00003 would become $00.00…

Not sure if that is what you needed, but, should give you a few ideas on how to handle it. Good luck!

One note: I would not actually use $_POST[‘numWeeks’] every time you need to use it. That would mean
the server would have to scan the requesting page’s array every time you use it. It would be faster to put
it into a variable and use that… $numweeks=$_POST[‘numWeeks’]; … (I left it your way.)

#3

Hi Erniealex, thanks for the reply!

Sorry… I don’t think I explained it well enough :slight_smile:
let’s create two records in the db as an example.

  1. Name=‘invoice 1’, amount=$10.00, frequency=1(weekly), nextdue=2012-04-28
  2. Name=‘invoice 2’, amount=$18.50, frequency=2(fortnightly), nextdue=2012-04-28

Using my code above to setup a table (I actually made a booboo with my code and left out an empty cell in the table)… The table needs to be 1 column wider than $_POST[‘numWeeks’] because I want the name from the record to be in the first column of subsequent rows.

So, let’s say the user chooses 4 weeks:
First row of table (header row) =
Col1=empty cell

Col2= “$todaysdate + (0*604800)” to “$todaysdate + 518400”//first iteration so $k=o and if evaluates to true. If $todaysdate = 2012-04-23 then the actual result would be “2012-04-23 to 2012-04-29” which is 7 days inclusive.

Col3= “$todaysdate + (1604800)" to "$todaysdate + (1604800)+518400”//2nd iteration so $k=1, If $todaysdate = 2012-04-23 then the actual result would be “2012-04-30 to 2012-05-06” which is 7 days inclusive.

Col4= “$todaysdate + (2604800)" to "$todaysdate + (2604800)+518400”//3rd iteration so $k=2, If $todaysdate = 2012-04-23 then the actual result would be “2012-05-07 to 2012-05-13” which is 7 days inclusive.

Col5= “$todaysdate + (3604800)" to "$todaysdate + (3604800)+518400”//4nd iteration so $k=3, If $todaysdate = 2012-04-23 then the actual result would be “2012-05-14 to 2012-05-20” which is 7 days inclusive.

Second row is where it gets tricky for me:
Col1 = $row[‘name’]

For col2-5 I need to test if $row[‘nextdue’] falls between the date range as shown in the row above so something like:
[php]
Switch($row[‘frequency’])
{
Case 1:
For($i =0; $i < $_POST[‘numWeeks’]; $i++)
{
If(strtotime($row[‘nextdue’])+($i * 604800) >= ($todaysdate+($i * 604800)) && strtotime($row[‘nextdue’])+(($i * 604800)+604800) <= ($todaysdate+($i * 604800)+604800))
{ echo $row[‘amount’]; } else { echo “$0.00”; }
}
Break;
}
[/php]
This test needs to be inside the while loop that is printing the row for each record. Case 1 looks at frequency value…1 = weekly.
So $row[‘nextdue’] =2012-04-28 which is greater than 2012-04-23 and less than 2012-04-23+7 days so if returns true and prints the amount…next iteration…
2012-04-28 + 7 days = 2012-05-05 which is greater than 2012-04-23+7 days and is less than 2012-04-23+14 days so prints amount… Next iteration and so on. This works ok for weekly frequency.

But with case 2: (fortnightly) things get tricky, and I can’t figure out the formula…

I hope this gives you a better explanation of what I am trying to do.

Basically set up ‘x’ columns with an associated date range, each column incrementing 1 week starting from todays date.
For each record in the database iterate the nextdue date by its frequency and determine if that record will fall within each column date range, and if so print the amount.

I have been working on this for weeks on and off and just can’t get anything beyond the weekly frequency working for me.

#4

Okay, I think I get it now. My sample can be changed to do this without many changes. With a couple small tricks, I do not think you need to use a case…

Just one quick question and I will make up another sample for you to try…

You said 5 options 1, 2, 4, 8 12 weeks, correct?
So does this mean you want all 52 weeks across as cols ?
Or, just 52 divided by the number 1 to 12?

#5

The user will be able to choose to see a table spanning 4,8,12,26 or 52 weeks. For the later two I will probably show month columns rather than week columns (I.e 6 or 12 months)

Thanks!

#6

Okay, I have a solution… Just have to experiment a little future…

I found some problems in your code. First, you seem to be adding 604800 times the col# and I can’t figure that one out. Okay, never mind, I got it… 7 days x 24 hours x 60 minute x 60 seconds… SILLY!!! LOL…

Okay, I’m just going to use date(+ 7 days)… LOL, much simpler! Okay, I will put that into the mix and should have a sampler for you in another 30 minutes or so… Later…

#7

LOL thanks Ernie… My mind does work in mysterious ways :slight_smile:
Man… Really looking forward to your suggestion… This has done my head in trying to work it out!

#8

YIKES !!! That was a job… Okay, still have a way to go yet. But, here is a sampler for you…
It’s one entire page that posts to itself. It is just to show you how to use the time functions to create the first row of the display. It basically uses a few tricks that I have learned the past few months helping others.
So, anyway, copy the entire page and put it on your server or locally, whatever. You can click on the the dropdown and change dates for testing. I think it works the way you want. (Well, it’s just the first line…)

But, it does show how to do it. I will just have to sort out some date compares for the rest…
So what it does it allow a number of weeks and a date to be inputted. The date is the nextDue that you talked about. It doesn’t do anything at the moment. I still have to play with the conditions and the database items.
I might have to finish it in the morning as I am running a bit tired now… Anyway, here it is…

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Date Table Testing...</title>
</head>
<body>
<form name="form1" action="" method="post">
<?PHP 
  $numWeeks = $_POST['weeks'];
  if ($numWeeks == "") $numWeeks = 4;
  $nextDue = $_POST['nextdue'];
  if ($nextDue == "") $nextDue = date("Y-m-d");
  $todaysdate = date("Y-m-d"); 
  echo "<br><br><br><center>TODAY'S DATE: " . $todaysdate . " !!!<br><br>";

  // First start table and create headers for cols...
  echo "<table width='100%' border='1'><tr><td></td>";
  For($i =0; $i < $numWeeks; $i++)
     {
	 $fromDate = strtotime(date("Y-m-d", strtotime($todaysdate)) . " +" . $i*7 . " days");
	 $toDate   = strtotime(date("Y-m-d", strtotime($todaysdate)) . " +" . $i*7 . " days +6 days");
	 echo "<td>" . date("Y-m-d", $fromDate) . " to " . date("Y-m-d", $toDate) . "</td>";
      }
   echo "</tr>";
   echo "</table>";
?>
</form>
</body>
</html>

Again, this is just a sampler for the first line. I will add in the database and get it to you soon. (If I can stay awake…) This version with the date functions is much better way to go… Let me know what you think!

#9

Okay, off to bed… Here is another version with the database stuff in place… I can’t test it without the DB.
And, not sure about the date compare, but, should be close… Let me know what it does…

Good night…

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>form addition testing...</title>
</head>
<body>
<form name="form1" action="" method="post">
<?PHP 
  $numWeeks = $_POST['weeks'];
  if ($numWeeks == "") $numWeeks = 4;
  $nextDue = $_POST['nextdue'];
  if ($nextDue == "") $nextDue = date("Y-m-d");
  $todaysdate = date("Y-m-d"); 
  echo "<br><br><br><center>TODAY'S DATE: " . $todaysdate . " !!!<br><br>";
?>

<center>SELECT WEEKS:  
  <SELECT name="weeks">
    <option value=4>4</option><option value=8>8</option><option value=12>12</option><option value=26>26</option><option value=52>52</option>
  </SELECT>   ENTER NEXTDUE DATE (2012-04-28) <input type="text" name="nextdue" value='<? echo $nextDue; ?>' /><input type="submit" name="submit" value="SUBMIT" />
</center> <br /><br /> 

<?PHP
  // First start table and create headers for cols...
  echo "<table width='100%' border='1'><tr><td></td>";
  For($i =0; $i < $numWeeks; $i++)
      {
	   $fromDate = strtotime(date("Y-m-d", strtotime($todaysdate)) . " +" . $i*7 . " days");
	   $toDate   = strtotime(date("Y-m-d", strtotime($todaysdate)) . " +" . $i*7 . " days +6 days");
	   echo "<td>" . date("Y-m-d", $fromDate) . " to " . date("Y-m-d", $toDate) . "</td>";
      }
  echo "</tr>";
   
  // Now read database and display correct cols...
  while ($row = mysql_fetch_assoc($result)) {
      // For each row of your data create a new row...
      echo "<tr><td>" . $row['name'] . "</td>";
      For($i =0; $i < $numWeeks; $i++)
         {
	       echo "<td>";
	       If(strtotime($row['nextdue']) < strtotime(date("Y-m-d", strtotime($todaysdate)) . " +" . $i*7 . " days"))
		     {
			   echo $row['amount'];
		     } else {
			   echo "$0.00";
             }
          }
	  echo "</tr>";	  
      }
  echo "</table>";
?>
</form>
</body>
</html>
#10

Thanks so much for taking the time! I’ll give this a blast tomorrow and come back to you with results.

#11

Hi Ernie!

Thanks so much for your help. Your way of adding dates was much prettier than mine :slight_smile:
I have plucked some of your code out and merged with mine but I still have the same problem. The best bet I think will be to give you the db table and all of my code and then you will see my problem.

Here is a sample table:


CREATE TABLE IF NOT EXISTS `incomeaccounts` (
  `incAcc_id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `incName` text NOT NULL,
  `incAmount` float(10,2) NOT NULL,
  `incFreq` int(11) NOT NULL,
  `incNext` date NOT NULL,
  PRIMARY KEY (`incAcc_id`),
  KEY `incFreq` (`incFreq`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `incomeaccounts`
--

INSERT INTO `incomeaccounts` (`incAcc_id`, `userid`, `incName`, `incAmount`, `incFreq`, `incNext`) VALUES
(5, 0, 'test', 1.00, 1, '2012-04-28'),
(6, 0, 'test2', 223.00, 2, '2012-04-28'),
(7, 0, 'test3', 323.00, 3, '2012-04-28'),
(8, 0, 'test4', 4345.00, 4, '2012-04-28'),
(9, 0, 'test5', 345.00, 5, '2012-04-28');

--
-- Constraints for table `incomeaccounts`
--
ALTER TABLE `incomeaccounts`
  ADD CONSTRAINT `incomeaccounts_ibfk_1` FOREIGN KEY (`incFreq`) REFERENCES `frequencies` (`freq_id`);


--
-- Table structure for table `frequencies`
--

CREATE TABLE IF NOT EXISTS `frequencies` (
  `freq_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`freq_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `frequencies`
--

INSERT INTO `frequencies` (`freq_id`, `name`) VALUES
(1, 'Weekly'),
(2, 'Fortnightly'),
(3, 'Monthly'),
(4, 'Quarterly'),
(5, 'Annually');

This gives you sample data, 1 record for each frequency.

Here is my basic page:
[php]

<?php require_once('Connections/mysql.php'); error_reporting(E_ALL ^ E_NOTICE); ?> Untitled Document
Add Accounts
4 Weeks 8 Weeks 12 Weeks <?php if(isset($_POST['buildBudget'])) { include 'includes/updatedates.php'; include 'includes/buildbudget.php'; } ?> <?php if(isset($message)){ echo $message; } ?>
[/php] All the updatedates.php file does is iterate through the db and bring the incNext dates forward to the next due date in the future, so you don't need that. Here is the buildbudget.php file so far: [php] <?php $today =date("Y-m-d"); echo ""; for($k=0; $k < ($_POST['numWeeks']); $k++) { $fromDate = strtotime(date("Y-m-d", strtotime($today)) . " +" . $k*7 . " days"); $toDate = strtotime(date("Y-m-d", strtotime($today)) . " +" . $k*7 . " days +6 days"); echo ""; } echo ""; $sql="SELECT incName, incAmount, incNext, incFreq FROM incomeaccounts ORDER BY incNext"; $result = mysql_query($sql, $mysql) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)) { $startdate = strtotime($today); echo ""; for($i=0; $i < $_POST['numWeeks']; $i++) { if((strtotime($row['incNext']) < strtotime("+7 days", $startdate)) ) { echo ""; $startdate = strtotime("+7 days", $startdate); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } echo ""; } ?>
" . date("Y-m-d", $fromDate) . " to " . date("Y-m-d", $toDate) . "
" . $row['incName'] . "" . $row['incAmount'] . "$0.00
[/php] This works for the weekly frequencies, but if you run this you will see that all the other frequencies are also inserted when, for example, the record with a fortnightly frequency should only display every second column, the monthly records should only show once per 4 weeks (approx).

Hope you can see what I mean :slight_smile:

Thanks again!

#12

Well, that looks like totally new code… LOL…

I am leaving for a few hours. I will try to figure it out for you a bit late tonight, so check in in the morning.
You are getting close with this project. I just have plans tonight… Later…

#13

Thanks Ernie…
I’m going to send you a PM and let you know what the end game is for this :slight_smile:

Your help is greatly appeciated!

#14

WOOOHOOO! I cracked it!

Here’s what I did:
[php]

<?php $today =date("Y-m-d"); echo ""; for($k=0; $k < ($_POST['numWeeks']); $k++) { $fromDate = strtotime(date("Y-m-d", strtotime($today)) . " +" . $k*7 . " days"); $toDate = strtotime(date("Y-m-d", strtotime($today)) . " +" . $k*7 . " days +6 days"); echo ""; } echo ""; $sql="SELECT incName, incAmount, incNext, incFreq FROM incomeaccounts ORDER BY incNext"; $result = mysql_query($sql, $mysql) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)) { $startdate = strtotime($today); echo ""; switch($row['incFreq']) { case 1: $nextdue = strtotime($row['incNext']); for($i=0; $i < $_POST['numWeeks']; $i++) { if($nextdue < strtotime("+7 days", $startdate)) { echo ""; $startdate = strtotime("+7 days", $startdate); $nextdue = strtotime("+7 days", $nextdue); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } break; case 2: $nextdue = strtotime($row['incNext']); for($i=0; $i < $_POST['numWeeks']; $i++) { if($nextdue < strtotime("+7 days", $startdate)) { echo ""; $startdate = strtotime("+7 days", $startdate); $nextdue = strtotime("+14 days", $nextdue); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } break; case 3: $nextdue = strtotime($row['incNext']); for($i=0; $i < $_POST['numWeeks']; $i++) { if($nextdue < strtotime("+7 days", $startdate)) { echo ""; $startdate = strtotime("+7 days", $startdate); $nextdue = strtotime("+1 month", $nextdue); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } break; case 4: $nextdue = strtotime($row['incNext']); for($i=0; $i < $_POST['numWeeks']; $i++) { if($nextdue < strtotime("+7 days", $startdate)) { echo ""; $startdate = strtotime("+7 days", $startdate); $nextdue = strtotime("+3 months", $nextdue); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } break; case 5: $nextdue = strtotime($row['incNext']); for($i=0; $i < $_POST['numWeeks']; $i++) { if($nextdue < strtotime("+7 days", $startdate)) { echo ""; $startdate = strtotime("+7 days", $startdate); $nextdue = strtotime("+1 year", $nextdue); } else { echo ""; $startdate = strtotime("+7 days", $startdate); } } break; } echo ""; } ?> 
</table>

[/php]
works a treat so far!

Thanks for your help Ernie! I’ll keep you updated on progress :slight_smile:

" . date("Y-m-d", $fromDate) . " to " . date("Y-m-d", $toDate) . "
" . $row['incName'] . "" . $row['incAmount'] . "$0.00" . $row['incAmount'] . "$0.00" . $row['incAmount'] . "$0.00" . $row['incAmount'] . "$0.00" . $row['incAmount'] . "$0.00
#15

NICE! Glad I could help… I will mark it solved and for other issues, we can start a new post…

For others, anyone interested in date compares or date functions, look at this code. In this project one of the main problems was adding days or weeks to the current date and looking at future dates. This can be done in PHP if you just find the correct code. It has been done in this project. Hope it helps others!

#16

Hey h20! Delete some messages from your private message box…
(As a newer member, that mailbox is quite small…)

I was sending you ideas for the new grid…

#17

ok…The challenge continues :slight_smile:

So I have this table populating the way I want but now I need to delve into javascript which I have almost zero experience with so any help on this one will be appreciated.

Basically I have a series of input fields that are being populated from a db table. When this ‘grid’ of inputs is being put together I am naming all of the inputs dynamically with 0inccol0, 0inccol1, 0inccol2, 0inccol3 etc.
(First number being the row, last number being the column.)
What I want to do is have a field that sums all of the inputs with matching column numbers using the above naming convention. i.e. all the input names ending in 0 totalled in one field, all the input names ending in 1 totalled in another field etc. I also want to add an onChange event (or similar, Ernie suggested onBlur?) to each of my inputs that will update the value of the ‘total’ fields when one of the ‘#inccol#’ inputs above is changed manually by the user.

Here is how the table is put together
[php]
for($i=0; $i < $numweeks; $i++)
{
if($nextdue < strtotime("+7 days", $startdate))
{
echo “

<input type=‘text’ name=’”.$line.“inccol”.$i
."’ value=’$" . $row[‘incAmount’] . “’ style=‘text-align:center; background-color:#cccccc’/>”;
$totals[] = array(“weekend” => $startdate, “amount” => $row[‘incAmount’]);
$startdate = strtotime("+7 days", $startdate);
$nextdue = strtotime("+7 days", $nextdue);
} else {
echo “”;
$totals[] = array(“weekend” => $startdate, “amount” => 0.00);
$startdate = strtotime("+7 days", $startdate);
}
}
[/php]
At the moment I am using an array to provide me with totals fields but this hopefully won’t be necessary if I can figure out the javascript component I am looking for.
The $line value is incremented after each iteration of a while loop for the db query, basically giving me a row number for my table.

Here is a sample of the HTML output:

<tr>
<td style='width:60px'><a href='index.php?inc_id=30' onclick='return confirmDelete(this)'>Remove</a></td>
<td style='text-align:center'>test</td>
<td ><input type='text' name='0inccol0' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol1' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol2' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol3' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol4' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol5' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol6' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='0inccol7' value='$432.00' style='text-align:center; background-color:#cccccc'/></td>
</tr>
<tr>
<td style='width:60px'><a href='index.php?inc_id=31' onclick='return confirmDelete(this)'>Remove</a></td>
<td style='text-align:center'>test2</td>
<td ><input type='text' name='1inccol0' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol1' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol2' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol3' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol4' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol5' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol6' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='1inccol7' value='$213.00' style='text-align:center; background-color:#cccccc'/></td>
</tr>
<tr>
<td style='width:60px'><a href='index.php?inc_id=32' onclick='return confirmDelete(this)'>Remove</a></td>
<td style='text-align:center'>test3</td>
<td ><input type='text' name='2inccol0' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol1' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol2' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol3' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol4' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol5' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol6' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
<td ><input type='text' name='2inccol7' value='$65.00' style='text-align:center; background-color:#cccccc'/></td>
</tr>

Sorry its not nicely formatted…this is how php outputs it :slight_smile:
This shows three rows, each with inputs starting with a number (being the row number, starting from 0) then a column number at the end.

So if I had 3 rows, I would want to sum the fields ‘0inccol0’, ‘1inccol0’ and ‘2inccol0’ in one ‘totals’ field.
In the next ‘totals’ field I would want to sum ‘0inccol1’, ‘1inccol1’ and ‘2inccol1’, and so on for ‘x’ number of columns. (in the above example there are 8 columns so there would be 8 ‘totals’ fields).

Hope that makes some sense :slight_smile:

So in summary, I need a script to sum all the fields in each column on page load and display this total in a ‘totals’ field for each column, and a function to run on an event triggered if a user updates any individual input field.
Any javascript advice will be appreciated!

#18

Well, that shouldn’t be too hard… LOL, I have said that before!

First, I suggested “onblur” because if you use “onchange” and someone starts typing, let’s say “123.34”, when they type in “1”, that is a change and triggers the “onchange” function and does the calculations. So, the user sees odd totals being created using 1 for their input. Then, they type “2” which puts “12” in the square and the function gets called again giving another false calculation. Very annoying from a user’s viewpoint… Using “onblur” only does the calculation when the mouse is moved away from the input box. The only downside is if the user leaves the cursor hovering over the field. (Seldom happens, but, does!) You could change the “hover” text to say “move cursor from field for calculation” and display that only if they stay hovered on the field. I would say you should test with onchange and see how it looks and feels. So, hope this long explanation makes sense!

As far as the adding of cols, you would have to create a row, I assume at the bottom, that was as wide as your displayed cols. Then, when the calculations are triggered, you would have to do a FOR clause. Set all of the values in the new row to zero. Next, simply do a “foreach” loop and go thru all of your fields displayed pulling their names and values. In this loop, pull the col from the name of the field. Once you have the col number for a field, add the value in that field to your total for that col in the new row field. Should only be a few lines of code.

Hope that is what you are looking for. When you get it coded, show us and let us know how it works!

#19

Well, after a crash course in JavaScript, I must admit to having no idea :slight_smile:
I understand the getelementbyid method to pull a value from an element, but I have no idea how to do this for all the fields in my table, or how to then loop through these adding each col value to the correct total.

Ernie, your explanation sounds perfect but I can’t figure out where to start with it…any hints? :slight_smile:

#20

Well, so, you need more than a “crash” course on JS… I will see if I can help…

First, Javascript runs CLIENT-SIDE. So, it is done totally in your browser. So, if totals are needed to be saved and placed into a database, most likely you would have to call a PHP page either hidden from the user or by actually calling the PHP page using a SUBMIT button. But, CLIENT-SIDE, you can manipulate most any HTML field using a Javascript function. You can use the “getelementbyid” method or just call it directly. If you call is directly, it would be something like this: var1=document.myform.myfield.value; That order depends greatly on your page layout. Document is the top level, next would be the form name that you have fields in and then the fields and the value that is used for that type of field. So, for, let’s say a textarea, it would not be .value, but .text !

So, all of this only works if you have some way to access the data. To read some data from a field who’s id or name was created in a program, like 0incol9 , you would have to create a loop such as a for loop and create the name again. So, lets say you call the variable “cell” for the name of the cell in your grid. You can loop thru the row , called “r” and col called “c” and create a string like this: cell= r + “incol” + c; or something similar to how you created it in the first place. Then, using the new variable “cell”, you can pull the value using your getelementbyid(cell)… So, then you could do your math. This can be quite complicated. But, it can be done.

Most programmers would just send the grid out to a PHP page by posting it and refreshing the page. This also can be done by using Javascript and AJax to POST the page out to a new PHP page that reads all the fields and adds the correct totals and sends the totals back to the first page. This is not as hard as it sounds. If you post a page that has the grid inside it, ALL of the fields get POSTED to the PHP page. Then, the PHP page can loop thru the $_POST array which is just an array in the form of $fieldname=>$fieldvalue… (Sort -of !)

To look at the makeup of the $_POST array, create a form with a few fields, post it to a PHP page and have the PHP page just do print_r($_POST); It should display what is actually inside a $_POST and you will understand how to access it.

So, there are some thoughts to start you with. I am gone for several hours. But, post back with your ideas or questions and I will check in when I get home. Good luck!

Sponsor our Newsletter | Privacy Policy | Terms of Service