Total Table Columns

jimlawrnc · November 12, 2007

I have a report that pulls data from a mysql table and generates a html table with the data. I need to sum up the columns into 2 separate fields I think I need to access the result from the query but do not know how.

Here is my result.php script

<?php

session_start();

if(!session_is_registered('member_ID')) :
header('Location: index.php?msg=requires_login');
endif;

// Include database information and connectivity
include ('config/opendb.php');

// We store all our functions in one file
include ('config/functions.php');

$tech = htmlspecialchars($_POST['technician']);

$startdate = htmlspecialchars($_POST['datBegin']);
$enddate = htmlspecialchars($_POST['datBegin1']);

//build the query
  $query = sprintf("SELECT * FROM tblhours WHERE technician = '$tech' AND servicedate >= '$startdate' AND servicedate <= '$enddate' ORDER BY servicedate ASC ");

// print $query

$result = mysql_query($query);

if (mysql_num_rows($result)) {
       print "<center><TABLE BORDER=\"3\"><TR STYLE=\"font-weight: bold;\"><TD>ID</TD><TD>Technician</TD><TD>Client</TD><TD>Hours</TD><TD>Miles</TD><TD>Billable</TD><TD>Not Billable</TD><TD>Remote/Bench</TD><TD>Date of Service</TD></TR>";
      while($row = mysql_fetch_assoc($result)) {
         extract($row);
         print "<TR><TD>$ID</TD><TD>$tech</TD><TD>$client</TD><TD>$hours</TD><TD>$mileage</TD><TD>$billable</TD><TD>$nobillable</TD><TD>$remote_bench</TD><TD>$servicedate</TD></TR>";   
     
      }
   
      print "</TABLE></center>";
   }

?>

jimlawrnc · November 12, 2007

I should add the table structure...

- Table structure for table `tblhours`
--

