...

View Full Version : running total problem



RCR
07-21-2012, 01:46 AM
I am trying to generate a layout based on data in a table called MM. The data in the MM table looks like this:


ID... datem ..............item1 ....item2........item3....i1price....i2price....i3price
1.....2012-07-16.......Cookies......HotDog......Pizza.....1.00......2.00......2.50
2.....2012-07-17.......Soup.........Salad.........Candy.....1.00.....2.00......0.50

i1price is the price of item1.
i2price is the price of item2
i3price is the price of item3.

I need to create a form where the user can buy each of the items, for each day.
The user can input how many units of item1 can be purchased, how many of item2 and how many of item3.

The form has a tabular structure to make it easy for data entry.
datem ..............item1 .....acc1.....item2.....acc2...item3.....acc3.....total
2012-07-16.......Cookies...____....HotDog...____....Pizza....____.....
2012-07-17.......Soup.......____....Salad......____....Candy...____.....

For each row displayed, after the user types in the quantities the total should be calculated based on the price of the item,when the Apply button is pressed.
For example:

datem ...........item1 .....acc1.....item2.....acc2....item3.....acc3.....total..button
2012-07-16....Cookies..._2__....HotDog...__1_....Pizza...._1_...._6.50_..Apply
2012-07-17....Soup......._2__....Salad......__1_....Candy..._2_...._5.00_...Apply

The value 6.50 is created by:
2*(price of cookies i.e. 1.00)+1*(price of HotDog i.e 2.00) + 1* (price of Pizza ie. 2.50) = 6.50

To get this i have written the following code :
<? ob_start(); ?>
<?php
include_once '../../core/init.php';

$sql="SELECT ID, datem, item1,item1price, item2, item2price, item3,item3price from mm order by datem";
$result= mysql_query($sql);
if (!$result)
{
echo 'Error fetching items'.mysql_error();
}
else
{
while ($row = mysql_fetch_array($result))
{
$menus[] = array('ID' => $row['ID'],
'datem' => $row['datem'],
'item1' => $row['item1'],
'i1price'=> $row['i1price'],
'item2' => $row['item2'],
'i2price'=> $row['i2price'],
'item3' => $row['item3'],
'i3price'=> $row['i3price'],

);
}

}

if (isset($_POST['dailybuy']) and $_POST['dailybuy'] == 'Apply')
{
how do i generate the totals when the apply button is selected for each row???
}

?>
<? ob_flush(); ?>
<!DOCTYPE html>
<html>
<head>
<title>Hot Lunch - School Administration</title>
<link rel="stylesheet" href="../../css/primary.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

</head>

<body>
<form id="poster" action="" method="POST" style="display: none;"></form>
<table class="stable" width="900px">
<thead>
<tr class="th"><td>Id</td><td>Date</td><td>Daily Menu Items </td><td></td><td></td><td></td><td></td><td></td><td></td><td>Total</td></tr>
</thead>

<tbody>
<?php foreach ($menus as $menu): ?>
<tr>
<td><?php echo $menu['ID']; ?></td>
<td><?php echo $menu['datem']; ?></td>

<td><?php echo $menu['item1'].'-'.$menu['i1price']; ?> </td>
<td> <input type="text" name="q1" size="2" value="<?php if (isset($_POST['q1'])) echo $_POST['q1'];?>"></td>
<td><?php echo $menu['item2'].'-'.$menu['i2price']; ?></td>
<td><input type="text" name="q2" size="2" value="<?php if (isset($_POST['q2'])) echo $_POST['q2'];?>"></td>
<td><?php echo $menu['item3'].'-'.$menu['i3price']; ?></td>
<td><input type="text" name="q3" size="2" value="<?php if (isset($_POST['q3'])) echo $_POST['q3'];?>"></td>

<td><input type="text" name="subtotal"
value="<?php echo $_POST['q1'] * $menu['item1price'] +
$_POST['q2'] * $menu['item2price'] +
$_POST['q3'] * $menu['item3price']; ?>" >
</td>
<td>
<input type="submit" name="dailybuy" value="Apply" onclick="submitForm(this);" ></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>

=================
Can someone please give me some pointers in how I can obtain the desired output. Your time and help is greatly appreciated.

Len Whistler
07-21-2012, 07:50 AM
The design of the MySQL looks bad, I would change it. Below is an example with two tables - Items and Date, which would be joined together for output and input.

Items
ID | ID_Date | Item | Price
1 | 1 | Cookies | 1.00
2 | 1 | HotDog | 2.00
3 | 1 | Pizza | 2.50
4 | 2 | Soup | 1.00
5 | 2 | Salad | 2.00
6 | 2 | Candy | 0.50

Date
ID | Date
1 | 2012-07-16
2 | 2012-07-17


----

HDRebel88
07-21-2012, 08:21 AM
The design of the MySQL looks bad, I would change it. Below is an example with two tables - Items and Date, which would be joined together for output and input.

