Jump to content

Recommended Posts

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>";
   }

?>

 

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/
Share on other sites

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 ;

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390265
Share on other sites

  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. 

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390526
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-392839
Share on other sites

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]

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-394872
Share on other sites

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'?

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-395789
Share on other sites

  • 2 weeks later...

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.

 

 

Link to comment
https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-402825
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.