CREATE TABLE IF NOT EXISTS `tblhours` (
  `ID` int(10) NOT NULL auto_increment,
  `technician` char(30) NOT NULL,
  `client` char(50) NOT NULL,
  `hours` float NOT NULL,
  `mileage` tinyint(10) NOT NULL,
  `billable` varchar(10) NOT NULL,
  `nobillable` varchar(10) NOT NULL,
  `remote_bench` varchar(10) NOT NULL,
  `servicedate` date default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

gin · November 13, 2007

It'd be great if you could explain what you mean by "sum up the columns into 2 separate fields". Also, please cut out all non-relevant code.

jimlawrnc · November 13, 2007

while($row = mysql_fetch_assoc($result)) {

extract($row);

print "<TR><TD>$ID</TD><TD>$tech</TD><TD>$client</TD><TD>$hours</TD><TD>$mileage</TD><TD>$billable</TD><TD>$nobillable</TD><TD>$remote_bench</TD><TD>$servicedate</TD></TR>";

I need to add the $hours column & $mileage column

the $hours column needs to be summed by the following

total hours where $billable = Yes and $remote_bench = no (or Null)

total hours where $billable = Yes and $remote_bench = Yes

total hours where $billable = No (or Null) and $remote_bench = no (or Null)

total hours where $billable = No (or Null) and $remote_bench = Yes

No (or Null) is what is in the table Yes is given if on the input form a checkbox is checked. I tried to have the field a bool type but data would not save to the table.

sasa · November 13, 2007

try

SELECT sum( hours ) hr, sum( mileage ) ml,
IF (
billable = '', 'no', billable
)bill,
IF (
remote_bench = '', 'no', remote_bench
)rb
FROM `tblhours`
WHERE 1
GROUP BY bill, rb

jimlawrnc · November 14, 2007

This can in incorporated into the query i have?

jimlawrnc · November 14, 2007

Not sure how to do this

jimlawrnc · November 16, 2007

bump

pkSML · November 16, 2007

I need to add the $hours column & $mileage column

the $hours column needs to be summed by the following

total hours where $billable = Yes and $remote_bench = no (or Null)

total hours where $billable = Yes and $remote_bench = Yes

total hours where $billable = No (or Null) and $remote_bench = no (or Null)

total hours where $billable = No (or Null) and $remote_bench = Yes

No (or Null) is what is in the table Yes is given if on the input form a checkbox is checked. I tried to have the field a bool type but data would not save to the table.

This may not be the prettiest way to do it, but just have several complex if statements inside your while loop.

Use global variables to keep track of the running totals.

jimlawrnc · November 16, 2007

Yes but i do not know how to do this...

the code given above run in phpmyadmin displays the results but i cannot get that to work in the result.php page

jimlawrnc · November 20, 2007

Bumpy bump

I was able to do the following but the table layout looks horrible

<?php

session_start();

if(!session_is_registered('member_ID')) :
header('Location: index.php?msg=requires_login');
endif;

// Include database information and connectivity
include ('config/opendb.php');

// We store all our functions in one file
include ('config/functions.php');

$tech = htmlspecialchars($_POST['technician']);

$startdate = htmlspecialchars($_POST['datBegin']);
$enddate = htmlspecialchars($_POST['datBegin1']);



$sql = "SELECT sum( hours ) hr, sum( mileage ) ml, IF( billable = '', 'no', billable ) bill, IF( remote_bench = '', 'no', remote_bench ) rb
FROM `tblhours`
WHERE technician = '$tech'
AND servicedate >= '$startdate'
AND servicedate <= '$enddate'
GROUP BY bill, rb";
        
        
$result = mysql_query($sql);
print $result;
if (mysql_num_rows($result)) {
       
       print "<center><TABLE> <face='verdana, arial, helvetica' size='2' align='center'><TR><TD>Hours Report For: $tech      From: $startdate - $enddate </TD></TR></table></center><center><TABLE BORDER=\"3\"><TR STYLE=\"font-weight: bold;\"><TD>Total Hours</TD><TD>Mileage</TD><TD>Billable</TD><TD>Remote/Bench</TD></TR>";
      while($row = mysql_fetch_assoc($result)) {
         extract($row);
         print "<TR><TD>$hr</TD><TD>$ml</TD><TD>$rb</TD></TR>";   
     
      }
   
      print "</TABLE></center>";
   }

//print $result;

?>

[attachment deleted by admin]

pkSML · November 21, 2007

I should add the table structure...

- Table structure for table `tblhours`
--

CREATE TABLE IF NOT EXISTS `tblhours` (
  `ID` int(10) NOT NULL auto_increment,
  `technician` char(30) NOT NULL,
  `client` char(50) NOT NULL,
  `hours` float NOT NULL,
  `mileage` tinyint(10) NOT NULL,
  `billable` varchar(10) NOT NULL,
  `nobillable` varchar(10) NOT NULL,
  `remote_bench` varchar(10) NOT NULL,
  `servicedate` date default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

We'll start with the basics and then this will be a lot easier.

For each yes/no entry in your database, use the boolean data type.

Change billable and remote_bench to BOOL NULL rather than varchar.

You shouldn't need the column "nobillable", right?

And when you're inputting all this data with your form, make billable and remote bench a checkbox. This will put the data in the db properly.

Edit: Is remote_bench supposed to be either 'remote' or 'bench'?

jimlawrnc · November 30, 2007

I cant change the field type on phpmyadmin

ALTER TABLE `tblhours` CHANGE `billable` `billable` BOOL( 1 ) NULL DEFAULT NULL

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1) NULL DEFAULT NULL' at line 1

if you would like to see it in action send me a message and i'll give you a login

this is the form i use

webtime.php

<?php
// Start a session
session_start();

// Sends the user to the login-page if not logged in
if(!session_is_registered('member_ID')) :
header('Location: login.php?msg=requires_login');
endif;


// Include database information and connectivity
include ('config/opendb.php');

// We store all our functions in one file
include ('config/functions.php');

//Links 
include 'links.php';
?>

<html>
<title>Technician Hours Submission Form</title>
<head>
<link rel=StyleSheet href="stylesheet.css" type="text/css">

<script language="javascript" src="choosedate.js"></script>
<link type="text/css" rel="stylesheet" href="calendar.css">

</head>
<body> <!--- style="background-color: silver;">--->
<p>Welcome <?php print user_info('username'); ?></p>
<form name="frm" method="post" action="submittime.php">
<div style="text-align: center;"><span style="font-family: Verdana;">Technician Hours Entry Form<br>
</span>
<hr style="width: 100%; height: 2px;"><span style="font-family: Verdana;"><br style="font-family: Verdana;">
</span></div>
<br>
<table>
<tbody>
<input type="hidden" name="tech" value="$username">
<tr id="0">
<td><input type="hidden" name="technician"></td>
<td>Client:</td>
<td> <input size="20" name="Client"></td>
<td>Hours:</td>
<td><input size="5" name="Hours"></td>
<td>Mileage:</td>
<td><input size="5" name="Mileage">
</td>
<td>Billable:</td>
<td> <input name="Billable" value="Yes" type="checkbox"></td>
<td>Non-Billable:</td>
<td> <input name="NoBillable" value="Yes" type="checkbox"> </td>
<td>Remote/Bench:</td>
<td> <input name="remotebench" value="Yes" type="checkbox"> </td>
<td>Date:</td>
<td><input type="text" name="datBegin" size="7">
 
<script language=javascript>
var basicCal = new calendar("FIELD:document.frm.datBegin");
basicCal.writeCalendar();
</script></td>
</tr>
<!--- Add a few more rows here --->
<tr id="1">
<td><input type="hidden" name="technician1"></td>
<td>Client:</td>
<td> <input size="20" name="Client1"></td>
<td>Hours:</td>
<td><input size="5" name="Hours1"></td>
<td>Mileage:</td>
<td><input size="5" name="Mileage1">
</td>
<td>Billable:</td>
<td> <input name="Billable1" value="Yes" type="checkbox"></td>
<td>Non-Billable:</td>
<td> <input name="NoBillable1" value="Yes" type="checkbox"> </td>
<td>Remote/Bench:</td>
<td> <input name="remotebench1" value="Yes" type="checkbox"> </td>
<td>Date:</td>
<td><input type="text" name="datBegin1" size="7">
 
<script language=javascript>
var basicCal = new calendar("FIELD:document.frm.datBegin1");
basicCal.writeCalendar();
</script></td></tr>
<tr id="2">
<td><input type="hidden" name="technician2"></td>
<td>Client:</td>
<td> <input size="20" name="Client2"></td>
<td>Hours:</td>
<td><input size="5" name="Hours2"></td>
<td>Mileage:</td>
<td><input size="5" name="Mileage2">
</td>
<td>Billable:</td>
<td> <input name="Billable2" value="Yes" type="checkbox"></td>
<td>Non-Billable:</td>
<td> <input name="NoBillable2" value="Yes" type="checkbox"> </td>
<td>Remote/Bench:</td>
<td> <input name="remotebench2" value="Yes" type="checkbox"> </td>
<td>Date:</td>
<td><input type="text" name="datBegin2" size="7">
 
<script language=javascript>
var basicCal = new calendar("FIELD:document.frm.datBegin2");
basicCal.writeCalendar();
</script></td></tr>
<tr id="3">
<td><input type="hidden" name="technician3"></td>
<td>Client:</td>
<td> <input size="20" name="Client3"></td>
<td>Hours:</td>
<td><input size="5" name="Hours3"></td>
<td>Mileage:</td>
<td><input size="5" name="Mileage3">
</td>
<td>Billable:</td>
<td> <input name="Billable3" value="Yes" type="checkbox"></td>
<td>Non-Billable:</td>
<td> <input name="NoBillable3" value="Yes" type="checkbox"> </td>
<td>Remote/Bench:</td>
<td> <input name="remotebench3" value="Yes" type="checkbox"> </td>
<td>Date:</td>
<td><input type="text" name="datBegin3" size="7">
 
<script language=javascript>
var basicCal = new calendar("FIELD:document.frm.datBegin3");
basicCal.writeCalendar();
</script></td>
</tr>
<tr id="4">
<td><input type="hidden" name="technician4"></td>
<td>Client:</td>
<td> <input size="20" name="Client4"></td>
<td>Hours:</td>
<td><input size="5" name="Hours4"></td>
<td>Mileage:</td>
<td><input size="5" name="Mileage4">
</td>
<td>Billable:</td>
<td> <input name="Billable4" value="Yes" type="checkbox"></td>
<td>Non-Billable:</td>
<td> <input name="NoBillable4" value="Yes" type="checkbox"> </td>
<td>Remote/Bench:</td>
<td> <input name="remotebench4" value="Yes" type="checkbox"> </td>
<td>Date:</td>
<td><input type="text" name="datBegin4" size="7">
 
<script language=javascript>
var basicCal = new calendar("FIELD:document.frm.datBegin4");
basicCal.writeCalendar();
</script></td>
</tr>

</tbody>
</table>
<p style="text-align: right;"><input name="submit" value="Submit" type="submit"></p>
<p></p>
</form>

</body>

</html>

This is the form that submits the entries to the DB

submittime.php

<?php 
// Start a session
session_start();
include 'config/config.php';
include 'config/opendb.php';
include 'config/functions.php';
include 'links.php';


// Sends the user to the login-page if not logged in
if(!session_is_registered('member_ID')) :
header('Location: login.php?msg=requires_login');
endif;

//echo user_info('username');
$tech = user_info('username');
$client = htmlspecialchars($_POST['Client']); 
$Hours =  htmlspecialchars($_POST['Hours']);
$Mileage =  htmlspecialchars($_POST['Mileage']);
$Billable = htmlspecialchars($_POST['Billable']);
$NoBillable = htmlspecialchars($_POST['NoBillable']);
$RemoteBench = htmlspecialchars($_POST['remotebench']);
$serviceDate =  htmlspecialchars($_POST['datBegin']);


//Build sql insert command 

$addTimequery = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')";

//second row 
$tech = user_info('username');
$client = htmlspecialchars($_POST['Client1']); 
$Hours =  htmlspecialchars($_POST['Hours1']);
$Mileage =  htmlspecialchars($_POST['Mileage1']);
$Billable = htmlspecialchars($_POST['Billable1']);
$NoBillable = htmlspecialchars($_POST['NoBillable1']);
$RemoteBench = htmlspecialchars($_POST['remotebench1']);
$serviceDate =  htmlspecialchars($_POST['datBegin1']);
//Build sql insert command 

$addTimequery1 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')";

//Third row 
$tech = user_info('username');
$client2 = htmlspecialchars($_POST['Client2']); 
$Hours2 =  htmlspecialchars($_POST['Hours2']);
$Mileage2 =  htmlspecialchars($_POST['Mileage2']);
$Billable2 = htmlspecialchars($_POST['Billable2']);
$NoBillable2 = htmlspecialchars($_POST['NoBillable2']);
$RemoteBench2 = htmlspecialchars($_POST['remotebench2']);
$serviceDate2 =  htmlspecialchars($_POST['datBegin2']);
//Build sql insert command 

$addTimequery2 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client2', '$Hours2', '$Mileage2', '$Billable2', '$NoBillable2', '$RemoteBench2', '$serviceDate2')";

//Forth row 
$tech = user_info('username');
$client = htmlspecialchars($_POST['Client3']); 
$Hours =  htmlspecialchars($_POST['Hours3']);
$Mileage =  htmlspecialchars($_POST['Mileage3']);
$Billable = htmlspecialchars($_POST['Billable3']);
$NoBillable = htmlspecialchars($_POST['NoBillable3']);
$RemoteBench = htmlspecialchars($_POST['remotebench3']);
$serviceDate =  htmlspecialchars($_POST['datBegin3']);
//Build sql insert command 

$addTimequery3 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')";

//Fifth row 
$tech = user_info('username');
$client = htmlspecialchars($_POST['Client4']); 
$Hours =  htmlspecialchars($_POST['Hours4']);
$Mileage =  htmlspecialchars($_POST['Mileage4']);
$Billable = htmlspecialchars($_POST['Billable4']);
$NoBillable = htmlspecialchars($_POST['NoBillable4']);
$RemoteBench = htmlspecialchars($_POST['remotebench4']);
$serviceDate =  htmlspecialchars($_POST['datBegin4']);
//Build sql insert command 

$addTimequery4 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')";

//execute sql query 
mysql_query($addTimequery) or die (mysql_error());
mysql_query($addTimequery1) or die (mysql_error());
mysql_query($addTimequery2) or die (mysql_error());
// mysql_query($addTimequery3) or die (mysql_error());
// mysql_query($addTimequery4) or die (mysql_error());

include 'closedb.php';
echo "Your Time was added sucessfully";

?>
<html>
<head>
<link rel=StyleSheet href="stylesheet.css" type="text/css">
</head>

<body>

</body>
</html>

what im trying to accomplish is to combind 2 results into 1 page

e.g

i have a report page that asks for name start date and end date

i need it to spit out the results in a table format and total the colums

the code posted by sasa works by its self but i cannot get the 2 results on one page formatted nicely.

Sign In

Total Table Columns

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information