Items
ID | ID_Date | Item | Price
1 | 1 | Cookies | 1.00
2 | 1 | HotDog | 2.00
3 | 1 | Pizza | 2.50
4 | 2 | Soup | 1.00
5 | 2 | Salad | 2.00
6 | 2 | Candy | 0.50

Date
ID | Date
1 | 2012-07-16
2 | 2012-07-17


----

If you need each food item to be selectable on more than one date, this may be a better option:

items
id | item | price
1 | Cookies | 1.00
2 | HotDog | 2.00
3 | Pizza | 2.50
4 | Soup | 1.00
5 | Salad | 2.00
6 | Candy | 0.50

date
id | item_id | datem
1 | 1 | 2012-07-16
2 | 2 | 2012-07-16
3 | 3 | 2012-07-16
4 | 4 |2012-07-17
5 | 5 |2012-07-17
6 | 6 |2012-07-17

Because then you could add this to the "date" table:

7 | 3 | 2012-07-18
8 | 4 | 2012-07-18
9 | 6 | 2012-07-18

RCR
07-21-2012, 10:23 AM
Thank you for your replies on the design, that will be fixed.

Can anyone please help with my running totals issue?

HDRebel88
07-21-2012, 12:16 PM
You really should get your PHP code out of the HTML.

This:



<? ob_flush(); ?>
<!DOCTYPE html>
<html>
<head>
<title>Hot Lunch - School Administration</title>
<link rel="stylesheet" href="../../css/primary.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

</head>

<body>
<form id="poster" action="" method="POST" style="display: none;"></form>
<table class="stable" width="900px">
<thead>
<tr class="th"><td>Id</td><td>Date</td><td>Daily Menu Items </td><td></td><td></td><td></td><td></td><td></td><td></td><td>Total</td></tr>
</thead>

<tbody>
<?php foreach ($menus as $menu): ?>
<tr>
<td><?php echo $menu['ID']; ?></td>
<td><?php echo $menu['datem']; ?></td>

<td><?php echo $menu['item1'].'-'.$menu['i1price']; ?> </td>
<td> <input type="text" name="q1" size="2" value="<?php if (isset($_POST['q1'])) echo $_POST['q1'];?>"></td>
<td><?php echo $menu['item2'].'-'.$menu['i2price']; ?></td>
<td><input type="text" name="q2" size="2" value="<?php if (isset($_POST['q2'])) echo $_POST['q2'];?>"></td>
<td><?php echo $menu['item3'].'-'.$menu['i3price']; ?></td>
<td><input type="text" name="q3" size="2" value="<?php if (isset($_POST['q3'])) echo $_POST['q3'];?>"></td>

<td><input type="text" name="subtotal"
value="<?php echo $_POST['q1'] * $menu['item1price'] +
$_POST['q2'] * $menu['item2price'] +
$_POST['q3'] * $menu['item3price']; ?>" >
</td>
<td>
<input type="submit" name="dailybuy" value="Apply" onclick="submitForm(this);" ></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>


Should really look like this:



<? ob_flush();
$display=''; /*initially set $display to a blank variable outside the foreach statement.
We have to concatenate the variable inside the foreach statement because it would overwrite
the previous iteration through otherwise.*/
foreach ($menus as $menu){
$display.='<tr>
'.$menu['ID'].'</td>
<td>'.$menu['datem'].'</td>
<td>'.$menu['item1'].'-'.$menu['i1price'].'</td>
<td> <input type="text" name="q1" size="2" value="
';
if (isset($_POST['q1'])){
$display.=$_POST['q1'];
$display.='
"></td>
<td>'.$menu['item2'].'-'.$menu['i2price'].'</td>
<td><input type="text" name="q2" size="2" value="
';
if (isset($_POST['q2'])){
$display.=$_POST['q2'];
$display.='
"></td>
<td>'.$menu['item3'].'-'.$menu['i3price'].'</td>
<td><input type="text" name="q3" size="2" value="
';
if (isset($_POST['q3'])){
$display.=$_POST['q3'];
$display.='
"></td>

<td><input type="text" name="subtotal"
value="'.$_POST['q1'] * $menu['item1price'] +
$_POST['q2'] * $menu['item2price'] +
$_POST['q3'] * $menu['item3price'].'
">
</td>
<td>
<input type="submit" name="dailybuy" value="Apply" onclick="submitForm(this);" ></td>
</tr>';
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Hot Lunch - School Administration</title>
<link rel="stylesheet" href="../../css/primary.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

</head>

<body>
<form id="poster" action="" method="POST" style="display: none;"></form>
<table class="stable" width="900px">
<thead>
<tr class="th"><td>Id</td><td>Date</td><td>Daily Menu Items </td><td></td><td></td><td></td><td></td><td></td><td></td><td>Total</td></tr>
</thead>

<tbody>
<?php echo $display; ?>
</tbody>
</table>
</body>
</html>



Also this part seems little redundant to me:



while ($row = mysql_fetch_array($result))
{
$menus[] = array('ID' => $row['ID'],
'datem' => $row['datem'],
'item1' => $row['item1'],
'i1price'=> $row['i1price'],
'item2' => $row['item2'],
'i2price'=> $row['i2price'],
'item3' => $row['item3'],
'i3price'=> $row['i3price'],

);
}


You just set the variable as $menu in the while statement:
while ($menu = mysql_fetch_array($result))


When the user hits the "Apply" button, do you want the page to reload, or just send a request to the server and update the total in real time? If so, your best bet is a second PHP page that does the calculation which is requested via an AJAX call (asynchronous javascript and xml). The button would send a Javascript request (which it seems like you already have), then the Javascript code would send the request to the second PHP page, and return the data, then update the HTML element where you identify the "id" attribute that matches the AJAX output.

Just out of curiosity, what text editor, or IDE are you using?

RCR
07-21-2012, 12:32 PM
Thank you for your reply. It is going to take me sometime to wrap my head around what you said and start learning how to implement this with ajax. (I do not know ajax :( yet).

I am using the NetBeans IDE.

Thanks again. More great tips like this are most welcome, so I can get some inspiration on how to tackle this.

RCR
07-21-2012, 12:38 PM
The javascript that I was hoping to use was based off this article, however I am not completely sure it will serve my purpose.
http://federmanscripts.com/2010/01/12/form-and-table-row-nesting-workaround/
Any ideas...

HDRebel88
07-21-2012, 01:43 PM
I'm not sure about that Javascript you linked to but, here's a working example of AJAX:



function requestObj() {
var xmlhttp;
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
return xmlhttp;
}


var http = requestObj();

function sndReq(year,month){
http.open('get', 'signup_sessionset.php?day_menu=yes&year='+year+'&month='+month, true);
http.onreadystatechange = handleResponse;
http.send(null);
}

function handleResponse(){
if(http.readyState == 4){
var response = http.responseText;
document.getElementById("register_day").innerHTML = response;
}
}



The requestqObj function creates a new instance of a XML HTTP connection.

Then the requestObj function is assigned to the variable http.

The sndReq function sends the request to the page set in the http.open command (which is essentially requestObj().open; because the requestObj function is set to the variable http).

The data is then passed back the handleResponse function. And it checks if the response is code #4, this is just AJAX's way of telling the function that the request was successful.

Then it gets the requested data.

From there this line:
document.getElementById("register_day").innerHTML = response updates a HTML element that has the id attribute set to register_day.
So say:



<div id="register_day"></div>


The response from the AJAX request would be dynamically inserted into the DIV.

You can also apply the data back to an input tag, or any HTML tag/element that accepts the id attribute.

So in your form (in your case, the "apply" submit button) you would add onclick="sndReq(this.form);"


It's not the best resource, because occasionally their information is wrong, but you might find the w3cschools tutorial helpful:
http://www.w3schools.com/ajax/ajax_xmlhttprequest_send.asp

And a decent choice on the IDE. I personally use the NotePad++ text editor, but an IDE is good too. Was just making sure you weren't working in a regular text editor, like the basic Notepad included with Windows.

RCR
07-21-2012, 06:21 PM
This post is not relevant any more...please proceed to my next issue...

RCR
07-21-2012, 08:34 PM
Okay...I think I got the if closing braces to work...however, the one of the input elements is not displaying. Is something missing for the quantity to not display?... Fresh eyes are always helpful..I've been looking at this code too long now ....


==
<? ob_flush();
$display=''; /*initially set $display to a blank variable outside the foreach statement.
We have to concatenate the variable inside the foreach statement because it would overwrite
the previous iteration through otherwise.*/
foreach ($menus as $menu){
$display.='<tr class="form">
<td>'.$menu['ID'].'</td><td>
'.$menu['datem'].'</td>
<td>'.$menu['item1'].'-'.$menu['item1price'].'</td>
<td> <input type="text" name="q1" size="2" value="
';
if (isset($_POST['q1']))
{
$display.=$_POST['q1'];
}
$display.='"></td>
<td>'.$menu['item2'].'-'.$menu['item2price'].'</td>
<td><input type="text" name="q2" size="2" value="
';
if (isset($_POST['q2'])){
$display.=$_POST['q2'];}
$display.='
"></td>
<td>'.$menu['item3'].'-'.$menu['item3price'].'</td>
<td>
<input type="text" name="q3" size="2" value="
';
if (isset($_POST['q3'])){
$display.=$_POST['q3'];}
$display.='
"></td>
<td>
<input type="text" name="subtotal"
value="'.$_POST['q1'] * $menu['item1price'] +
$_POST['q2'] * $menu['item2price'] +
$_POST['q3'] * $menu['item3price'].'">
</td>
<td>
<input type="submit" name="dailybuy" value="Apply" onclick="submitForm(this);" ></td>
</tr>';
}
?>
================================================



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum